fk_nonsps.sql

derby database source code.good for you.

drop table pr3;
drop table pr2;
drop table pr1;
CALL WAIT_FOR_POST_COMMIT();

-- should return 0, confirm no unexpected dependencies
-- verify that all rows in sys.sysdepends got dropped 
-- apart from sps dependencies
create table default_sysdepends_count2(a int);
insert into default_sysdepends_count2 select count(*) from sys.sysdepends;
select default_sysdepends_count2.a - default_sysdepends_count.a
    from default_sysdepends_count2, default_sysdepends_count;

-- dependencies and spses
create table x (x int not null primary key, y int, constraint xfk foreign key (y) references x);
create table y (x int, constraint yfk foreign key (x) references x);
prepare ss as 'select * from x';
prepare si as 'insert into x values (1,1)';
prepare su as 'update x set x = x+1, y=y+1';

alter table x drop constraint xfk;
CALL WAIT_FOR_POST_COMMIT();

autocommit off;

-- drop the referenced fk, should force su to be recompiled
-- since it no longer has to check the foreign key table
alter table y drop constraint yfk;
commit;
CALL WAIT_FOR_POST_COMMIT();

drop table y;
commit;
CALL WAIT_FOR_POST_COMMIT();

-- ok
drop table x;

remove ss;
remove si;
remove su;

drop table f3;
drop table f2;
drop table f;
commit;
CALL WAIT_FOR_POST_COMMIT();

-- verify that all rows in sys.sysdepends got dropped 
-- apart from sps dependencies
-- Since, with beetle 5352; we create metadata SPS for network server at database bootup time
-- so the dependencies for SPS are there.
 
create table default_sysdepends_count3(a int);
insert into default_sysdepends_count3 select count(*) from sys.sysdepends;
select default_sysdepends_count3.a - default_sysdepends_count.a
    from default_sysdepends_count3, default_sysdepends_count;

-- ** insert fkdml.sql
autocommit on;
--
-- DML and foreign keys
--
drop table s;
drop table f3;
drop table f2;
drop table f;
drop table p;
CALL WAIT_FOR_POST_COMMIT();


create table p (x int not null, y int not null, constraint pk primary key (x,y));
create table f (x int, y int, constraint fk foreign key (x,y) references p);

insert into p values (1,1);

-- ok
insert into f values (1,1);

-- fail
insert into f values (2,1);
insert into f values (1,2);

-- nulls are ok
insert into f values (1,null);
insert into f values (null,null);
insert into f values (1,null);

-- update on pk, fail
update p set x = 2;
update p set y = 2;
update p set x = 1, y = 2;
update p set x = 2, y = 1;
update p set x = 2, y = 2;


-- ok
update p set x = 1, y = 1;

-- delete pk, fail
delete from p;

-- delete fk, ok
delete from f;

insert into f values (1,1);

-- update fk, fail
update f set x = 2;
update f set y = 2;
update f set x = 1, y = 2;
update f set x = 2, y = 1;

-- update fk, ok
update f set x = 1, y = 1;

-- nulls ok
update f set x = null, y = 1;
update f set x = 1, y = null;
update f set x = null, y = null;

delete from f;

insert into f values (1,1);
insert into p values (2,2);

-- ok
update f set x = x+1, y = y+1;

select * from f;
select * from p;

-- ok
update p set x = x+1, y = y+1;

-- fail
update p set x = x+1, y = y+1;


-- 
-- BOUNDARY CONDITIONS
--
delete from f;
delete from p;

insert into f select * from f;
delete from p where x = 9999;
update p set x = x+1, y=y+1 where x = 999;

insert into p values (1,1);
insert into f values (1,1);
update p set x = x+1, y=y+1 where x = 999;
delete from p where x = 9999;
insert into f select * from f;

--
-- test a CURSOR
--
delete from f;
delete from p;
insert into p values (1,1);
insert into f values (1,1);

autocommit off;
get cursor  c as 'select * from p for update of x';
next c;

-- fail
update p set x = 666 where current of c;
close c;

get cursor  c as 'select * from f for update of x';
next c;

-- fail
update f set x = 666 where current of c;
close c;

commit;
autocommit on;

delete from f;
delete from p;
insert into p values (0,0), (1,1), (2,2), (3,3), (4,4);
insert into f values (1,1);

-- lets add some additional foreign keys to the mix
create table f2 (x int, y int, constraint fk2 foreign key (x,y) references p);
insert into f2 values (2,2);
create table f3 (x int, y int, constraint fk3 foreign key (x,y) references p);
insert into f3 values (3,3);

-- ok
update p set x = x+1, y = y+1;

-- error, fk1
update p set x = x+1;
update p set y = y+1;
update p set x = x+1, y = y+1;

-- fail of fk3
update p set y = 666 where y = 3;

-- fail of fk2
update p set x = 666 where x = 2;

-- cleanup
drop table f;
drop table f2;
drop table f3;
drop table p;
CALL WAIT_FOR_POST_COMMIT();

--
-- SELF REFERENCING
--
create table s (x int not null primary key, y int references s, z int references s);

-- ok
insert into s values (1,null,null);

-- ok
update s set y = 1;

-- fail
update s set z = 2;

-- ok
update s set z = 1;

-- ok
insert into s values (2, 1, 1);

-- ok
update s set x = 666 where x = 2;

-- ok
update s set x = x+1, y = y+1, z = z+1;

delete from s;

-- ok
insert into s values (1,null,null);
-- ok
insert into s values (2,null,null);

-- ok
update s set y = 2 where x = 1;
-- ok
update s set z = 1 where x = 2;

select * from s;

-- fail 
update s set x = 0 where x = 1; 

--
-- Now we are going to do a short but sweet
-- check to make sure we are actually hitting
-- the correct columns
--
create table p (c1 char(1), y int not null, c2 char(1), x int not null, constraint pk primary key (x,y));
create table f (x int, s smallint, y int, constraint fk foreign key (x,y) references p);

insert into p values ('1',1,'1',1);
-- ok
insert into f values (1,1,1);

insert into p values ('0',0,'0',0);
-- ok
update p set x = x+1, y=y+1;

-- fail
delete from p where y = 1;

-- fail
insert into f values (1,1,4);

delete from f;
delete from p;

--
-- Lets make sure we don't interact poorly with
-- 'normal' deferred dml

insert into p values ('1',1,'1',1);
insert into f values (1,1,1);

insert into p values ('0',0,'0',0);
-- ok
update p set x = x+1, y=y+1 where x < (select max(x)+10000 from p);

-- fail
delete from p where y = 1 and y in (select y from p);

-- inserts
create table f2 (x int, t smallint, y int);
insert into f2 values (1,1,4);

-- fail
insert into f select * from f2;


-- ok
insert into f2 values (1,1,1);
insert into f select * from f2 where y = 1;

drop table f2;
drop table f;
drop table p;
CALL WAIT_FOR_POST_COMMIT();

--
-- PREPARED STATEMENTS
--
drop table f;
drop table p;
--the reason for this wait call is to wait unitil system tables row deletes
--are completed other wise we will get different order fk checks
--that will lead different error messages depending on when post commit thread runs
CALL WAIT_FOR_POST_COMMIT();
 
prepare s as 
	'create table p (w int not null primary key, x int references p, y int not null, z int not null, constraint uyz unique (y,z))';
execute s;
remove s;

prepare s as 
	'create table f (w int references p, x int, y int, z int, constraint fk foreign key (y,z) references p (y,z))';
execute s;
remove s;

prepare s as 
	'alter table f drop constraint fk';
execute s;
remove s;
--the reason for this wait call is to wait unitil system tables row deletes
--are completed other wise we will get different order fk checks
CALL WAIT_FOR_POST_COMMIT(); 
prepare s as 
	'alter table f add constraint fk foreign key (y,z) references p (y,z)';
execute s;
remove s;

prepare sf as 
	'insert into f values (1,1,1,1)';

prepare sp as 
	'insert into p values (1,1,1,1)';

-- fail
execute sf;

-- ok
execute sp;
execute sf;

insert into p values (2,2,2,2);


remove sf;
prepare sf as 
	'update f set w=w+1, x = x+1, y=y+1, z=z+1';
-- ok
execute sf;

remove sp;
prepare sp as 
	'update p set w=w+1, x = x+1, y=y+1, z=z+1';
-- ok
execute sp;

remove sp;
prepare sp as 
	'delete from p where x =1';
-- ok
execute sp;

remove sp;
remove sf;
drop table f;
drop table p;
CALL WAIT_FOR_POST_COMMIT();
drop procedure WAIT_FOR_POST_COMMIT;