Debian Codegraph @

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

raw
1 create table t1 (
2 a int not null,
3 b int not null,
4 c int not null,
5 d int not null,
6 e int not null,
7 f int not null,
8 primary key (a),
9 unique(c) using hash,
10 index(e)) engine = ndb;
11 alter table t1 add constraint fkname
12 foreign key (a) references t1(a) on delete restrict on update restrict;
13 insert into t1 values (1,1,1,1,1,1);
14 alter table t1 drop foreign key fkname;
15 delete from t1;
16 alter table t1 add constraint fkname
17 foreign key (c) references t1(a) on delete restrict on update restrict;
18 insert into t1 values (1,1,1,1,1,1);
19 insert into t1 values (2,2,3,3,2,2);
20 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `fkname` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON DELETE RESTRICT ON UPDATE RESTRICT)
21 alter table t1 drop foreign key fkname;
22 delete from t1;
23 alter table t1 add constraint fkname
24 foreign key (e) references t1(a) on delete restrict on update restrict;
25 insert into t1 values (1,1,1,1,1,1);
26 insert into t1 values (2,2,2,2,3,3);
27 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `fkname` FOREIGN KEY (`e`) REFERENCES `t1` (`a`) ON DELETE RESTRICT ON UPDATE RESTRICT)
28 alter table t1 drop foreign key fkname;
29 delete from t1;
30 alter table t1 add constraint fkname
31 foreign key (a) references t1(c) on delete restrict on update restrict;
32 insert into t1 values (1,1,1,1,1,1);
33 insert into t1 values (2,2,3,3,2,2);
34 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `fkname` FOREIGN KEY (`a`) REFERENCES `t1` (`c`) ON DELETE RESTRICT ON UPDATE RESTRICT)
35 alter table t1 drop foreign key fkname;
36 delete from t1;
37 alter table t1 add constraint fkname
38 foreign key (c) references t1(c) on delete restrict on update restrict;
39 insert into t1 values (1,1,1,1,1,1);
40 alter table t1 drop foreign key fkname;
41 delete from t1;
42 alter table t1 add constraint fkname
43 foreign key (e) references t1(c) on delete restrict on update restrict;
44 insert into t1 values (1,1,1,1,1,1);
45 insert into t1 values (2,2,2,2,3,3);
46 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `fkname` FOREIGN KEY (`e`) REFERENCES `t1` (`c`) ON DELETE RESTRICT ON UPDATE RESTRICT)
47 alter table t1 drop foreign key fkname;
48 delete from t1;
49 drop table t1;
50 create table t1 (
51 a int not null,
52 b int not null,
53 c int not null,
54 d int not null,
55 e int not null,
56 f int not null,
57 primary key (a,b),
58 unique(c,d) using hash,
59 index(e,f)) engine = ndb;
60 alter table t1 add constraint fkname
61 foreign key (a,b) references t1(a,b) on delete no action on update no action;
62 insert into t1 values (1,1,1,1,1,1);
63 alter table t1 drop foreign key fkname;
64 delete from t1;
65 alter table t1 add constraint fkname
66 foreign key (c,d) references t1(a,b) on delete no action on update no action;
67 insert into t1 values (1,1,1,1,1,1);
68 insert into t1 values (2,2,3,3,2,2);
69 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `fkname` FOREIGN KEY (`c`,`d`) REFERENCES `t1` (`a`,`b`) ON DELETE NO ACTION ON UPDATE NO ACTION)
70 alter table t1 drop foreign key fkname;
71 delete from t1;
72 alter table t1 add constraint fkname
73 foreign key (e,f) references t1(a,b) on delete no action on update no action;
74 insert into t1 values (1,1,1,1,1,1);
75 insert into t1 values (2,2,2,2,3,3);
76 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `fkname` FOREIGN KEY (`e`,`f`) REFERENCES `t1` (`a`,`b`) ON DELETE NO ACTION ON UPDATE NO ACTION)
77 alter table t1 drop foreign key fkname;
78 delete from t1;
79 alter table t1 add constraint fkname
80 foreign key (a,b) references t1(c,d) on delete no action on update no action;
81 insert into t1 values (1,1,1,1,1,1);
82 insert into t1 values (2,2,3,3,2,2);
83 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `fkname` FOREIGN KEY (`a`,`b`) REFERENCES `t1` (`c`,`d`) ON DELETE NO ACTION ON UPDATE NO ACTION)
84 alter table t1 drop foreign key fkname;
85 delete from t1;
86 alter table t1 add constraint fkname
87 foreign key (c,d) references t1(c,d) on delete no action on update no action;
88 insert into t1 values (1,1,1,1,1,1);
89 alter table t1 drop foreign key fkname;
90 delete from t1;
91 alter table t1 add constraint fkname
92 foreign key (e,f) references t1(c,d) on delete no action on update no action;
93 insert into t1 values (1,1,1,1,1,1);
94 insert into t1 values (2,2,2,2,3,3);
95 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `fkname` FOREIGN KEY (`e`,`f`) REFERENCES `t1` (`c`,`d`) ON DELETE NO ACTION ON UPDATE NO ACTION)
96 alter table t1 drop foreign key fkname;
97 delete from t1;
98 drop table t1;
99 #
100 # Truncating table with self referencing Foreign key
101 #
102 create table t2 (
103 pk2 int not null primary key,
104 c int,
105 foreign key (c) references t2(pk2)
106 ) engine=NDB;
107 Foreign keys referenced by test.t2 : t2_fk_1
108 Foreign keys on test.t2 : t2_fk_1
109 truncate table t2;
110 Verify all the foreign key are re-created after truncate
111 Foreign keys referenced by test.t2 : t2_fk_1
112 Foreign keys on test.t2 : t2_fk_1
113 show create table t2;
114 Table Create Table
115 t2 CREATE TABLE `t2` (
116 `pk2` int NOT NULL,
117 `c` int DEFAULT NULL,
118 PRIMARY KEY (`pk2`),
119 KEY `c` (`c`),
120 CONSTRAINT `t2_fk_1` FOREIGN KEY (`c`) REFERENCES `t2` (`pk2`)
121 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
122 drop table t2;