Debian Codegraph @

mysql-test/suite/ndb/r/ndb_fk_setnull.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,
10 c int,
11 unique(b) using hash,
12 index(c)) engine = ndb;
13 Warnings:
14 Warning 1121 Ndb does not support unique index on NULL valued attributes, index access with NULL value will become full table scan
15 alter table child add constraint fkname foreign key (b) references parent (a) on update restrict on delete set null;
16 insert into parent values (1,1,1),(2,2,2);
17 insert into child values (1,1,1),(2,2,2);
18 insert into child values (3,3,3);
19 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 SET NULL ON UPDATE RESTRICT)
20 update child set b = 3 where a = 1;
21 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 SET NULL ON UPDATE RESTRICT)
22 begin;
23 delete from parent where a = 1;
24 select * from child order by 1,2,3;
25 a b c
26 1 NULL 1
27 2 2 2
28 commit;
29 delete from child;
30 delete from parent;
31 alter table child drop foreign key fkname;
32 alter table child add constraint fkname foreign key (c) references parent (a) on update restrict on delete set null;
33 insert into parent values (1,1,1),(2,2,2);
34 insert into child values (1,1,1),(2,2,2),(3,3,1);
35 update child set c = 3 where a = 1;
36 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 SET NULL ON UPDATE RESTRICT)
37 insert into child values (4,4,4);
38 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 SET NULL ON UPDATE RESTRICT)
39 begin;
40 delete from parent where a = 1;
41 select * from child order by 1,2,3;
42 a b c
43 1 1 NULL
44 2 2 2
45 3 3 NULL
46 commit;
47 delete from child;
48 delete from parent;
49 alter table child drop foreign key fkname;
50 alter table child add constraint fkname foreign key (b) references parent (b) on update set null on delete set null;
51 insert into parent values (1,1,1),(2,2,2);
52 insert into child values (1,1,1),(2,2,2);
53 update child set b = 3 where a = 1;
54 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 SET NULL ON UPDATE SET NULL)
55 insert into child values (3,3,3);
56 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 SET NULL ON UPDATE SET NULL)
57 begin;
58 update parent set b = 3 where a = 1;
59 select * from child order by 1,2,3;
60 a b c
61 1 NULL 1
62 2 2 2
63 rollback;
64 begin;
65 delete from parent where a = 1;
66 select * from child order by 1,2,3;
67 a b c
68 1 NULL 1
69 2 2 2
70 commit;
71 delete from child;
72 delete from parent;
73 alter table child drop foreign key fkname;
74 alter table child add constraint fkname foreign key (c) references parent (b) on update set null on delete set null;
75 insert into parent values (1,1,1),(2,2,2);
76 insert into child values (1,1,1),(2,2,2),(3,3,1);
77 update child set c = 3 where a = 1;
78 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 SET NULL ON UPDATE SET NULL)
79 insert into child values (4,4,4);
80 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 SET NULL ON UPDATE SET NULL)
81 begin;
82 update parent set b = 3 where a = 1;
83 select * from child order by 1,2,3;
84 a b c
85 1 1 NULL
86 2 2 2
87 3 3 NULL
88 rollback;
89 begin;
90 delete from parent where a = 1;
91 select * from child order by 1,2,3;
92 a b c
93 1 1 NULL
94 2 2 2
95 3 3 NULL
96 commit;
97 delete from child;
98 delete from parent;
99 alter table child drop foreign key fkname;
100 drop table parent, child;