fk_nonsps.out

derby database source code.good for you.

ij> CALL WAIT_FOR_POST_COMMIT();
0 rows inserted/updated/deleted
ij> -- now lets drop the foreign keys and try again
drop table f2;
0 rows inserted/updated/deleted
ij> drop table f3;
0 rows inserted/updated/deleted
ij> drop table f4;
0 rows inserted/updated/deleted
ij> CALL WAIT_FOR_POST_COMMIT();
0 rows inserted/updated/deleted
ij> select constraintname, referencecount 
	from sys.sysconstraints c, sys.sysforeignkeys fk
	where fk.keyconstraintid = c.constraintid order by constraintname;
CONSTRAINTNAME                                                                                                                  |REFERENCEC&
--------------------------------------------------------------------------------------------------------------------------------------------
PK1                                                                                                                             |1          
ij> alter table f drop constraint fk;
0 rows inserted/updated/deleted
ij> CALL WAIT_FOR_POST_COMMIT();
0 rows inserted/updated/deleted
ij> -- ok
alter table p1 drop constraint pk1;
0 rows inserted/updated/deleted
ij> CALL WAIT_FOR_POST_COMMIT();
0 rows inserted/updated/deleted
ij> -- we shouldn't be able to add an fk on p1 now
alter table f add constraint fk foreign key (x) references p1;
ERROR X0Y41: Constraint 'FK' is invalid because the referenced table APP.P1 has no primary key.  Either add a primary key to APP.P1 or explicitly specify the columns of a unique constraint that this foreign key references. 
ij> -- add the constraint and try again
alter table p1 add constraint pk1 primary key (x);
0 rows inserted/updated/deleted
ij> create table f2 (x int, constraint fk2 foreign key (x) references p1(x));
0 rows inserted/updated/deleted
ij> create table f3 (x int, constraint fk3 foreign key (x) references p1(x));
0 rows inserted/updated/deleted
ij> create table f4 (x int, constraint fk4 foreign key (x) references p1(x));
0 rows inserted/updated/deleted
ij> -- drop constraint
alter table f4 drop constraint fk4;
0 rows inserted/updated/deleted
ij> alter table f3 drop constraint fk3;
0 rows inserted/updated/deleted
ij> alter table f2 drop constraint fk2;
0 rows inserted/updated/deleted
ij> alter table p1 drop constraint pk1;
0 rows inserted/updated/deleted
ij> CALL WAIT_FOR_POST_COMMIT();
0 rows inserted/updated/deleted
ij> -- all fks are gone, right?
select constraintname 
	from sys.sysconstraints c, sys.sysforeignkeys fk
	where fk.constraintid = c.constraintid order by constraintname;
CONSTRAINTNAME                                                                                                                  
--------------------------------------------------------------------------------------------------------------------------------
ij> -- cleanup what we have done so far
drop table p1;
0 rows inserted/updated/deleted
ij> drop table p2;
0 rows inserted/updated/deleted
ij> drop table u1;
0 rows inserted/updated/deleted
ij> drop table u2;
0 rows inserted/updated/deleted
ij> drop table otherschema.p1;
0 rows inserted/updated/deleted
ij> drop schema otherschema restrict;
0 rows inserted/updated/deleted
ij> CALL WAIT_FOR_POST_COMMIT();
0 rows inserted/updated/deleted
ij> -- will return dependencies for SPS metadata queries now created by default 
-- database is created.
create table default_sysdepends_count(a int);
0 rows inserted/updated/deleted
ij> insert into default_sysdepends_count select count(*) from sys.sysdepends;
1 row inserted/updated/deleted
ij> select * from default_sysdepends_count;
A          
-----------
0          
ij> -- 
-- now we are going to do some self referencing
-- tests.
-- 
create table selfref (p char(10) not null primary key, 
		f char(10) references selfref);
0 rows inserted/updated/deleted
ij> drop table selfref;
0 rows inserted/updated/deleted
ij> CALL WAIT_FOR_POST_COMMIT();
0 rows inserted/updated/deleted
ij> -- ok
create table selfref (p char(10) not null, 
		f char(10) references selfref, 
		constraint pk primary key (p));
0 rows inserted/updated/deleted
ij> drop table selfref;
0 rows inserted/updated/deleted
ij> CALL WAIT_FOR_POST_COMMIT();
0 rows inserted/updated/deleted
ij> -- ok
create table selfref (p char(10) not null, f char(10), 
		constraint f foreign key (f) references selfref(p), 
		constraint pk primary key (p));
0 rows inserted/updated/deleted
ij> -- should fail
alter table selfref drop constraint pk;
ERROR X0Y25: Operation 'DROP CONSTRAINT' cannot be performed on object 'PK' because CONSTRAINT 'F' is dependent on that object.
ij> CALL WAIT_FOR_POST_COMMIT();
0 rows inserted/updated/deleted
ij> -- ok
alter table selfref drop constraint f;
0 rows inserted/updated/deleted
ij> alter table selfref drop constraint pk;
0 rows inserted/updated/deleted
ij> drop table selfref;
0 rows inserted/updated/deleted
ij> CALL WAIT_FOR_POST_COMMIT();
0 rows inserted/updated/deleted
ij> -- what if a pk references another pk?  should just
-- drop the direct references (nothing special, really)
create table pr1(x int not null, 
		constraint pkr1 primary key (x));
0 rows inserted/updated/deleted
ij> create table pr2(x int not null, 
		constraint pkr2 primary key(x), 
		constraint fpkr2 foreign key (x) references pr1);
0 rows inserted/updated/deleted
ij> create table pr3(x int not null, 
		constraint pkr3 primary key(x), 
		constraint fpkr3 foreign key (x) references pr2);
0 rows inserted/updated/deleted
ij> select constraintname, referencecount from sys.sysconstraints order by constraintname;
CONSTRAINTNAME                                                                                                                  |REFERENCEC&
--------------------------------------------------------------------------------------------------------------------------------------------
FPKR2                                                                                                                           |0          
FPKR3                                                                                                                           |0          
PK3                                                                                                                             |0          
PKR1                                                                                                                            |1          
PKR2                                                                                                                            |1          
PKR3                                                                                                                            |0          
ij> -- now drop constraint pkr1
alter table pr2 drop constraint fpkr2;
0 rows inserted/updated/deleted
ij> alter table pr1 drop constraint pkr1;
0 rows inserted/updated/deleted
ij> CALL WAIT_FOR_POST_COMMIT();
0 rows inserted/updated/deleted
ij> -- pkr1 and pfkr2 are gone
select constraintname, referencecount from sys.sysconstraints order by constraintname;
CONSTRAINTNAME                                                                                                                  |REFERENCEC&
--------------------------------------------------------------------------------------------------------------------------------------------
FPKR3                                                                                                                           |0          
PK3                                                                                                                             |0          
PKR2                                                                                                                            |1          
PKR3                                                                                                                            |0          
ij> -- cleanup
drop table pr3;
0 rows inserted/updated/deleted
ij> drop table pr2;
0 rows inserted/updated/deleted
ij> drop table pr1;
0 rows inserted/updated/deleted
ij> CALL WAIT_FOR_POST_COMMIT();
0 rows inserted/updated/deleted
ij> -- 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);
0 rows inserted/updated/deleted
ij> insert into default_sysdepends_count2 select count(*) from sys.sysdepends;
1 row inserted/updated/deleted
ij> select default_sysdepends_count2.a - default_sysdepends_count.a
    from default_sysdepends_count2, default_sysdepends_count;
1          
-----------
0          
ij> -- dependencies and spses
create table x (x int not null primary key, y int, constraint xfk foreign key (y) references x);
0 rows inserted/updated/deleted
ij> create table y (x int, constraint yfk foreign key (x) references x);
0 rows inserted/updated/deleted
ij> prepare ss as 'select * from x';
ij> prepare si as 'insert into x values (1,1)';
ij> prepare su as 'update x set x = x+1, y=y+1';
ij> alter table x drop constraint xfk;
0 rows inserted/updated/deleted
ij> CALL WAIT_FOR_POST_COMMIT();
0 rows inserted/updated/deleted
ij> autocommit off;
ij> -- 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;
0 rows inserted/updated/deleted
ij> commit;
ij> CALL WAIT_FOR_POST_COMMIT();
0 rows inserted/updated/deleted
ij> drop table y;
0 rows inserted/updated/deleted
ij> commit;
ij> CALL WAIT_FOR_POST_COMMIT();
0 rows inserted/updated/deleted
ij> -- ok
drop table x;
0 rows inserted/updated/deleted
ij> remove ss;
ij> remove si;
ij> remove su;
ij> drop table f3;
0 rows inserted/updated/deleted
ij> drop table f2;
0 rows inserted/updated/deleted
ij> drop table f;
0 rows inserted/updated/deleted
ij> commit;
ij> CALL WAIT_FOR_POST_COMMIT();
0 rows inserted/updated/deleted
ij> -- 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);
0 rows inserted/updated/deleted
ij> insert into default_sysdepends_count3 select count(*) from sys.sysdepends;
1 row inserted/updated/deleted
ij> select default_sysdepends_count3.a - default_sysdepends_count.a
    from default_sysdepends_count3, default_sysdepends_count;
1          
-----------
0          
ij> -- ** insert fkdml.sql
autocommit on;
ij> --
-- DML and foreign keys
--
drop table s;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'S' because it does not exist.
ij> drop table f3;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'F3' because it does not exist.
ij> drop table f2;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'F2' because it does not exist.
ij> drop table f;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'F' because it does not exist.
ij> drop table p;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'P' because it does not exist.
ij> CALL WAIT_FOR_POST_COMMIT();
0 rows inserted/updated/deleted
ij> create table p (x int not null, y int not null, constraint pk primary key (x,y));
0 rows inserted/updated/deleted
ij> create table f (x int, y int, constraint fk foreign key (x,y) references p);
0 rows inserted/updated/deleted
ij> insert into p values (1,1);
1 row inserted/updated/deleted
ij> -- ok
insert into f values (1,1);
1 row inserted/updated/deleted
ij> -- fail
insert into f values (2,1);
ERROR 23503: INSERT on table 'F' caused a violation of foreign key constraint 'FK' for key (2,1).  The statement has been rolled back.
ij> insert into f values (1,2);
ERROR 23503: INSERT on table 'F' caused a violation of foreign key constraint 'FK' for key (1,2).  The statement has been rolled back.
ij> -- nulls are ok
insert into f values (1,null);
1 row inserted/updated/deleted
ij> insert into f values (null,null);
1 row inserted/updated/deleted
ij> insert into f values (1,null);
1 row inserted/updated/deleted
ij> -- update on pk, fail
update p set x = 2;
ERROR 23503: UPDATE on table 'P' caused a violation of foreign key constraint 'FK' for key (1,1).  The statement has been rolled back.
ij> update p set y = 2;
ERROR 23503: UPDATE on table 'P' caused a violation of foreign key constraint 'FK' for key (1,1).  The statement has been rolled back.
ij> update p set x = 1, y = 2;
ERROR 23503: UPDATE on table 'P' caused a violation of foreign key constraint 'FK' for key (1,1).  The statement has been rolled back.
ij> update p set x = 2, y = 1;
ERROR 23503: UPDATE on table 'P' caused a violation of foreign key constraint 'FK' for key (1,1).  The statement has been rolled back.
ij> update p set x = 2, y = 2;
ERROR 23503: UPDATE on table 'P' caused a violation of foreign key constraint 'FK' for key (1,1).  The statement has been rolled back.
ij> -- ok
update p set x = 1, y = 1;
1 row inserted/updated/deleted
ij> -- delete pk, fail
delete from p;
ERROR 23503: DELETE on table 'P' caused a violation of foreign key constraint 'FK' for key (1,1).  The statement has been rolled back.
ij> -- delete fk, ok
delete from f;
4 rows inserted/updated/deleted
ij> insert into f values (1,1);
1 row inserted/updated/deleted
ij> -- update fk, fail
update f set x = 2;
ERROR 23503: UPDATE on table 'F' caused a violation of foreign key constraint 'FK' for key (2,1).  The statement has been rolled back.
ij> update f set y = 2;
ERROR 23503: UPDATE on table 'F' caused a violation of foreign key constraint 'FK' for key (1,2).  The statement has been rolled back.
ij> update f set x = 1, y = 2;
ERROR 23503: UPDATE on table 'F' caused a violation of foreign key constraint 'FK' for key (1,2).  The statement has been rolled back.
ij> update f set x = 2, y = 1;
ERROR 23503: UPDATE on table 'F' caused a violation of foreign key constraint 'FK' for key (2,1).  The statement has been rolled back.
ij> -- update fk, ok
update f set x = 1, y = 1;
1 row inserted/updated/deleted
ij> -- nulls ok
update f set x = null, y = 1;
1 row inserted/updated/deleted
ij> update f set x = 1, y = null;
1 row inserted/updated/deleted
ij> update f set x = null, y = null;
1 row inserted/updated/deleted
ij> delete from f;
1 row inserted/updated/deleted
ij> insert into f values (1,1);
1 row inserted/updated/deleted