Debian Codegraph @

mysql-test/suite/ndb/r/ndb_fk_deferred_cascade.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 grandchild (
14 a int primary key auto_increment,
15 b int not null,
16 c int not null,
17 unique(b) using hash,
18 index(c)) engine = ndb;
19 alter table child add constraint fkname foreign key (a) references parent (a)
20 on delete cascade on update restrict;
21 alter table grandchild add constraint fkname_child foreign key (a) references child
22 (a) on delete cascade on update restrict;
23 insert into parent values (1,1,1),(2,2,2);
24 insert into child values (1,1,1),(2,2,2);
25 insert into grandchild values (1,1,1),(2,2,2);
26 set ndb_deferred_constraints = 0;
27 begin;
28 delete from parent where a = 1;
29 select * from child order by 1,2,3;
30 a b c
31 2 2 2
32 select * from grandchild order by 1,2,3;
33 a b c
34 2 2 2
35 rollback;
36 set ndb_deferred_constraints = 1;
37 begin;
38 delete from parent where a = 1;
39 select * from child order by 1,2,3;
40 a b c
41 1 1 1
42 2 2 2
43 select * from grandchild order by 1,2,3;
44 a b c
45 1 1 1
46 2 2 2
47 commit;
48 set ndb_deferred_constraints = 0;
49 select * from child order by 1,2,3;
50 a b c
51 2 2 2
52 select * from grandchild order by 1,2,3;
53 a b c
54 2 2 2
55 drop table grandchild, child, parent;
56 create table t1 (
57 a int primary key,
58 b int null,
59 unique(b) using hash) engine = ndb;
60 Warnings:
61 Warning 1121 Ndb does not support unique index on NULL valued attributes, index access with NULL value will become full table scan
62 select max(a) into @maxa
63 from t1;
64 select min(a) into @mina
65 from t1;
66 select count(*) from t1;
67 count(*)
68 1025
69 select * from t1 p
70 where not exists (select 1 from t1 c where c.a = p.b);
71 a b
72 0 NULL
73 alter table t1 add constraint fkname
74 foreign key (b) references t1 (a) on delete cascade on update restrict;
75 set ndb_deferred_constraints = 0;
76 begin;
77 update t1 set b = a + 1;
78 Got one of the listed errors
79 rollback;
80 set ndb_deferred_constraints = 1;
81 begin;
82 update t1 set b = a + 1;
83 commit;
84 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `fkname` FOREIGN KEY (`b`) REFERENCES `t1` (`a`) ON DELETE CASCADE ON UPDATE RESTRICT)
85 begin;
86 update t1 set b = a + 1;
87 update t1 set b = null where a = @maxa;
88 commit;
89 select count(*) from t1;
90 count(*)
91 1025
92 select * from t1 p
93 where not exists (select 1 from t1 c where c.a = p.b);
94 a b
95 1024 NULL
96 set ndb_deferred_constraints = 0;
97 begin;
98 delete from t1 where a = @maxa - 1;
99 select * from t1;
100 a b
101 1024 NULL
102 rollback;
103 set ndb_deferred_constraints = 1;
104 begin;
105 delete from t1 where a = @maxa - 5;
106 select count(*) from t1;
107 count(*)
108 1024
109 commit;
110 select * from t1 order by 1,2;
111 a b
112 1020 1021
113 1021 1022
114 1022 1023
115 1023 1024
116 1024 NULL
117 select * from t1 where b = @maxa;
118 a b
119 1023 1024
120 select * from t1 where b = @maxa - 1;
121 a b
122 1022 1023
123 select * from t1 where b = @maxa - 2;
124 a b
125 1021 1022
126 select * from t1 where b = @maxa - 3;
127 a b
128 1020 1021
129 drop table t1;