Debian Codegraph @

mysql-test/suite/ndb/r/ndb_metadata_upgrade_80_minor_fk.result

raw
1 Backup restored
2 Restart server 1 to trigger schema synchronization
3 # restart
4 SHOW TABLES;
5 Tables_in_test
6 child
7 parent
8 self_ref
9 SHOW CREATE TABLE parent;
10 Table Create Table
11 parent CREATE TABLE `parent` (
12 `c1` int NOT NULL,
13 `c2` int DEFAULT NULL,
14 PRIMARY KEY (`c1`),
15 UNIQUE KEY `p_ukc2` (`c2`)
16 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
17 SELECT * FROM parent;
18 c1 c2
19 1 10
20 2 20
21 SHOW CREATE TABLE child;
22 Table Create Table
23 child CREATE TABLE `child` (
24 `c1` int DEFAULT NULL,
25 `c2` int DEFAULT NULL,
26 KEY `c_xc1` (`c1`),
27 KEY `c_xc2` (`c2`),
28 CONSTRAINT `c_fk1` FOREIGN KEY (`c1`) REFERENCES `parent` (`c1`),
29 CONSTRAINT `c_fk2` FOREIGN KEY (`c2`) REFERENCES `parent` (`c2`)
30 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
31 SELECT * FROM child;
32 c1 c2
33 1 10
34 1 20
35 2 10
36 2 20
37 # Check foreign key constraints
38 INSERT INTO parent VALUES (3, 30);
39 DELETE FROM parent WHERE c1 = 1;
40 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `c_fk1` FOREIGN KEY (`c1`) REFERENCES `parent` (`c1`) ON DELETE NO ACTION ON UPDATE NO ACTION)
41 SELECT * FROM parent;
42 c1 c2
43 1 10
44 2 20
45 3 30
46 INSERT INTO child VALUES (3, 30);
47 INSERT INTO child VALUES (4, 40);
48 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `c_fk1` FOREIGN KEY (`c1`) REFERENCES `parent` (`c1`) ON DELETE NO ACTION ON UPDATE NO ACTION)
49 SELECT * FROM child;
50 c1 c2
51 1 10
52 1 20
53 2 10
54 2 20
55 3 30
56 SHOW CREATE TABLE self_ref;
57 Table Create Table
58 self_ref CREATE TABLE `self_ref` (
59 `c1` int NOT NULL,
60 `c2` int DEFAULT NULL,
61 UNIQUE KEY `sr_ukc1` (`c1`),
62 KEY `sr_fk1` (`c2`),
63 CONSTRAINT `sr_fk1` FOREIGN KEY (`c2`) REFERENCES `self_ref` (`c1`) ON DELETE CASCADE ON UPDATE SET DEFAULT
64 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
65 SELECT * FROM self_ref;
66 c1 c2
67 1 1
68 2 1
69 INSERT INTO self_ref VALUES (3, 2);
70 INSERT INTO self_ref VALUES (4, 5);
71 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`self_ref`, CONSTRAINT `sr_fk1` FOREIGN KEY (`c2`) REFERENCES `self_ref` (`c1`) ON DELETE CASCADE ON UPDATE SET DEFAULT)
72 DELETE FROM self_ref WHERE c1 = 1;
73 SELECT * FROM self_ref;
74 c1 c2
75 DROP TABLE child;
76 DROP TABLE parent;
77 DROP TABLE self_ref;