checkconstraint.out

derby database source code.good for you.

ij> -- tests for check constraints
autocommit off;
ij> -- negative
-- The following are not allowed in check constraints:
--	?, subquery, datetime functions
create table neg1(c1 int check(?));
ERROR 42Y39: '?' may not appear in a CHECK CONSTRAINT definition because it may return non-deterministic results.
ij> create table neg1(c1 int check(c1 in (select c1 from neg1)));
ERROR 42Y39: 'subquery' may not appear in a CHECK CONSTRAINT definition because it may return non-deterministic results.
ij> create table neg1(c1 int check(CURRENT_DATE = CURRENT_DATE));
ERROR 42Y39: 'CURRENT DATE' may not appear in a CHECK CONSTRAINT definition because it may return non-deterministic results.
ij> create table neg1(c1 int check(CURRENT_TIME = CURRENT_TIME));
ERROR 42Y39: 'CURRENT TIME' may not appear in a CHECK CONSTRAINT definition because it may return non-deterministic results.
ij> create table neg1(c1 int check(CURRENT_TIMESTAMP = CURRENT_TIMESTAMP));
ERROR 42Y39: 'CURRENT TIMSTAMP' may not appear in a CHECK CONSTRAINT definition because it may return non-deterministic results.
ij> -- The check constraint definition must evaluate to a boolean
create table neg1(c1 int check(c1));
ERROR 42X19: The WHERE or HAVING clause or CHECK CONSTRAINT definition is a 'INTEGER' expression.  It must be a BOOLEAN expression.
ij> create table neg1(c1 int check(1));
ERROR 42X19: The WHERE or HAVING clause or CHECK CONSTRAINT definition is a 'INTEGER' expression.  It must be a BOOLEAN expression.
ij> create table neg1(c1 int check(c1+c1));
ERROR 42X19: The WHERE or HAVING clause or CHECK CONSTRAINT definition is a 'INTEGER' expression.  It must be a BOOLEAN expression.
ij> -- All column references are to target table
create table neg1(c1 int check((c2 = 1)));
ERROR 42X04: Column 'C2' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'C2' is not a column in the target table.
ij> -- verify that a check constraint can't be used as an optimizer override
create table t1(c1 int constraint asdf check(c1 = 1));
0 rows inserted/updated/deleted
ij> select * from t1 properties constraint = asdf;
ERROR 42X01: Syntax error: PROPERTIES.
ij> -- alter table t1 drop constraint asdf;
rollback;
ij> -- alter table t1 drop constraint asdf;
-- forward references should fail
create table neg1(c1 int check(c2 = 1), c2 int);
ERROR 42621: A check constraint or generated column that is defined with 'C1' is invalid.
ij> create table neg2(c1 int constraint asdf check(c2 = 1), c2 int);
ERROR 42621: A check constraint or generated column that is defined with 'C1' is invalid.
ij> rollback;
ij> -- positive
-- multiple check constraints on same table
create table pos1(c1 int check(c1 > 0), constraint asdf check(c1 < 10));
0 rows inserted/updated/deleted
ij> -- verify both constraints are enforced
insert into pos1 values 0;
ERROR 23513: The check constraint 'xxxxGENERATED-IDxxxx' was violated while performing an INSERT or UPDATE on table 'APP.POS1'.
ij> insert into pos1 values 1;
1 row inserted/updated/deleted
ij> insert into pos1 values 9;
1 row inserted/updated/deleted
ij> insert into pos1 values 10;
ERROR 23513: The check constraint 'ASDF' was violated while performing an INSERT or UPDATE on table 'APP.POS1'.
ij> select * from pos1;
C1         
-----------
1          
9          
ij> -- verify constraint violation rolls back entire statement
update pos1 set c1 = c1 + 1;
ERROR 23513: The check constraint 'ASDF' was violated while performing an INSERT or UPDATE on table 'APP.POS1'.
ij> select * from pos1;
C1         
-----------
1          
9          
ij> update pos1 set c1 = c1 - 1;
ERROR 23513: The check constraint 'xxxxGENERATED-IDxxxx' was violated while performing an INSERT or UPDATE on table 'APP.POS1'.
ij> select * from pos1;
C1         
-----------
1          
9          
ij> rollback;
ij> -- conflicting constraints, should fail
create table negcks(c1 int constraint ck1st check(c1 > 4), c2 int constraint ck2nd check(c2 > 2), c3 int, constraint ckLast check(c2 > c1));
0 rows inserted/updated/deleted
ij> -- constraint ck1st fails
insert into negcks values (1, 3, 3);
ERROR 23513: The check constraint 'CK1ST' was violated while performing an INSERT or UPDATE on table 'APP.NEGCKS'.
ij> -- constraint ckLast fails (ck2nd fails too)
insert into negcks values (5, 1, 3);
ERROR 23513: The check constraint 'CKLAST' was violated while performing an INSERT or UPDATE on table 'APP.NEGCKS'.
ij> -- constraint ck1st fails (ckLast fails too)
insert into negcks values (2, 3, 3);
ERROR 23513: The check constraint 'CK1ST' was violated while performing an INSERT or UPDATE on table 'APP.NEGCKS'.
ij> rollback;
ij> -- same source and target tables
create table pos1(c1 int, c2 int, constraint ck1 check (c1 < c2));
0 rows inserted/updated/deleted
ij> insert into pos1 values (1, 2), (2, 3), (3, 4);
3 rows inserted/updated/deleted
ij> commit;
ij> -- these should work
insert into pos1 select * from pos1;
3 rows inserted/updated/deleted
ij> select count(*) from pos1;
1          
-----------
6          
ij> update pos1 set c2 = (select max(c1) from pos1),
				c1 = (select min(c2) from pos1);
6 rows inserted/updated/deleted
ij> select * from pos1;
C1         |C2         
-----------------------
2          |3          
2          |3          
2          |3          
2          |3          
2          |3          
2          |3          
ij> rollback;
ij> -- these should fail
insert into pos1 select c2, c1 from pos1;
ERROR 23513: The check constraint 'CK1' was violated while performing an INSERT or UPDATE on table 'APP.POS1'.
ij> select count(*) from pos1;
1          
-----------
3          
ij> update pos1 set c2 = (select min(c1) from pos1),
				c1 = (select max(c2) from pos1);
ERROR 23513: The check constraint 'CK1' was violated while performing an INSERT or UPDATE on table 'APP.POS1'.
ij> select * from pos1;
C1         |C2         
-----------------------
1          |2          
2          |3          
3          |4          
ij> drop table pos1;
0 rows inserted/updated/deleted
ij> commit;
ij> -- union under insert
create table t1(c1 int, c2 int, constraint ck1 check(c1 = c2));
0 rows inserted/updated/deleted
ij> insert into t1 values (1, 1), (2, 1);
ERROR 23513: The check constraint 'CK1' was violated while performing an INSERT or UPDATE on table 'APP.T1'.
ij> select * from t1;
C1         |C2         
-----------------------
ij> -- normalize result set under insert/update
insert into t1 values (1.0, 1);
1 row inserted/updated/deleted
ij> insert into t1 values (2.0, 1);
ERROR 23513: The check constraint 'CK1' was violated while performing an INSERT or UPDATE on table 'APP.T1'.
ij> select * from t1;
C1         |C2         
-----------------------
1          |1          
ij> update t1 set c2 = 1.0;
1 row inserted/updated/deleted
ij> update t1 set c2 = 2.0;
ERROR 23513: The check constraint 'CK1' was violated while performing an INSERT or UPDATE on table 'APP.T1'.
ij> select * from t1;
C1         |C2         
-----------------------
1          |1          
ij> update t1 set c1 = 3.0, c2 = 3.0;
1 row inserted/updated/deleted
ij> select * from t1;
C1         |C2         
-----------------------
3          |3          
ij> rollback;
ij> -- positioned update
create table t1(c1 int, c2 int, constraint ck1 check(c1 = c2), constraint ck2 check(c2=c1));
0 rows inserted/updated/deleted
ij> insert into t1 values (1, 1), (2, 2), (3, 3), (4, 4);
4 rows inserted/updated/deleted
ij> create index i1 on t1(c1);
0 rows inserted/updated/deleted
ij> get cursor c1 as 'select * from t1 where c2 = 2 for update of c1';
ij> next c1;
C1         |C2         
-----------------------
2          |2          
ij> -- this update should succeed
update t1 set c1 = c1 where current of c1;
1 row inserted/updated/deleted
ij> -- this update should fail
update t1 set c1 = c1 + 1 where current of c1;
ERROR 23513: The check constraint 'CK2' was violated while performing an INSERT or UPDATE on table 'APP.T1'.
ij> close c1;
ij> get cursor c2 as 'select * from t1 where c1 = 2 for update of c2';
ij> next c2;
C1         |C2         
-----------------------
2          |2          
ij> -- this update should succeed
update t1 set c2 = c2 where current of c2;