primarykey.out

derby database source code.good for you.

ij> -- tests for primary/unique key
-- most testing currently deferred since we have extensive index tests
-- and no foreign keys yet.
-- negative tests
-- duplicate primary keys
create table neg (c1 int not null primary key, c2 int, c3 int not null constraint asdf primary key);
ERROR 42X90: More than one primary key constraint specified for table 'NEG'.
ij> create table neg (c1 int not null primary key, c2 int, c3 int not null constraint asdf primary key);
ERROR 42X90: More than one primary key constraint specified for table 'NEG'.
ij> create table neg (c1 int not null primary key, c2 int not null, primary key(c1, c2));
ERROR 42X90: More than one primary key constraint specified for table 'NEG'.
ij> -- duplicate constraint names
create table neg (c1 int not null constraint asdf primary key, c2 int, c3 int constraint asdf unique);
ERROR 42X91: Constraint name 'ASDF' appears more than once in the CREATE TABLE statement. 
ij> -- duplicate column names in same constraint column list
create table neg (c1 int not null, c2 int not null, primary key(c1, c2, c1));
ERROR 42X92: Column name 'C1' appears more than once in a constraint's column list.
ij> -- non-existant columns in constraint column list
create table neg (c1 int not null, c2 int not null, primary key(c1, c2, cx));
ERROR 42X93: Table 'NEG' contains a constraint definition with column 'CX' which is not in the table.
ij> -- invalid constraint schema name
create table neg (c1 int not null, c2 int not null, constraint bar.pkneg primary key(c1, c2));
ERROR 42X85: Constraint 'BAR.PKNEG'is required to be in the same schema as table 'NEG'.
ij> create table neg (c1 int not null, c2 int not null, constraint sys.pkneg primary key(c1, c2));
ERROR 42X85: Constraint 'SYS.PKNEG'is required to be in the same schema as table 'NEG'.
ij> create table neg (c1 int not null constraint bar.pkneg primary key, c2 int);
ERROR 42X85: Constraint 'BAR.PKNEG'is required to be in the same schema as table 'NEG'.
ij> create table neg (c1 int not null constraint sys.pkneg primary key, c2 int);
ERROR 42X85: Constraint 'SYS.PKNEG'is required to be in the same schema as table 'NEG'.
ij> -- constraint names must be unique within a schema
create table neg1(c1 int not null constraint asdf primary key);
0 rows inserted/updated/deleted
ij> create table neg2(c1 int not null constraint asdf primary key);
ERROR X0Y32: Constraint 'ASDF' already exists in Schema 'APP'.
ij> drop table neg1;
0 rows inserted/updated/deleted
ij> create table neg2(c1 int not null constraint asdf primary key);
0 rows inserted/updated/deleted
ij> drop table neg2;
0 rows inserted/updated/deleted
ij> -- again with explict schema names, should fail
create table neg1(c1 int not null constraint app.asdf primary key);
0 rows inserted/updated/deleted
ij> create table neg2(c1 int not null constraint app.asdf primary key);
ERROR X0Y32: Constraint 'ASDF' already exists in Schema 'APP'.
ij> create table neg2(c1 int not null constraint app.asdf primary key);
ERROR X0Y32: Constraint 'ASDF' already exists in Schema 'APP'.
ij> -- again with mixing schema names
create table neg1(c1 int not null constraint asdf primary key);
ERROR X0Y32: Table/View 'NEG1' already exists in Schema 'APP'.
ij> create table neg2(c1 int not null constraint app.asdf primary key);
ERROR X0Y32: Constraint 'ASDF' already exists in Schema 'APP'.
ij> drop table neg1;
0 rows inserted/updated/deleted
ij> create table neg2(c1 int not null constraint app.asdf primary key);
0 rows inserted/updated/deleted
ij> -- primary key cannot be explicitly nullable
create table neg2(c1 int null constraint asdf primary key);
ERROR 42X01: Syntax error: Encountered "null" at line 2, column 26.
ij> create table neg2(c1 int null, c2 int, constraint asdf primary key(c1, c2));
ERROR 42X01: Syntax error: Encountered "null" at line 1, column 26.
ij> -- test that a unique key can be not be explicitly nullable
create table neg1(c1 int unique);
ERROR 42831: 'C1' cannot be a column of a primary key or unique key because it can contain null values.
ij> create table neg1(c1 int, c2 int, constraint asdf unique(c1));
ERROR 42831: 'C1' cannot be a column of a primary key or unique key because it can contain null values.
ij> -- positive tests
-- verify that a primary key implies not null
create table pos1 (c1 int primary key);
0 rows inserted/updated/deleted
ij> insert into pos1(c1) values(1);
1 row inserted/updated/deleted
ij> insert into pos1(c1) values(1);
ERROR 23505: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'xxxxGENERATED-IDxxxx' defined on 'POS1'.
ij> insert into pos1(c1) values(null);
ERROR 23502: Column 'C1'  cannot accept a NULL value.
ij> drop table pos1;
0 rows inserted/updated/deleted
ij> -- verify that a unique key can not contain nulls
create table pos1 (c1 int not null unique, c2 int);
0 rows inserted/updated/deleted
ij> insert into pos1 (c1) values(null);
ERROR 23502: Column 'C1'  cannot accept a NULL value.
ij> insert into pos1 (c1) values(null);
ERROR 23502: Column 'C1'  cannot accept a NULL value.
ij> select * from pos1;
C1         |C2         
-----------------------
ij> drop table pos1;
0 rows inserted/updated/deleted
ij> -- verify that you can combine not null and unique/primary key constraints
create table pos1 (c1 int not null unique, c2 int not null primary key);
0 rows inserted/updated/deleted
ij> insert into pos1 (c1) values (null);
ERROR 23502: Column 'C1'  cannot accept a NULL value.
ij> insert into pos1 (c2) values (null);
ERROR 23502: Column 'C1'  cannot accept a NULL value.
ij> drop table pos1;
0 rows inserted/updated/deleted
ij> -- verify that you can combine multiple column constraints
select count(*) from sys.sysconstraints;
1          
-----------
1          
ij> select count(*) from sys.syskeys;
1          
-----------
1          
ij> -- we will be adding 6 rows to both sysconstraints and syskeys
create table pos1 (c1 int not null unique, c2 int not null primary key);
0 rows inserted/updated/deleted
ij> insert into pos1 (c1) values (null);
ERROR 23502: Column 'C1'  cannot accept a NULL value.
ij> insert into pos1 (c2) values (null);
ERROR 23502: Column 'C1'  cannot accept a NULL value.
ij> insert into pos1 values (1, 1), (1, 2);
ERROR 23505: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'xxxxGENERATED-IDxxxx' defined on 'POS1'.
ij> insert into pos1 values (1, 1), (2, 1);
ERROR 23505: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'xxxxGENERATED-IDxxxx' defined on 'POS1'.
ij> select count(*) from sys.sysconstraints;
1          
-----------
3          
ij> select count(*) from sys.syskeys;
1          
-----------
3          
ij> drop table pos1;
0 rows inserted/updated/deleted
ij> -- verify that you can delete from a primary key
create table pos1 (c1 int not null, c2 int not null, primary key(c2, c1));
0 rows inserted/updated/deleted
ij> insert into pos1 values (1, 2);
1 row inserted/updated/deleted