130 lines
2.9 KiB
Plaintext
130 lines
2.9 KiB
Plaintext
create table parent (
|
|
a int primary key auto_increment,
|
|
b int not null,
|
|
c int not null,
|
|
unique(b) using hash,
|
|
index(c)) engine = ndb;
|
|
create table child (
|
|
a int primary key auto_increment,
|
|
b int not null,
|
|
c int not null,
|
|
unique(b) using hash,
|
|
index(c)) engine = ndb;
|
|
create table grandchild (
|
|
a int primary key auto_increment,
|
|
b int not null,
|
|
c int not null,
|
|
unique(b) using hash,
|
|
index(c)) engine = ndb;
|
|
alter table child add constraint fkname foreign key (a) references parent (a)
|
|
on delete cascade on update restrict;
|
|
alter table grandchild add constraint fkname_child foreign key (a) references child
|
|
(a) on delete cascade on update restrict;
|
|
insert into parent values (1,1,1),(2,2,2);
|
|
insert into child values (1,1,1),(2,2,2);
|
|
insert into grandchild values (1,1,1),(2,2,2);
|
|
set ndb_deferred_constraints = 0;
|
|
begin;
|
|
delete from parent where a = 1;
|
|
select * from child order by 1,2,3;
|
|
a b c
|
|
2 2 2
|
|
select * from grandchild order by 1,2,3;
|
|
a b c
|
|
2 2 2
|
|
rollback;
|
|
set ndb_deferred_constraints = 1;
|
|
begin;
|
|
delete from parent where a = 1;
|
|
select * from child order by 1,2,3;
|
|
a b c
|
|
1 1 1
|
|
2 2 2
|
|
select * from grandchild order by 1,2,3;
|
|
a b c
|
|
1 1 1
|
|
2 2 2
|
|
commit;
|
|
set ndb_deferred_constraints = 0;
|
|
select * from child order by 1,2,3;
|
|
a b c
|
|
2 2 2
|
|
select * from grandchild order by 1,2,3;
|
|
a b c
|
|
2 2 2
|
|
drop table grandchild, child, parent;
|
|
create table t1 (
|
|
a int primary key,
|
|
b int null,
|
|
unique(b) using hash) engine = ndb;
|
|
Warnings:
|
|
Warning 1121 Ndb does not support unique index on NULL valued attributes, index access with NULL value will become full table scan
|
|
select max(a) into @maxa
|
|
from t1;
|
|
select min(a) into @mina
|
|
from t1;
|
|
select count(*) from t1;
|
|
count(*)
|
|
1025
|
|
select * from t1 p
|
|
where not exists (select 1 from t1 c where c.a = p.b);
|
|
a b
|
|
0 NULL
|
|
alter table t1 add constraint fkname
|
|
foreign key (b) references t1 (a) on delete cascade on update restrict;
|
|
set ndb_deferred_constraints = 0;
|
|
begin;
|
|
update t1 set b = a + 1;
|
|
Got one of the listed errors
|
|
rollback;
|
|
set ndb_deferred_constraints = 1;
|
|
begin;
|
|
update t1 set b = a + 1;
|
|
commit;
|
|
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)
|
|
begin;
|
|
update t1 set b = a + 1;
|
|
update t1 set b = null where a = @maxa;
|
|
commit;
|
|
select count(*) from t1;
|
|
count(*)
|
|
1025
|
|
select * from t1 p
|
|
where not exists (select 1 from t1 c where c.a = p.b);
|
|
a b
|
|
1024 NULL
|
|
set ndb_deferred_constraints = 0;
|
|
begin;
|
|
delete from t1 where a = @maxa - 1;
|
|
select * from t1;
|
|
a b
|
|
1024 NULL
|
|
rollback;
|
|
set ndb_deferred_constraints = 1;
|
|
begin;
|
|
delete from t1 where a = @maxa - 5;
|
|
select count(*) from t1;
|
|
count(*)
|
|
1024
|
|
commit;
|
|
select * from t1 order by 1,2;
|
|
a b
|
|
1020 1021
|
|
1021 1022
|
|
1022 1023
|
|
1023 1024
|
|
1024 NULL
|
|
select * from t1 where b = @maxa;
|
|
a b
|
|
1023 1024
|
|
select * from t1 where b = @maxa - 1;
|
|
a b
|
|
1022 1023
|
|
select * from t1 where b = @maxa - 2;
|
|
a b
|
|
1021 1022
|
|
select * from t1 where b = @maxa - 3;
|
|
a b
|
|
1020 1021
|
|
drop table t1;
|