Newer
Older
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
START TRANSACTION;
# exam_period_options
# 1. создать поле RecordBookID
ALTER TABLE `exam_period_options`
ADD COLUMN `RecordBookID` int(11),
ADD KEY (`RecordBookID`);
# discipline_students
# 1. создать поле RecordBookID
ALTER TABLE `disciplines_students`
ADD COLUMN `RecordBookID` int(11),
ADD KEY (`RecordBookID`);
COMMIT;
LOCK TABLES exam_period_options WRITE, disciplines_students WRITE, submodules WRITE, modules READ,
disciplines WRITE, students_groups READ, record_books WRITE;
-- --------------------------------------------------------
# exam_period_options
# 1. создать поле RecordBookID - done
# 2. заполнить поле RecordBookID
# 3. удалить все связи поля StudentID и потом само поле
# 4. создать связи для поля RecordBookID
UPDATE exam_period_options
INNER JOIN submodules on submodules.ID = exam_period_options.SubmoduleID
INNER JOIN modules on modules.ID = submodules.ModuleID
INNER JOIN disciplines on disciplines.ID = modules.DisciplineID
INNER JOIN students_groups on students_groups.SemesterID = disciplines.SemesterID
AND students_groups.StudentID = exam_period_options.StudentID
SET exam_period_options.RecordBookID = students_groups.RecordBookID;
ALTER TABLE `exam_period_options`
DROP FOREIGN KEY `exam_period_options_ibfk_2`,
DROP INDEX `StudentID`,
DROP INDEX `StudentID_2`,
DROP INDEX `StudentID_3`,
DROP FOREIGN KEY `exam_period_options_ibfk_1`,
DROP INDEX `SubmoduleID`,
DROP INDEX `SubmoduleID_2`,
DROP COLUMN `StudentID`;
ALTER TABLE `exam_period_options`
ADD KEY (`SubmoduleID`),
ADD CONSTRAINT `Unique` UNIQUE (`RecordBookID`, `SubmoduleID`),
ADD CONSTRAINT `exam_period_options_ibfk_1` FOREIGN KEY (`SubmoduleID`) REFERENCES `submodules` (`ID`),
ADD CONSTRAINT `exam_period_options_ibfk_2` FOREIGN KEY (`RecordBookID`) REFERENCES `record_books` (`ID`);
-- --------------------------------------------------------
# discipline_students
# 1. создать поле RecordBookID - done
# 2. заполнить поле RecordBookID
# 3. удалить все связи поля StudentID и потом само поле
# 4. создать связи для поля RecordBookID
UPDATE `disciplines_students`
INNER JOIN disciplines on disciplines.ID = disciplines_students.DisciplineID
INNER JOIN students_groups on students_groups.SemesterID = disciplines.SemesterID
AND students_groups.StudentID = disciplines_students.StudentID
SET disciplines_students.RecordBookID = students_groups.RecordBookID;
ALTER TABLE `disciplines_students`
DROP FOREIGN KEY `disciplines_students_ibfk_2`,
DROP INDEX DisciplineID_2,
DROP INDEX `StudentID`,
DROP COLUMN `StudentID`;
ALTER TABLE `disciplines_students`
ADD CONSTRAINT `Unique` UNIQUE (`RecordBookID`, `DisciplineID`),
ADD CONSTRAINT `disciplines_students_ibfk_2` FOREIGN KEY (`RecordBookID`) REFERENCES `record_books` (`ID`);
UNLOCK TABLES ;