HSQLDB example source code file (TestSelfFKModes.txt)

This example HSQLDB source code file (TestSelfFKModes.txt) is included in the DevDaily.com "Java Source Code Warehouse" project. The intent of this project is to help you "Learn Java by Example" ^TM.

Java - HSQLDB tags/keywords

bigint, cascade, delete, delete, fk, groupsubject, groupsubject, on, on, referencing, self, set, table, table

The HSQLDB TestSelfFKModes.txt source code

-- 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;