Debian Codegraph @

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

raw
1 create table parent (
2 a int not null,
3 b int not null,
4 c int not null,
5 primary key (a,b),
6 unique(b,c) using hash,
7 index(c,a)) engine = ndb;
8 create table child (
9 a int not null,
10 b int not null,
11 c int not null,
12 primary key (b,a),
13 unique(c,b) using hash,
14 index(c,a)) engine = ndb;
15 insert into parent values (1,2,3);
16 alter table child add constraint fkname foreign key (c,a) references parent(a,b) on delete restrict on update restrict;
17 insert into child values (2,1,2);
18 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `fkname` FOREIGN KEY (`c`,`a`) REFERENCES `parent` (`a`,`b`) ON DELETE RESTRICT ON UPDATE RESTRICT)
19 insert into child values (1,1,1);
20 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `fkname` FOREIGN KEY (`c`,`a`) REFERENCES `parent` (`a`,`b`) ON DELETE RESTRICT ON UPDATE RESTRICT)
21 insert into child values (2,1,1);
22 delete from parent;
23 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `fkname` FOREIGN KEY (`c`,`a`) REFERENCES `parent` (`a`,`b`) ON DELETE RESTRICT ON UPDATE RESTRICT)
24 delete from child;
25 alter table child drop foreign key fkname;
26 alter table child add constraint fkname foreign key (b,a) references parent(a,b) on delete restrict on update restrict;
27 insert into child values (1,2,2);
28 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `fkname` FOREIGN KEY (`b`,`a`) REFERENCES `parent` (`a`,`b`) ON DELETE RESTRICT ON UPDATE RESTRICT)
29 insert into child values (2,3,1);
30 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `fkname` FOREIGN KEY (`b`,`a`) REFERENCES `parent` (`a`,`b`) ON DELETE RESTRICT ON UPDATE RESTRICT)
31 insert into child values (2,1,1);
32 delete from parent;
33 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `fkname` FOREIGN KEY (`b`,`a`) REFERENCES `parent` (`a`,`b`) ON DELETE RESTRICT ON UPDATE RESTRICT)
34 delete from child;
35 alter table child drop foreign key fkname;
36 alter table child add constraint fkname foreign key (c,b) references parent(a,b) on delete restrict on update restrict;
37 insert into child values (1,2,2);
38 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `fkname` FOREIGN KEY (`c`,`b`) REFERENCES `parent` (`a`,`b`) ON DELETE RESTRICT ON UPDATE RESTRICT)
39 insert into child values (2,3,1);
40 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `fkname` FOREIGN KEY (`c`,`b`) REFERENCES `parent` (`a`,`b`) ON DELETE RESTRICT ON UPDATE RESTRICT)
41 insert into child values (2,2,1);
42 delete from parent;
43 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `fkname` FOREIGN KEY (`c`,`b`) REFERENCES `parent` (`a`,`b`) ON DELETE RESTRICT ON UPDATE RESTRICT)
44 drop table child, parent;
45 #
46 # Bug#21664899 : FAILURE CREATING COMPOSITE FKS REFERENCING COMPOSITE PKS OF PARENT
47 #
48 #create table t1
49 create table t1 (
50 a int not null,
51 b int not null,
52 c int not null,
53 primary key pk1(a,b)
54 ) engine=ndbcluster;
55 #create t2 with a unique key on single column
56 create table t2 (
57 a int not null,
58 b int not null,
59 c int not null,
60 unique key uniquet2a(a),
61 constraint fkt2ab foreign key (a,b) references t1(a,b)
62 ) engine=ndbcluster;
63 #verify the presence of uniquet2a, fkt2ab & and an extra key for fkt2ab
64 show create table t2;
65 Table Create Table
66 t2 CREATE TABLE `t2` (
67 `a` int NOT NULL,
68 `b` int NOT NULL,
69 `c` int NOT NULL,
70 UNIQUE KEY `uniquet2a` (`a`),
71 KEY `fkt2ab` (`a`,`b`),
72 CONSTRAINT `fkt2ab` FOREIGN KEY (`a`, `b`) REFERENCES `t1` (`a`, `b`)
73 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
74 alter table t2 drop key uniquet2a;
75 #alter add a unique constraint on single key
76 alter table t2 add constraint uniquet2a unique (a);
77 #verify the presence of uniquet2a, fkt2ab & and an extra key for fkt2ab
78 show create table t2;
79 Table Create Table
80 t2 CREATE TABLE `t2` (
81 `a` int NOT NULL,
82 `b` int NOT NULL,
83 `c` int NOT NULL,
84 UNIQUE KEY `uniquet2a` (`a`),
85 KEY `fkt2ab` (`a`,`b`),
86 CONSTRAINT `fkt2ab` FOREIGN KEY (`a`, `b`) REFERENCES `t1` (`a`, `b`)
87 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
88 #alter add a unique constraint on all cols in fk
89 alter table t2 add constraint uniquet2ab unique (a,b);
90 #verify the presence of uniquet2a, uniquet2ab, fkt2ab. no extra key for fkt2ab
91 show create table t2;
92 Table Create Table
93 t2 CREATE TABLE `t2` (
94 `a` int NOT NULL,
95 `b` int NOT NULL,
96 `c` int NOT NULL,
97 UNIQUE KEY `uniquet2a` (`a`),
98 UNIQUE KEY `uniquet2ab` (`a`,`b`),
99 CONSTRAINT `fkt2ab` FOREIGN KEY (`a`, `b`) REFERENCES `t1` (`a`, `b`)
100 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
101 #try adding fk(a,b,c) referring t1(a,b,c), it should fail without parent index.
102 alter table t2 add constraint fkt2abc foreign key (a,b,c) references t1(a,b,c);
103 ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fkt2abc' in the referenced table 't1'
104 #now add a unique key but on a different column order and try again
105 alter table t1 add constraint uniquet1abc unique (c,a,b);
106 alter table t2 add constraint fkt2abc foreign key (a,b,c) references t1(a,b,c);
107 #verify the presence of new fk
108 show create table t2;
109 Table Create Table
110 t2 CREATE TABLE `t2` (
111 `a` int NOT NULL,
112 `b` int NOT NULL,
113 `c` int NOT NULL,
114 UNIQUE KEY `uniquet2a` (`a`),
115 UNIQUE KEY `uniquet2ab` (`a`,`b`),
116 KEY `fkt2abc` (`a`,`b`,`c`),
117 CONSTRAINT `fkt2ab` FOREIGN KEY (`a`, `b`) REFERENCES `t1` (`a`, `b`),
118 CONSTRAINT `fkt2abc` FOREIGN KEY (`a`, `b`, `c`) REFERENCES `t1` (`a`, `b`, `c`)
119 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
120 #cleanup
121 drop table t2, t1;