Debian Codegraph @

mysql-test/suite/ndb/t/ndb_fk_setnull.test

raw
1 -- source include/have_ndb.inc
2
3 create table parent (
4 a int primary key auto_increment,
5 b int not null,
6 c int not null,
7 unique(b) using hash,
8 index(c)) engine = ndb;
9
10 create table child (
11 a int primary key auto_increment,
12 b int,
13 c int,
14 unique(b) using hash,
15 index(c)) engine = ndb;
16
17 ###
18 ### PK vs UK
19 ###
20 alter table child add constraint fkname foreign key (b) references parent (a) on update restrict on delete set null;
21
22 insert into parent values (1,1,1),(2,2,2);
23 insert into child values (1,1,1),(2,2,2);
24
25 --error 1452
26 insert into child values (3,3,3);
27
28 --error 1452
29 update child set b = 3 where a = 1;
30
31 begin;
32 delete from parent where a = 1;
33 select * from child order by 1,2,3;
34 commit;
35
36 delete from child;
37 delete from parent;
38
39 alter table child drop foreign key fkname;
40
41 ###
42 ### PK vs OI
43 ###
44 alter table child add constraint fkname foreign key (c) references parent (a) on update restrict on delete set null;
45
46 insert into parent values (1,1,1),(2,2,2);
47 insert into child values (1,1,1),(2,2,2),(3,3,1);
48
49 --error 1452
50 update child set c = 3 where a = 1;
51
52 --error 1452
53 insert into child values (4,4,4);
54
55 begin;
56 delete from parent where a = 1;
57 select * from child order by 1,2,3;
58 commit;
59
60 delete from child;
61 delete from parent;
62
63 alter table child drop foreign key fkname;
64
65 ###
66 ### UK vs UK
67 ###
68 alter table child add constraint fkname foreign key (b) references parent (b) on update set null on delete set null;
69
70 insert into parent values (1,1,1),(2,2,2);
71 insert into child values (1,1,1),(2,2,2);
72
73 --error 1452
74 update child set b = 3 where a = 1;
75
76 --error 1452
77 insert into child values (3,3,3);
78
79 begin;
80 update parent set b = 3 where a = 1;
81 select * from child order by 1,2,3;
82 rollback;
83
84 begin;
85 delete from parent where a = 1;
86 select * from child order by 1,2,3;
87 commit;
88
89 delete from child;
90 delete from parent;
91
92 alter table child drop foreign key fkname;
93
94 ###
95 ### UK vs OI
96 ###
97 alter table child add constraint fkname foreign key (c) references parent (b) on update set null on delete set null;
98
99 insert into parent values (1,1,1),(2,2,2);
100 insert into child values (1,1,1),(2,2,2),(3,3,1);
101
102 --error 1452
103 update child set c = 3 where a = 1;
104
105 --error 1452
106 insert into child values (4,4,4);
107
108 begin;
109 update parent set b = 3 where a = 1;
110 select * from child order by 1,2,3;
111 rollback;
112
113 begin;
114 delete from parent where a = 1;
115 select * from child order by 1,2,3;
116 commit;
117
118 delete from child;
119 delete from parent;
120
121 alter table child drop foreign key fkname;
122
123 drop table parent, child;