81 lines
2.0 KiB
Plaintext
81 lines
2.0 KiB
Plaintext
-- source include/have_ndb.inc
|
|
|
|
create table emp (
|
|
id int primary key auto_increment,
|
|
name varchar(256) not null,
|
|
manager int,
|
|
index(manager)) engine = ndb;
|
|
|
|
alter table emp add constraint fkname foreign key (manager) references emp(id) on update restrict on delete cascade;
|
|
|
|
insert into emp values
|
|
(1, 'larry', null)
|
|
,(2, 'eddie', 1)
|
|
,(3, 'tomas', 2)
|
|
,(4, 'ricky', 2)
|
|
,(5, 'edwin', 2)
|
|
,(6, 'bockie', 3)
|
|
,(7, 'jonas', 6)
|
|
,(8, 'magnus', 6)
|
|
,(9, 'frasse', 6)
|
|
,(10, 'samuel', 7)
|
|
,(11, 'jonatan', 7)
|
|
,(12, 'wille', 8);
|
|
|
|
select * from emp order by 1;
|
|
begin;
|
|
# lets see what happens if we rif cluster
|
|
delete from emp where id=6;
|
|
select * from emp order by 1;
|
|
rollback;
|
|
select * from emp order by 1;
|
|
|
|
alter table emp drop foreign key fkname;
|
|
|
|
drop table emp;
|
|
|
|
create table tree (
|
|
id int primary key,
|
|
value varchar(256) not null,
|
|
parent int null,
|
|
left_child int null,
|
|
right_child int null,
|
|
index (parent),
|
|
unique (left_child),
|
|
unique (right_child)) engine = ndb;
|
|
|
|
#
|
|
# insert following tree
|
|
# 3
|
|
# 1 5
|
|
# 0 2 4 6
|
|
insert into tree values (0, '3-root', null, 1,2);
|
|
insert into tree values (1, '1-inner', 0, 3,4);
|
|
insert into tree values (2, '5-inner', 0, 5,6);
|
|
insert into tree values (3, '0-leaf', 1, null, null);
|
|
insert into tree values (4, '2-leaf', 1, null, null);
|
|
insert into tree values (5, '4-leaf', 2, null, null);
|
|
insert into tree values (6, '6-leaf', 2, null, null);
|
|
|
|
select * from tree order by 1;
|
|
|
|
alter table tree add constraint fk_parent foreign key (parent) references tree(id) on update restrict on delete cascade;
|
|
alter table tree add constraint fk_left foreign key (left_child) references tree(id) on update restrict on delete cascade;
|
|
|
|
begin;
|
|
delete from tree where value = '5-inner';
|
|
select * from tree order by 1;
|
|
rollback;
|
|
|
|
begin;
|
|
delete from tree where value = '4-leaf';
|
|
select * from tree order by 1;
|
|
rollback;
|
|
|
|
begin;
|
|
delete from tree where value = '3-root';
|
|
select * from tree order by 1;
|
|
commit;
|
|
|
|
drop table tree;
|