fk_nonsps.out

derby database source code.good for you.

ij> -- ** insert fkBulkInsert.sql
--
-- test foreign key checking.  first
-- check that we do proper checking.
-- then make sure that dependencies interact
-- correctly with foreign keys
CREATE PROCEDURE WAIT_FOR_POST_COMMIT() DYNAMIC RESULT SETS 0 LANGUAGE JAVA EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.T_Access.waitForPostCommitToFinish' PARAMETER STYLE JAVA;
0 rows inserted/updated/deleted
ij> create table p (c1 char(1), y int not null, c2 char(1), x int not null, constraint pk primary key (x,y));
0 rows inserted/updated/deleted
ij> create table f (x int not null, s smallint, y int not null, constraint fk foreign key (x,y) references p);
0 rows inserted/updated/deleted
ij> insert into p values ('1',1,'1',1);
1 row inserted/updated/deleted
ij> -- should pass, foreign key constraint satisfied
insert into f 
values 
	(1,1,1),
	(1,1,1),
	(1,1,1),	
	(1,1,1),
	(1, 0, 1),
	(1,1,1),
	(1,0,1),
	(1, 0, 1);
8 rows inserted/updated/deleted
ij> -- should FAIL, foreign key constraint violated
delete from f;
8 rows inserted/updated/deleted
ij> insert into f 
values 
	(1,1,1),
	(1,1,1),
	(1,1,1),	
	(1,1,1),
	(1, 1, 1),
	(2,1,666),
	(1,1,0),
	(0, 1, 0);
ERROR 23503: INSERT on table 'F' caused a violation of foreign key constraint 'FK' for key (2,666).  The statement has been rolled back.
ij> drop table f;
0 rows inserted/updated/deleted
ij> CALL WAIT_FOR_POST_COMMIT();
0 rows inserted/updated/deleted
ij> -- make sure boundary conditions are ok, null insert set
create table f (c1 char(1), y int, c2 char(1), x int, constraint fk foreign key (x,y) references p);
0 rows inserted/updated/deleted
ij> insert into f 
	select * from p where 1 = 2;
0 rows inserted/updated/deleted
ij> drop table f;
0 rows inserted/updated/deleted
ij> drop table p;
0 rows inserted/updated/deleted
ij> CALL WAIT_FOR_POST_COMMIT();
0 rows inserted/updated/deleted
ij> -- self referencing
create table s (x int not null primary key, y int references s, z int references s);
0 rows inserted/updated/deleted
ij> -- ok
insert into s 
values 
	(1,1,1),
	(2,1,1),
	(10,2,1),	
	(11,1,2),
	(12,4,4),
	(4,1,1),
	(13,null,null),
	(14,1,2),
	(15,null, 1);
9 rows inserted/updated/deleted
ij> delete from s;
9 rows inserted/updated/deleted
ij> -- bad
insert into s 
values 
	(1,1,1),
	(2,1,1),
	(10,2,1),	
	(11,1,2),
	(12,4,4),
	(4,1,1),
	(13,null,null),
	(14,1,2),
	(15,666, 1);
ERROR 23503: INSERT on table 'S' caused a violation of foreign key constraint 'xxxxGENERATED-IDxxxx' for key (666).  The statement has been rolled back.
ij> -- now a test for depenencies.
-- the insert will create new index conglomerate numbers,
-- so we want to test that a statement with a constraint
-- check that is dependent on the conglomerate number that
-- is being changed is invalidated
create table x (x int not null, y int, constraint pk primary key (x));
0 rows inserted/updated/deleted
ij> create table y (x int , y int, constraint fk foreign key (x) references x);
0 rows inserted/updated/deleted
ij> prepare ix as 
	'insert into x 
	values
		(0,0),
		(1,1),
		(2,2)';
ij> prepare ix2 as 
	'insert into x 
	values
		(3,3),
		(4,4)';
ij> prepare iy as 
	'insert into y 
	values
		(0,0),
		(1,1),
		(2,2)';
ij> prepare dy as 'delete from y where x = 1';
ij> prepare dx as 'delete from x where x = 1';
ij> execute ix;
3 rows inserted/updated/deleted
ij> autocommit off;
ij> commit;
ij> -- ok
execute dy;
0 rows inserted/updated/deleted
ij> -- ok
execute dx;
1 row inserted/updated/deleted
ij> -- will fail, no key 1 in x
execute iy;
ERROR 23503: INSERT on table 'Y' caused a violation of foreign key constraint 'FK' for key (1).  The statement has been rolled back.
ij> rollback;
ij> commit;
ij> execute iy;
3 rows inserted/updated/deleted
ij> execute dy;
1 row inserted/updated/deleted
ij> execute dx;
1 row inserted/updated/deleted
ij> remove ix;
ij> remove ix2;
ij> remove iy;
ij> remove dy;
ij> remove dx;
ij> drop table y;
0 rows inserted/updated/deleted
ij> drop table x;
0 rows inserted/updated/deleted
ij> drop table s;
0 rows inserted/updated/deleted
ij> autocommit on;
ij> CALL WAIT_FOR_POST_COMMIT();
0 rows inserted/updated/deleted
ij> -- ** insert fkddl.sql
-- simple syntax checks
-- column constraint
create table p1 (x int not null, constraint pk1 primary key(x));
0 rows inserted/updated/deleted
ij> create table u1 (x int not null unique);
0 rows inserted/updated/deleted
ij> -- table constraint
create table p2 (x int not null, y dec(5,2) not null, constraint pk2 primary key (x,y));
0 rows inserted/updated/deleted
ij> create table u2 (x int not null, y dec(5,2) not null, constraint uk2 unique (x,y));
0 rows inserted/updated/deleted
ij> create table p3 (x char(10) not null, constraint pk3 primary key (x));
0 rows inserted/updated/deleted
ij> -- for future use
create schema otherschema;
0 rows inserted/updated/deleted
ij> create table otherschema.p1 (x int not null primary key);
0 rows inserted/updated/deleted
ij> -- 
-- Negative test cases for foreign key TABLE
-- constraints
--
-- negative: fk table, no table
create table f (x int, constraint fk foreign key (x) references notthere);
ERROR X0Y46: Constraint 'FK' is invalid: referenced table NOTTHERE does not exist.
ij> -- negative: fk table, bad column
create table f (x int, constraint fk foreign key (x) references p1(notthere));
ERROR X0Y44: Constraint 'FK' is invalid: there is no unique or primary key constraint on table 'APP.P1' that matches the number and types of the columns in the foreign key.
ij> -- negative: fk table, no constraint
create table f (x int, constraint fk foreign key (x) references p2(y));
ERROR X0Y44: Constraint 'FK' is invalid: there is no unique or primary key constraint on table 'APP.P2' that matches the number and types of the columns in the foreign key.
ij> -- negative: fk table, wrong type
create table f (x smallint, constraint fk foreign key (x) references p1(x));
ERROR X0Y44: Constraint 'FK' is invalid: there is no unique or primary key constraint on table 'APP.P1' that matches the number and types of the columns in the foreign key.
ij> -- negative: cannot reference a system table
create table f (x char(36), constraint fk foreign key (x) references sys.sysforeignkeys(constraintid));
ERROR 42Y08: Foreign key constraints are not allowed on system tables.
ij> -- negative: bad schema
create table f (x char(36), constraint fk foreign key (x) references badschema.x);
ERROR 42Y07: Schema 'BADSCHEMA' does not exist
ij> -- negative: bad column list
create table f (x dec(5,2), y int, constraint fk foreign key (x,z) references p2(x,y));
ERROR 42X93: Table 'F' contains a constraint definition with column 'Z' which is not in the table.
ij> -- negative: wrong number of columns
create table f (x dec(5,2), y int, constraint fk foreign key (x) references p2(x,y));
ERROR X0Y44: Constraint 'FK' is invalid: there is no unique or primary key constraint on table 'APP.P2' that matches the number and types of the columns in the foreign key.
ij> create table f (x dec(5,2), y int, constraint fk foreign key (x,y) references p2(x));
ERROR X0Y44: Constraint 'FK' is invalid: there is no unique or primary key constraint on table 'APP.P2' that matches the number and types of the columns in the foreign key.
ij> -- 
-- Negative test cases for foreign key COLUMN
-- constraints
--
-- negative: fk column, no table
create table f (x int references notthere);
ERROR X0Y46: Constraint 'xxxxGENERATED-IDxxxx' is invalid: referenced table NOTTHERE does not exist.
ij> -- negative: fk column, bad column
create table f (x int references p1(notthere));
ERROR X0Y44: Constraint 'xxxxGENERATED-IDxxxx' is invalid: there is no unique or primary key constraint on table 'APP.P1' that matches the number and types of the columns in the foreign key.
ij> -- negative: fk column, no constraint
create table f (x int references p2(y));
ERROR X0Y44: Constraint 'xxxxGENERATED-IDxxxx' is invalid: there is no unique or primary key constraint on table 'APP.P2' that matches the number and types of the columns in the foreign key.
ij> -- negative: fk column, wrong type
create table f (x smallint references p1(x));
ERROR X0Y44: Constraint 'xxxxGENERATED-IDxxxx' is invalid: there is no unique or primary key constraint on table 'APP.P1' that matches the number and types of the columns in the foreign key.
ij> -- negative: cannot reference a system table
create table f (x char(36) references sys.sysforeignkeys(constraintid));
ERROR 42Y08: Foreign key constraints are not allowed on system tables.
ij> -- negative: bad schema
create table f (x char(36) references badschema.x);
ERROR 42Y07: Schema 'BADSCHEMA' does not exist
ij> --
-- Some type checks.  Types must match exactly
--
-- ok
create table f (d dec(5,2), i int, constraint fk foreign key (i,d) references p2(x,y));
0 rows inserted/updated/deleted
ij> drop table f;
0 rows inserted/updated/deleted
ij> CALL WAIT_FOR_POST_COMMIT();
0 rows inserted/updated/deleted
ij> create table f (i int, d dec(5,2), constraint fk foreign key (i,d) references p2(x,y));
0 rows inserted/updated/deleted
ij> drop table f;
0 rows inserted/updated/deleted
ij> CALL WAIT_FOR_POST_COMMIT();
0 rows inserted/updated/deleted
ij> create table f (d dec(5,2), i int, constraint fk foreign key (i,d) references u2(x,y));
0 rows inserted/updated/deleted
ij> drop table f;
0 rows inserted/updated/deleted
ij> CALL WAIT_FOR_POST_COMMIT();
0 rows inserted/updated/deleted
ij> create table f (i int, d dec(5,2), constraint fk foreign key (i,d) references u2(x,y));
0 rows inserted/updated/deleted
ij> drop table f;
0 rows inserted/updated/deleted
ij> CALL WAIT_FOR_POST_COMMIT();
0 rows inserted/updated/deleted
ij> create table f (c char(10) references p3(x));
0 rows inserted/updated/deleted
ij> drop table f;
0 rows inserted/updated/deleted
ij> CALL WAIT_FOR_POST_COMMIT();
0 rows inserted/updated/deleted
ij> -- type mismatch
create table f (i int, d dec(5,1), constraint fk foreign key (i,d) references p2(x,y));
ERROR X0Y44: Constraint 'FK' is invalid: there is no unique or primary key constraint on table 'APP.P2' that matches the number and types of the columns in the foreign key.
ij> create table f (i int, d dec(4,2), constraint fk foreign key (i,d) references p2(x,y));
ERROR X0Y44: Constraint 'FK' is invalid: there is no unique or primary key constraint on table 'APP.P2' that matches the number and types of the columns in the foreign key.
ij> create table f (i int, d dec(4,2), constraint fk foreign key (i,d) references p2(x,y));
ERROR X0Y44: Constraint 'FK' is invalid: there is no unique or primary key constraint on table 'APP.P2' that matches the number and types of the columns in the foreign key.
ij> create table f (i int, d numeric(5,2), constraint fk foreign key (i,d) references p2(x,y));
ERROR X0Y44: Constraint 'FK' is invalid: there is no unique or primary key constraint on table 'APP.P2' that matches the number and types of the columns in the foreign key.
ij> create table f (c char(11) references p3(x));
ERROR X0Y44: Constraint 'xxxxGENERATED-IDxxxx' is invalid: there is no unique or primary key constraint on table 'APP.P3' that matches the number and types of the columns in the foreign key.
ij> create table f (c varchar(10) references p3(x));
ERROR X0Y44: Constraint 'xxxxGENERATED-IDxxxx' is invalid: there is no unique or primary key constraint on table 'APP.P3' that matches the number and types of the columns in the foreign key.
ij> -- wrong order
create table f (d dec(5,2), i int, constraint fk foreign key (d,i) references p2(x,y));
ERROR X0Y44: Constraint 'FK' is invalid: there is no unique or primary key constraint on table 'APP.P2' that matches the number and types of the columns in the foreign key.
ij> -- check system tables 
create table f (x int, constraint fk foreign key (x) references p1);
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> 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> select distinct constraintname, referencecount 
	from sys.sysconstraints c, sys.sysforeignkeys fk
	where fk.keyconstraintid = c.constraintid order by constraintname;
CONSTRAINTNAME                                                                                                                  |REFERENCEC&
--------------------------------------------------------------------------------------------------------------------------------------------
PK1                                                                                                                             |4          
ij> select constraintname 
	from sys.sysconstraints c, sys.sysforeignkeys fk
	where fk.constraintid = c.constraintid
	order by 1;
CONSTRAINTNAME                                                                                                                  
--------------------------------------------------------------------------------------------------------------------------------
FK                                                                                                                              
FK2                                                                                                                             
FK3                                                                                                                             
FK4                                                                                                                             
ij> -- we should not be able to drop the primary key 
alter table p1 drop constraint pk1;
ERROR X0Y25: Operation 'DROP CONSTRAINT' cannot be performed on object 'PK1' because CONSTRAINT 'FK' is dependent on that object.
ERROR X0Y25: Operation 'DROP CONSTRAINT' cannot be performed on object 'PK1' because CONSTRAINT 'FK2' is dependent on that object.
ERROR X0Y25: Operation 'DROP CONSTRAINT' cannot be performed on object 'PK1' because CONSTRAINT 'FK3' is dependent on that object.
ERROR X0Y25: Operation 'DROP CONSTRAINT' cannot be performed on object 'PK1' because CONSTRAINT 'FK4' is dependent on that object.
ij> drop table p1;
ERROR X0Y25: Operation 'DROP CONSTRAINT' cannot be performed on object 'PK1' because CONSTRAINT 'FK' is dependent on that object.
ERROR X0Y25: Operation 'DROP CONSTRAINT' cannot be performed on object 'PK1' because CONSTRAINT 'FK2' is dependent on that object.
ERROR X0Y25: Operation 'DROP CONSTRAINT' cannot be performed on object 'PK1' because CONSTRAINT 'FK3' is dependent on that object.
ERROR X0Y25: Operation 'DROP CONSTRAINT' cannot be performed on object 'PK1' because CONSTRAINT 'FK4' is dependent on that object.