Debian Codegraph @

mysql-test/suite/innodb/r/innodb_bug60196.result

raw
1 CREATE TABLE Bug_60196_FK1 (Primary_Key INT PRIMARY KEY) ENGINE=InnoDB;
2 CREATE TABLE Bug_60196_FK2 (Primary_Key INT PRIMARY KEY) ENGINE=InnoDB;
3 CREATE TABLE Bug_60196 (
4 FK1_Key INT NOT NULL,
5 FK2_Key INT NOT NULL,
6 PRIMARY KEY (FK2_Key, FK1_Key),
7 KEY FK1_Key (FK1_Key),
8 KEY FK2_Key (FK2_Key),
9 CONSTRAINT FK_FK1 FOREIGN KEY (FK1_Key)
10 REFERENCES Bug_60196_FK1 (Primary_Key)
11 ON DELETE CASCADE
12 ON UPDATE CASCADE,
13 CONSTRAINT FK_FK2 FOREIGN KEY (FK2_Key)
14 REFERENCES Bug_60196_FK2 (Primary_Key)
15 ON DELETE CASCADE
16 ON UPDATE CASCADE
17 ) ENGINE=InnoDB;
18 INSERT INTO Bug_60196_FK1 VALUES (1), (2), (3), (4), (5);
19 INSERT INTO Bug_60196_FK2 VALUES (1), (2), (3), (4), (5);
20 INSERT INTO Bug_60196 VALUES (1, 1);
21 INSERT INTO Bug_60196 VALUES (1, 2);
22 INSERT INTO Bug_60196 VALUES (1, 3);
23 INSERT INTO Bug_60196 VALUES (1, 99);
24 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`bug_60196`, CONSTRAINT `FK_FK2` FOREIGN KEY (`FK2_Key`) REFERENCES `Bug_60196_FK2` (`Primary_Key`) ON DELETE CASCADE ON UPDATE CASCADE)
25 INSERT INTO Bug_60196 VALUES (99, 1);
26 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`bug_60196`, CONSTRAINT `FK_FK1` FOREIGN KEY (`FK1_Key`) REFERENCES `Bug_60196_FK1` (`Primary_Key`) ON DELETE CASCADE ON UPDATE CASCADE)
27 SELECT * FROM bug_60196_FK1;
28 Primary_Key
29 1
30 2
31 3
32 4
33 5
34 SELECT * FROM bug_60196_FK2;
35 Primary_Key
36 1
37 2
38 3
39 4
40 5
41 SELECT * FROM bug_60196;
42 FK1_Key FK2_Key
43 1 1
44 1 2
45 1 3
46 # Stop server
47 # Restart server.
48 #
49 # Try to insert more to the example table with foreign keys.
50 # Bug60196 causes the foreign key file not to be found after
51 # the resstart above.
52 #
53 SELECT * FROM Bug_60196;
54 FK1_Key FK2_Key
55 1 1
56 1 2
57 1 3
58 INSERT INTO Bug_60196 VALUES (2, 1);
59 INSERT INTO Bug_60196 VALUES (2, 2);
60 INSERT INTO Bug_60196 VALUES (2, 3);
61 SELECT * FROM Bug_60196;
62 FK1_Key FK2_Key
63 1 1
64 1 2
65 1 3
66 2 1
67 2 2
68 2 3
69
70 # Clean up.
71 DROP TABLE Bug_60196;
72 DROP TABLE Bug_60196_FK1;
73 DROP TABLE Bug_60196_FK2;
74 CREATE TABLE Bug_60309_FK (
75 ID INT PRIMARY KEY,
76 ID2 INT,
77 KEY K2(ID2)
78 ) ENGINE=InnoDB;
79 CREATE TABLE Bug_60309 (
80 ID INT PRIMARY KEY,
81 FK_ID INT,
82 KEY (FK_ID),
83 CONSTRAINT FK FOREIGN KEY (FK_ID) REFERENCES Bug_60309_FK (ID)
84 ) ENGINE=InnoDB;
85 INSERT INTO Bug_60309_FK (ID, ID2) VALUES (1, 1), (2, 2), (3, 3);
86 INSERT INTO Bug_60309 VALUES (1, 1);
87 INSERT INTO Bug_60309 VALUES (2, 99);
88 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`bug_60309`, CONSTRAINT `FK` FOREIGN KEY (`FK_ID`) REFERENCES `Bug_60309_FK` (`ID`))
89 SELECT * FROM Bug_60309_FK;
90 ID ID2
91 1 1
92 2 2
93 3 3
94 SELECT * FROM Bug_60309;
95 ID FK_ID
96 1 1
97 # Stop server
98 # Restart server.
99 #
100 # Try to insert more to the example table with foreign keys.
101 # Bug60309 causes the foreign key file not to be found after
102 # the resstart above.
103 #
104 SELECT * FROM Bug_60309;
105 ID FK_ID
106 1 1
107 INSERT INTO Bug_60309 VALUES (2, 2);
108 INSERT INTO Bug_60309 VALUES (3, 3);
109 SELECT * FROM Bug_60309;
110 ID FK_ID
111 1 1
112 2 2
113 3 3
114
115 # Clean up.
116 DROP TABLE Bug_60309;
117 DROP TABLE Bug_60309_FK;