-- CIRCULAR SELF REFERENCING FK
-- ON DELETE CASCADE
drop table testB if exists;
create cached table testB(id integer, parent integer, ref integer,
 data varchar(200), unique(parent), primary key (id),foreign key (parent)
 references testB(id) on delete cascade, foreign key (id)
 references testB(parent) on delete cascade);
/*u1*/insert into testB values(100,100,1,'xxxx');
/*u1*/insert into testB values(200,200,1,'xxxx');
/*u1*/delete from testB where id=100;
/*r200,200,1,xxxx*/select * from testB
-- NORMAL SELF REFERENCING FK
-- ON DELETE CASCADE
drop table testB if exists;
create cached table testB(id integer, parent integer, ref integer,
 data varchar(200), unique (id), foreign key (parent)
 references testB(id) on delete cascade);
/*u1*/insert into testB values(100,100,1,'xxxx');
/*u1*/insert into testB values(101,100,1,'xxxx');
/*u1*/insert into testB values(102,100,1,'xxxx');
/*u1*/insert into testB values(200,200,1,'xxxx');
/*u1*/delete from testB where id=100;
/*r200,200,1,xxxx*/select * from testB
-- ON DELETE SET NULL
drop table testB if exists;
create cached table testB(id integer, parent integer, ref integer,
 data varchar(200), unique (id), foreign key (parent)
 references testB(id) on delete set null);
/*u1*/insert into testB values(100,100,1,'xxxx');
/*u1*/insert into testB values(101,100,1,'xxxx');
/*u1*/insert into testB values(102,100,1,'xxxx');
/*u1*/insert into testB values(200,200,1,'xxxx');
/*u1*/delete from testB where id=100;
/*r
 101,NULL,1,xxxx
 102,NULL,1,xxxx
 200,200,1,xxxx
*/select * from testB order by id
/*c2*/select * from testB where parent is null
-- ON DELETE SET DEFAULT
drop table testB if exists;
create cached table testB(id integer, parent integer default 20, ref integer,
 data varchar(200), unique (id),foreign key (parent)
 references testB(id) on delete set default);
/*u1*/insert into testB values(20,20,1,'xxxx');
/*u1*/insert into testB values(100,100,1,'xxxx');
/*u1*/insert into testB values(101,100,1,'xxxx');
/*u1*/insert into testB values(200,200,1,'xxxx');
/*u1*/delete from testB where id=100;
/*r
 20,20,1,xxxx
 101,20,1,xxxx
 200,200,1,xxxx
*/select * from testB order by id
/*c2*/select * from testB where parent=20
-- CHAINED SELF REFERENCING FK
-- ON DELETE CASCADE
drop table testA if exists;
create cached table testA(a int primary key,b int,
    foreign key(b) references testA(a) on update cascade on delete cascade);
insert into testA(a,b) values(1,1);
insert into testA(a,b) values(2,1);
insert into testA(a,b) values(3,1);
insert into testA(a,b) values(4,2);
insert into testA(a,b) values(5,2);
insert into testA(a,b) values(6,2);
insert into testA(a,b) values(7,3);
insert into testA(a,b) values(8,3);
insert into testA(a,b) values(9,3);
/*u9*/update testA set a = a+1;
/*r3*/select count(*) from testA where b=4;
/*u9*/update testA set a = a-1;
/*r0*/select count(*) from testA where b=4;
/*r3*/select count(*) from testA where b=3;
/*u1*/delete from testA where a=1;
/*r0*/select count(*) from testA;
-- bug 870835
-- MIXED SELF AND FORWARD REFERENCE
-- UPDATE ISSUE
CREATE CACHED TABLE GroupSubject (
 description VARCHAR(10),
 parent BIGINT,
 admin BIGINT NOT NULL,
 id_ BIGINT,
 UNIQUE ( id_ ));
CREATE CACHED TABLE UserSubject (
 subjectName VARCHAR(10) NOT NULL,
 id_ BIGINT,
 UNIQUE ( id_ ),
 PRIMARY KEY ( subjectName ));
ALTER TABLE GroupSubject ADD CONSTRAINT
 GroupSubject_REF_parent
 FOREIGN KEY ( parent )
 REFERENCES GroupSubject ( id_ );
ALTER TABLE GroupSubject ADD CONSTRAINT
 GroupSubject_REF_admin
 FOREIGN KEY ( admin )
 REFERENCES UserSubject ( id_ );
insert into UserSubject values ('admin', 100);
insert into GroupSubject values (null, null, 100, 200);
/*u1*/update GroupSubject set description = null,
 parent = null, admin = 100 where id_ = 200;
/*r
 admin,100
*/select * from UserSubject
/*r
 NULL,NULL,100,200
*/select * from GroupSubject

-- MULTIPLE FK ISSUE
drop table testA if exists;
drop table testB if exists;
drop table testC if exists;
create cached table testA(id integer primary key);
create cached table testB(id integer, foreign key (id) references testA(id) on delete cascade);
create cached table testC(id integer, foreign key (id) references testA(id));
insert into testA values(1);
insert into testA values(2);
insert into testB values(1);
insert into testB values(2);
insert into testC values(1);
/*e*/delete from testA
/*c2*/select * from testB
/*c1*/select * from testC
/*u1*/delete from testA where id=2
/*c1*/select * from testA
/*c1*/select * from testB
/*c1*/select * from testC

-- INVALID SET DEFAULT
/*e*/create cached table testE(id integer, foreign key (id) references testA(id) on delete set default);
/*e*/create cached table testE(id integer primary key, idref integer, foreign key (idref) references teste(id) on delete set default);
create cached table testE(id integer primary key, idref integer);
/*e*/alter table testE add foreign key(idref) references testE(id) on delete set default;
alter table testE alter column idref set default 10;
alter table testE add foreign key(idref) references testE(id) on delete set default;
/*e*/alter table testE alter column idref drop default;
