checkconstraint.out

derby database source code.good for you.

1 row inserted/updated/deleted
ij> -- this update should fail
update t1 set c2 = c2 + 1 where current of c2;
ERROR 23513: The check constraint 'CK2' was violated while performing an INSERT or UPDATE on table 'APP.T1'.
ij> close c2;
ij> get cursor c3 as 'select * from t1 where c1 = 2 for update of c1, c2';
ij> next c3;
C1         |C2         
-----------------------
2          |2          
ij> -- this update should succeed
update t1 set c2 = c1, c1 = c2 where current of c3;
1 row inserted/updated/deleted
ij> -- this update should fail
update t1 set c2 = c2 + 1, c1 = c1 + 3 where current of c3;
ERROR 23513: The check constraint 'CK2' was violated while performing an INSERT or UPDATE on table 'APP.T1'.
ij> -- this update should succeed
update t1 set c2 = c1 + 3, c1 = c2 + 3 where current of c3;
1 row inserted/updated/deleted
ij> select * from t1;
C1         |C2         
-----------------------
1          |1          
5          |5          
3          |3          
4          |4          
ij> close c3;
ij> rollback;
ij> -- complex expressions
create table t1(c1 int check((c1 + c1) = (c1 * c1) or 
							 (c1 + c1)/2 = (c1 * c1)), c2 int);
0 rows inserted/updated/deleted
ij> -- this insert should succeed
insert into t1 values (1, 9), (2, 10);
2 rows inserted/updated/deleted
ij> -- these updates should succeed
update t1 set c2 = c2 * c2;
2 rows inserted/updated/deleted
ij> update t1 set c1 = 2 where c1 = 1;
1 row inserted/updated/deleted
ij> update t1 set c1 = 1 where c1 = 2;
2 rows inserted/updated/deleted
ij> -- this update should fail
update t1 set c1 = c2;
ERROR 23513: The check constraint 'xxxxGENERATED-IDxxxx' was violated while performing an INSERT or UPDATE on table 'APP.T1'.
ij> select * from t1;
C1         |C2         
-----------------------
1          |81         
1          |100        
ij> rollback;
ij> -- built-in functions in a check constraint
create table charTab (c1 char(4) check(CHAR(c1) = c1));
0 rows inserted/updated/deleted
ij> insert into charTab values 'asdf';
1 row inserted/updated/deleted
ij> insert into charTab values 'fdsa';
1 row inserted/updated/deleted
ij> -- beetle 5805 - support built-in function INT
-- should fail until beetle 5805 is implemented
create table intTab (c1 int check(INT(1) = c1));
ERROR 42X01: Syntax error: Encountered "INT" at line 3, column 35.
ij> insert into intTab values 1;
ERROR 42X05: Table 'INTTAB' does not exist.
ij> -- this insert should fail, does not satisfy check constraint
insert into intTab values 2;
ERROR 42X05: Table 'INTTAB' does not exist.
ij> create table maxIntTab (c1 int check(INT(2147483647) > c1));
ERROR 42X01: Syntax error: Encountered "INT" at line 1, column 38.
ij> insert into maxIntTab values 1;
ERROR 42X05: Table 'MAXINTTAB' does not exist.
ij> -- this insert should fail, does not satisfy check constraint
insert into maxIntTab values 2147483647;
ERROR 42X05: Table 'MAXINTTAB' does not exist.
ij> rollback;
ij> -- verify that inserts, updates and statements with forced constraints are
-- indeed dependent on the constraints
create table t1(c1 int not null constraint asdf primary key);
0 rows inserted/updated/deleted
ij> insert into t1 values 1, 2, 3, 4, 5;
5 rows inserted/updated/deleted
ij> commit;
ij> prepare p1 as 'insert into t1 values 1';
ij> prepare p2 as 'update t1 set c1 = 3 where c1 = 4';
ij> prepare p3 as 'select * from t1';
ij> -- the insert and update should fail, select should succeed
execute p1;
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 'ASDF' defined on 'T1'.
ij> execute p2;
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 'ASDF' defined on 'T1'.
ij> execute p3;
C1         
-----------
1          
2          
3          
4          
5          
ij> alter table t1 drop constraint asdf;
0 rows inserted/updated/deleted
ij> -- rollback and verify that constraints are enforced and select succeeds
rollback;
ij> execute p1;
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 'ASDF' defined on 'T1'.
ij> execute p2;
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 'ASDF' defined on 'T1'.
ij> execute p3;
C1         
-----------
1          
2          
3          
4          
5          
ij> remove p1;
ij> remove p2;
ij> remove p3;
ij> drop table t1;
0 rows inserted/updated/deleted
ij> -- check constraints with parameters
create table t1(c1 int constraint asdf check(c1 = 1));
0 rows inserted/updated/deleted
ij> prepare p1 as 'insert into t1 values (?)';
ij> execute p1 using 'values (1)';
1 row inserted/updated/deleted
ij> -- clean up
drop table t1;
0 rows inserted/updated/deleted
ij> create table t1(active_flag char(2) check(active_flag IN ('Y', 'N')), araccount_active_flag char(2) check(araccount_active_flag IN ('Y', 'N')), automatic_refill_flag char(2) check(automatic_refill_flag IN ('Y', 'N')), call_when_ready_flag char(2) check(call_when_ready_flag IN ('Y', 'N')), compliance_flag char(2) check(compliance_flag IN ('Y', 'N')), delivery_flag char(2) check(delivery_flag IN ('Y', 'N')), double_count_flag char(2) check(double_count_flag IN ('Y', 'N')), gender_ind char(2) check(gender_ind IN ('M', 'F', 'U')), geriatric_flag char(2) check(geriatric_flag IN ('Y', 'N')), refuse_inquiry_flag char(2) check(refuse_inquiry_flag IN ('Y', 'N')), animal_flag char(2) check(animal_flag IN ('Y', 'N')), terminal_flag char(2) check(terminal_flag IN ('Y', 'N')), unit_flag char(2) check(unit_flag IN ('Y', 'N')), VIP_flag char(2) check(VIP_flag IN ('Y', 'N')), snap_cap_flag char(2) check(snap_cap_flag IN ('Y', 'N')), consent_on_file_flag char(2) check(consent_on_file_flag IN ('Y', 'N')), enlarged_SIG_flag char(2) check(enlarged_SIG_flag IN ('Y', 'N')),aquired_patient_flag char(2) check(aquired_patient_flag IN ('Y', 'N')));
0 rows inserted/updated/deleted
ij> -- bug 5622 - internal generated constraint names are re-worked to match db2's naming convention.
drop table t1;
0 rows inserted/updated/deleted
ij> create table t1 (c1 int not null primary key, c2 int not null unique, c3 int check (c3>=0));
0 rows inserted/updated/deleted
ij> alter table t1 add column c4 int not null default 1;
0 rows inserted/updated/deleted
ij> alter table t1 add constraint c4_unique UNIQUE(c4);
0 rows inserted/updated/deleted
ij> alter table t1 add column c5 int check(c5 >= 0);
0 rows inserted/updated/deleted
ij> select c.constraintname, c.type from sys.sysconstraints c, sys.systables t where c.tableid = t.tableid and tablename='T1';
CONSTRAINTNAME                                                                                                                  |&
----------------------------------------------------------------------------------------------------------------------------------
xxxxGENERATED-IDxxxx                                                                                                              |P
xxxxGENERATED-IDxxxx                                                                                                              |U
xxxxGENERATED-IDxxxx                                                                                                              |C
C4_UNIQUE                                                                                                                       |U
xxxxGENERATED-IDxxxx                                                                                                              |C
ij> drop table t2;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'T2' because it does not exist.
ij> create table t2 (c21 int references t1);
0 rows inserted/updated/deleted
ij> select c.constraintname, c.type from sys.sysconstraints c, sys.systables t where c.tableid = t.tableid and tablename='T2';
CONSTRAINTNAME                                                                                                                  |&
----------------------------------------------------------------------------------------------------------------------------------
xxxxGENERATED-IDxxxx                                                                                                              |F
ij> drop table t3;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'T3' because it does not exist.
ij> create table t3 (c1 int check (c1 >= 0), c2 int check (c2 >= 0), c3 int check (c3 >= 0), c4 int check (c4 >= 0), c5 int check (c5 >= 0), 
c6 int check (c6 >= 0), c7 int check (c7 >= 0), c8 int check (c8 >= 0), c9 int check (c9 >= 0), c10 int check (c10 >= 0), 
c11 int check (c11 >= 0), c12 int check (c12 >= 0), c13 int check (c13 >= 0));
0 rows inserted/updated/deleted
ij> select c.constraintname, c.type from sys.sysconstraints c, sys.systables t where c.tableid = t.tableid and tablename='T3';
CONSTRAINTNAME                                                                                                                  |&
----------------------------------------------------------------------------------------------------------------------------------
xxxxGENERATED-IDxxxx                                                                                                              |C
xxxxGENERATED-IDxxxx                                                                                                              |C
xxxxGENERATED-IDxxxx                                                                                                              |C
xxxxGENERATED-IDxxxx                                                                                                              |C
xxxxGENERATED-IDxxxx                                                                                                              |C
xxxxGENERATED-IDxxxx                                                                                                              |C
xxxxGENERATED-IDxxxx                                                                                                              |C
xxxxGENERATED-IDxxxx                                                                                                              |C
xxxxGENERATED-IDxxxx                                                                                                              |C
xxxxGENERATED-IDxxxx                                                                                                              |C
xxxxGENERATED-IDxxxx                                                                                                              |C
xxxxGENERATED-IDxxxx                                                                                                              |C
xxxxGENERATED-IDxxxx                                                                                                              |C
ij> drop table t4;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'T4' because it does not exist.
ij> create table t4(c11 int not null, c12 int not null, primary key (c11, c12));
0 rows inserted/updated/deleted
ij> select c.constraintname, c.type from sys.sysconstraints c, sys.systables t where c.tableid = t.tableid and tablename='T4';
CONSTRAINTNAME                                                                                                                  |&
----------------------------------------------------------------------------------------------------------------------------------
xxxxGENERATED-IDxxxx                                                                                                              |P
ij>