Debian Codegraph @

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

raw
1 create table parent (
2 a int primary key auto_increment,
3 b int not null,
4 c int not null,
5 unique(b) using hash,
6 index(c)) engine = ndb;
7 create table child (
8 a int primary key auto_increment,
9 b int not null,
10 c int not null,
11 unique(b) using hash,
12 index(c)) engine = ndb;
13 create table blobchild (
14 a int primary key auto_increment,
15 b int not null,
16 c int not null,
17 d text,
18 unique(b) using hash,
19 index(c)) engine = ndb;
20 alter table blobchild add constraint fkbad foreign key(a) references parent (a) on delete cascade on update restrict;
21 ERROR HY000: Cannot add foreign key constraint
22 show warnings;
23 Level Code Message
24 Warning 1296 Got error 21034 'Create foreign key failed - child table has Blob or Text column and on-delete-cascade is not allowed' from NDB
25 Error 1215 Cannot add foreign key constraint
26 alter table child add constraint fkname foreign key (a) references parent (a) on delete cascade on update restrict;
27 insert into parent values (1,1,1),(2,2,2);
28 insert into child values (1,1,1),(2,2,2);
29 insert into child values (3,3,3);
30 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `fkname` FOREIGN KEY (`a`) REFERENCES `parent` (`a`) ON DELETE CASCADE ON UPDATE RESTRICT)
31 begin;
32 delete from parent where a = 1;
33 select * from child order by 1,2,3;
34 a b c
35 2 2 2
36 commit;
37 delete from child;
38 delete from parent;
39 alter table child drop foreign key fkname;
40 alter table blobchild add constraint fkbad foreign key(b) references parent (a) on delete cascade on update restrict;
41 ERROR HY000: Cannot add foreign key constraint
42 show warnings;
43 Level Code Message
44 Warning 1296 Got error 21034 'Create foreign key failed - child table has Blob or Text column and on-delete-cascade is not allowed' from NDB
45 Error 1215 Cannot add foreign key constraint
46 alter table child add constraint fkname foreign key (b) references parent (a) on delete cascade on update restrict;
47 insert into parent values (1,1,1),(2,2,2);
48 insert into child values (1,1,1),(2,2,2);
49 insert into child values (3,3,3);
50 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `fkname` FOREIGN KEY (`b`) REFERENCES `parent` (`a`) ON DELETE CASCADE ON UPDATE RESTRICT)
51 update child set b = 3 where a = 1;
52 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `fkname` FOREIGN KEY (`b`) REFERENCES `parent` (`a`) ON DELETE CASCADE ON UPDATE RESTRICT)
53 begin;
54 delete from parent where a = 1;
55 select * from child order by 1,2,3;
56 a b c
57 2 2 2
58 commit;
59 delete from child;
60 delete from parent;
61 alter table child drop foreign key fkname;
62 alter table blobchild add constraint fkbad foreign key(a) references parent (b) on delete cascade on update restrict;
63 ERROR HY000: Cannot add foreign key constraint
64 show warnings;
65 Level Code Message
66 Warning 1296 Got error 21034 'Create foreign key failed - child table has Blob or Text column and on-delete-cascade is not allowed' from NDB
67 Error 1215 Cannot add foreign key constraint
68 alter table child add constraint fkname foreign key (a) references parent (b) on delete cascade on update restrict;
69 insert into parent values (1,1,1),(2,2,2);
70 insert into child values (1,1,1),(2,2,2);
71 insert into child values (3,3,3);
72 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `fkname` FOREIGN KEY (`a`) REFERENCES `parent` (`b`) ON DELETE CASCADE ON UPDATE RESTRICT)
73 update parent set b = 3 where a = 1;
74 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `fkname` FOREIGN KEY (`a`) REFERENCES `parent` (`b`) ON DELETE CASCADE ON UPDATE RESTRICT)
75 begin;
76 delete from parent where a = 1;
77 select * from child order by 1,2,3;
78 a b c
79 2 2 2
80 commit;
81 delete from child;
82 delete from parent;
83 alter table child drop foreign key fkname;
84 alter table blobchild add constraint fkbad foreign key(b) references parent (b) on delete cascade on update restrict;
85 ERROR HY000: Cannot add foreign key constraint
86 show warnings;
87 Level Code Message
88 Warning 1296 Got error 21034 'Create foreign key failed - child table has Blob or Text column and on-delete-cascade is not allowed' from NDB
89 Error 1215 Cannot add foreign key constraint
90 alter table child add constraint fkname foreign key (b) references parent (b) on delete cascade on update restrict;
91 insert into parent values (1,1,1),(2,2,2);
92 insert into child values (1,1,1),(2,2,2);
93 update child set b = 3 where a = 1;
94 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `fkname` FOREIGN KEY (`b`) REFERENCES `parent` (`b`) ON DELETE CASCADE ON UPDATE RESTRICT)
95 insert into child values (3,3,3);
96 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `fkname` FOREIGN KEY (`b`) REFERENCES `parent` (`b`) ON DELETE CASCADE ON UPDATE RESTRICT)
97 update parent set b = 3 where a = 1;
98 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `fkname` FOREIGN KEY (`b`) REFERENCES `parent` (`b`) ON DELETE CASCADE ON UPDATE RESTRICT)
99 begin;
100 delete from parent where a = 1;
101 select * from child order by 1,2,3;
102 a b c
103 2 2 2
104 commit;
105 delete from child;
106 delete from parent;
107 alter table child drop foreign key fkname;
108 alter table blobchild add constraint fkbad foreign key(c) references parent (a) on delete cascade on update restrict;
109 ERROR HY000: Cannot add foreign key constraint
110 show warnings;
111 Level Code Message
112 Warning 1296 Got error 21034 'Create foreign key failed - child table has Blob or Text column and on-delete-cascade is not allowed' from NDB
113 Error 1215 Cannot add foreign key constraint
114 alter table child add constraint fkname foreign key (c) references parent (a) on delete cascade on update restrict;
115 insert into parent values (1,1,1),(2,2,2);
116 insert into child values (1,1,1),(2,2,2);
117 update child set c = 3 where a = 1;
118 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `fkname` FOREIGN KEY (`c`) REFERENCES `parent` (`a`) ON DELETE CASCADE ON UPDATE RESTRICT)
119 insert into child values (3,3,3);
120 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `fkname` FOREIGN KEY (`c`) REFERENCES `parent` (`a`) ON DELETE CASCADE ON UPDATE RESTRICT)
121 begin;
122 delete from parent where a = 1;
123 select * from child order by 1,2,3;
124 a b c
125 2 2 2
126 commit;
127 delete from child;
128 delete from parent;
129 alter table child drop foreign key fkname;
130 alter table blobchild add constraint fkbad foreign key(c) references parent (b) on delete cascade on update restrict;
131 ERROR HY000: Cannot add foreign key constraint
132 show warnings;
133 Level Code Message
134 Warning 1296 Got error 21034 'Create foreign key failed - child table has Blob or Text column and on-delete-cascade is not allowed' from NDB
135 Error 1215 Cannot add foreign key constraint
136 alter table child add constraint fkname foreign key (c) references parent (b) on delete cascade on update restrict;
137 insert into parent values (1,1,1),(2,2,2);
138 insert into child values (1,1,1),(2,2,2);
139 update child set c = 3 where a = 1;
140 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `fkname` FOREIGN KEY (`c`) REFERENCES `parent` (`b`) ON DELETE CASCADE ON UPDATE RESTRICT)
141 insert into child values (3,3,3);
142 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `fkname` FOREIGN KEY (`c`) REFERENCES `parent` (`b`) ON DELETE CASCADE ON UPDATE RESTRICT)
143 update parent set b = 3 where a = 1;
144 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `fkname` FOREIGN KEY (`c`) REFERENCES `parent` (`b`) ON DELETE CASCADE ON UPDATE RESTRICT)
145 begin;
146 delete from parent where a = 1;
147 select * from child order by 1,2,3;
148 a b c
149 2 2 2
150 commit;
151 insert into parent values (1,1,1);
152 insert into child values (1,1,1);
153 insert into child select a+2,a+2,1+(a % 2) from child;
154 insert into child select a+4,a+4,1+(a % 2) from child;
155 insert into child select a+8,a+8,1+(a % 2) from child;
156 insert into child select a+16,a+16,1+(a % 2) from child;
157 insert into child select a+32,a+32,1+(a % 2) from child;
158 insert into child select a+64,a+64,1+(a % 2) from child;
159 select c, count(*) from child
160 group by c
161 order by 1,2;
162 c count(*)
163 1 64
164 2 64
165 delete from parent where a = 1;
166 select c, count(*) from child
167 group by c
168 order by 1,2;
169 c count(*)
170 2 64
171 delete from parent where a = 2;
172 delete from child;
173 delete from parent;
174 alter table child drop foreign key fkname;
175 drop table parent, child, blobchild;