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 # restart
47 #
48 # Try to insert more to the example table with foreign keys.
49 # Bug60196 causes the foreign key file not to be found after
50 # the resstart above.
51 #
52 SELECT * FROM Bug_60196;
53 FK1_Key FK2_Key
54 1 1
55 1 2
56 1 3
57 INSERT INTO Bug_60196 VALUES (2, 1);
58 INSERT INTO Bug_60196 VALUES (2, 2);
59 INSERT INTO Bug_60196 VALUES (2, 3);
60 SELECT * FROM Bug_60196;
61 FK1_Key FK2_Key
62 1 1
63 1 2
64 1 3
65 2 1
66 2 2
67 2 3
68
69 # Clean up.
70 DROP TABLE Bug_60196;
71 DROP TABLE Bug_60196_FK1;
72 DROP TABLE Bug_60196_FK2;
73 CREATE TABLE Bug_60309_FK (
74 ID INT PRIMARY KEY,
75 ID2 INT,
76 KEY K2(ID2)
77 ) ENGINE=InnoDB;
78 CREATE TABLE Bug_60309 (
79 ID INT PRIMARY KEY,
80 FK_ID INT,
81 KEY (FK_ID),
82 CONSTRAINT FK FOREIGN KEY (FK_ID) REFERENCES Bug_60309_FK (ID)
83 ) ENGINE=InnoDB;
84 INSERT INTO Bug_60309_FK (ID, ID2) VALUES (1, 1), (2, 2), (3, 3);
85 INSERT INTO Bug_60309 VALUES (1, 1);
86 INSERT INTO Bug_60309 VALUES (2, 99);
87 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`))
88 SELECT * FROM Bug_60309_FK;
89 ID ID2
90 1 1
91 2 2
92 3 3
93 SELECT * FROM Bug_60309;
94 ID FK_ID
95 1 1
96 # restart
97 #
98 # Try to insert more to the example table with foreign keys.
99 # Bug60309 causes the foreign key file not to be found after
100 # the resstart above.
101 #
102 SELECT * FROM Bug_60309;
103 ID FK_ID
104 1 1
105 INSERT INTO Bug_60309 VALUES (2, 2);
106 INSERT INTO Bug_60309 VALUES (3, 3);
107 SELECT * FROM Bug_60309;
108 ID FK_ID
109 1 1
110 2 2
111 3 3
112
113 # Clean up.
114 DROP TABLE Bug_60309;
115 DROP TABLE Bug_60309_FK;