3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
6 # May you do good and not evil.
7 # May you find forgiveness for yourself and forgive others.
8 # May you share freely, never taking more than you give.
10 #***********************************************************************
11 # This file implements regression tests for SQLite library. The
12 # focus of this file is testing CHECK constraints
14 # $Id: check.test,v 1.13 2009/06/05 17:09:12 drh Exp $
16 set testdir [file dirname $argv0]
17 source $testdir/tester.tcl
18 set ::testprefix check
20 # Only run these tests if the build includes support for CHECK constraints
29 x INTEGER CHECK( x<5 ),
36 INSERT INTO t1 VALUES(3,4);
42 INSERT INTO t1 VALUES(6,7);
44 } {1 {CHECK constraint failed: t1}}
52 INSERT INTO t1 VALUES(4,3);
54 } {1 {CHECK constraint failed: t1}}
62 INSERT INTO t1 VALUES(NULL,6);
72 INSERT INTO t1 VALUES(2,NULL);
82 DELETE FROM t1 WHERE x IS NULL OR x!=3;
83 UPDATE t1 SET x=2 WHERE x==3;
89 UPDATE t1 SET x=7 WHERE x==2
91 } {1 {CHECK constraint failed: t1}}
99 UPDATE t1 SET x=5 WHERE x==2
101 } {1 {CHECK constraint failed: t1}}
109 UPDATE t1 SET x=4, y=11 WHERE x==2
121 x INTEGER CONSTRAINT one CHECK( typeof(coalesce(x,0))=="integer" ),
122 y REAL CONSTRAINT two CHECK( typeof(coalesce(y,0.1))=='real' ),
123 z TEXT CONSTRAINT three CHECK( typeof(coalesce(z,''))=='text' )
129 INSERT INTO t2 VALUES(1,2.2,'three');
137 INSERT INTO t2 VALUES(NULL, NULL, NULL);
140 } {1 2.2 three {} {} {}}
143 INSERT INTO t2 VALUES(1.1, NULL, NULL);
145 } {1 {CHECK constraint failed: one}}
148 INSERT INTO t2 VALUES(NULL, 5, NULL);
150 } {1 {CHECK constraint failed: two}}
153 INSERT INTO t2 VALUES(NULL, NULL, 3.14159);
155 } {1 {CHECK constraint failed: three}}
157 # Undocumented behavior: The CONSTRAINT name clause can follow a constraint.
158 # Such a clause is ignored. But the parser must accept it for backwards
164 x INTEGER CHECK( typeof(coalesce(x,0))=='integer' ) CONSTRAINT one,
165 y TEXT PRIMARY KEY constraint two,
167 UNIQUE(x,z) constraint three
173 INSERT INTO t2b VALUES('xyzzy','hi',5);
175 } {1 {CHECK constraint failed: t2b}}
179 x INTEGER CONSTRAINT x_one CONSTRAINT x_two
180 CHECK( typeof(coalesce(x,0))=='integer' )
181 CONSTRAINT x_two CONSTRAINT x_three,
182 y INTEGER, z INTEGER,
183 CONSTRAINT u_one UNIQUE(x,y,z) CONSTRAINT u_two
189 INSERT INTO t2c VALUES('xyzzy',7,8);
191 } {1 {CHECK constraint failed: x_two}}
192 do_test check-2.cleanup {
194 DROP TABLE IF EXISTS t2b;
195 DROP TABLE IF EXISTS t2c;
204 CHECK( x<(SELECT min(x) FROM t1) )
207 } {1 {subqueries prohibited in CHECK constraints}}
212 SELECT name FROM sqlite_master ORDER BY name
222 } {1 {no such column: q}}
225 SELECT name FROM sqlite_master ORDER BY name
235 } {1 {no such column: t2.x}}
238 SELECT name FROM sqlite_master ORDER BY name
251 INSERT INTO t3 VALUES(1,2,3);
257 INSERT INTO t3 VALUES(111,222,333);
259 } {1 {CHECK constraint failed: t3}}
263 CREATE TABLE t4(x, y,
267 OR x/y BETWEEN 5 AND 8
275 INSERT INTO t4 VALUES(1,10);
281 UPDATE t4 SET x=4, y=3;
287 UPDATE t4 SET x=12, y=2;
293 UPDATE t4 SET x=12, y=-22;
299 UPDATE t4 SET x=0, y=1;
301 } {1 {CHECK constraint failed: t4}}
309 PRAGMA ignore_check_constraints=ON;
310 UPDATE t4 SET x=0, y=1;
316 PRAGMA ignore_check_constraints=OFF;
317 UPDATE t4 SET x=0, y=2;
319 } {1 {CHECK constraint failed: t4}}
330 CREATE TABLE t5(x, y,
334 } {1 {parameters prohibited in CHECK constraints}}
337 CREATE TABLE t5(x, y,
341 } {1 {parameters prohibited in CHECK constraints}}
346 execsql {SELECT * FROM t1}
350 UPDATE OR IGNORE t1 SET x=5;
356 INSERT OR IGNORE INTO t1 VALUES(5,4.0);
362 INSERT OR IGNORE INTO t1 VALUES(2,20.0);
368 UPDATE OR FAIL t1 SET x=7-x, y=y+1;
370 } {1 {CHECK constraint failed: t1}}
379 INSERT INTO t1 VALUES(1,30.0);
380 INSERT OR ROLLBACK INTO t1 VALUES(8,40.0);
382 } {1 {CHECK constraint failed: t1}}
387 } {1 {cannot commit - no transaction is active}}
395 execsql {SELECT * FROM t1}
399 REPLACE INTO t1 VALUES(6,7);
401 } {1 {CHECK constraint failed: t1}}
403 execsql {SELECT * FROM t1}
407 INSERT OR IGNORE INTO t1 VALUES(6,7);
411 execsql {SELECT * FROM t1}
417 #--------------------------------------------------------------------------
418 # If a connection opens a database that contains a CHECK constraint that
419 # uses an unknown UDF, the schema should not be considered malformed.
420 # Attempting to modify the table should fail (since the CHECK constraint
424 proc myfunc {x} {expr $x < 10}
425 db func myfunc myfunc
427 do_execsql_test 7.1 { CREATE TABLE t6(a CHECK (myfunc(a))) }
428 do_execsql_test 7.2 { INSERT INTO t6 VALUES(9) }
429 do_catchsql_test 7.3 { INSERT INTO t6 VALUES(11) } \
430 {1 {CHECK constraint failed: t6}}
434 execsql { SELECT * FROM t6 } db2
438 catchsql { INSERT INTO t6 VALUES(8) } db2
439 } {1 {unknown function: myfunc()}}
442 catchsql { CREATE TABLE t7(a CHECK (myfunc(a))) } db2
443 } {1 {no such function: myfunc}}
446 db2 func myfunc myfunc
447 execsql { INSERT INTO t6 VALUES(8) } db2
451 db2 func myfunc myfunc
452 catchsql { INSERT INTO t6 VALUES(12) } db2
453 } {1 {CHECK constraint failed: t6}}
455 # 2013-08-02: Silently ignore database name qualifiers in CHECK constraints.
457 do_execsql_test 8.1 {
458 CREATE TABLE t810(a, CHECK( main.t810.a>0 ));
459 CREATE TABLE t811(b, CHECK( xyzzy.t811.b BETWEEN 5 AND 10 ));
462 # Make sure check constraints involving the ROWID are not ignored
464 do_execsql_test 9.1 {
466 a INTEGER PRIMARY KEY,
467 b INTEGER NOT NULL CONSTRAINT 'b-check' CHECK( b>a ),
468 c INTEGER NOT NULL CONSTRAINT 'c-check' CHECK( c>rowid*2 ),
469 d INTEGER NOT NULL CONSTRAINT 'd-check' CHECK( d BETWEEN b AND c )
471 INSERT INTO t1(a,b,c,d) VALUES(1,2,4,3),(2,4,6,5),(3,10,30,20);
473 do_catchsql_test 9.2 {
474 UPDATE t1 SET b=0 WHERE a=1;
475 } {1 {CHECK constraint failed: b-check}}
476 do_catchsql_test 9.3 {
477 UPDATE t1 SET c=a*2 WHERE a=1;
478 } {1 {CHECK constraint failed: c-check}}