-- original: without_rowid3.test -- credit: http://www.sqlite.org/src/tree?ci=trunk&name=test CREATE TABLE t1(a PRIMARY KEY, b) WITHOUT rowid; INSERT INTO t1 VALUES('a', 1); CREATE TABLE t2(x REFERENCES t1); INSERT INTO t2 VALUES('a') ;DELETE FROM t2; DROP TABLE t1 ;CREATE TABLE t1(x PRIMARY KEY) WITHOUT rowid; INSERT INTO t1 VALUES('x') ;INSERT INTO t2 VALUES('x') ;DROP TABLE t2; DROP TABLE t1 ;CREATE TABLE pp(x, y, PRIMARY KEY(x, y)) WITHOUT ROWID; CREATE TABLE cc(a, b, FOREIGN KEY(a, b) REFERENCES pp(x, z)) ;DROP TABLE cc ;CREATE TABLE cc(a, b, FOREIGN KEY(a, b) REFERENCES pp DEFERRABLE INITIALLY DEFERRED ) ;INSERT INTO pp VALUES('a', 'b'); INSERT INTO cc VALUES('a', 'b'); BEGIN; DROP TABLE pp; CREATE TABLE pp(a, b, c, PRIMARY KEY(b, c)) WITHOUT rowid; INSERT INTO pp VALUES(1, 'a', 'b'); COMMIT ;BEGIN; DROP TABLE cc; DROP TABLE pp; COMMIT ;CREATE TABLE b1(a, b); CREATE TABLE b2(a, b REFERENCES b1); DROP TABLE b1 ;CREATE TABLE b3(a, b REFERENCES b2 DEFERRABLE INITIALLY DEFERRED); DROP TABLE b2 ;CREATE TABLE t1(x REFERENCES v); CREATE VIEW v AS SELECT * FROM t1 ;DROP VIEW v ;CREATE VIRTUAL TABLE v USING echo(t1) ;DROP TABLE v ;CREATE TABLE pp(a PRIMARY KEY, b) WITHOUT rowid; CREATE TABLE cc(x, y REFERENCES pp DEFERRABLE INITIALLY DEFERRED); INSERT INTO pp VALUES(1, 'one'); INSERT INTO pp VALUES(2, 'two'); INSERT INTO cc VALUES('neung', 1); INSERT INTO cc VALUES('song', 2) ;BEGIN; INSERT INTO cc VALUES('see', 4); -- Violates deferred constraint ;DELETE FROM cc WHERE x = 'see' ;BEGIN; DELETE FROM pp WHERE a = 2 ;INSERT INTO self VALUES(13, 13) ;UPDATE self SET a = 14, b = 14 ;DELETE FROM self ;PRAGMA foreign_keys=ON; CREATE TABLE t1(a,b,c,d,e,f, UNIQUE (a,b), PRIMARY KEY (e,c), FOREIGN KEY (d,f) REFERENCES t1(e,c) ) WITHOUT rowid; INSERT INTO t1 VALUES(1,2,3,5,5,3); INSERT INTO t1 VALUES(2,3,4,6,6,4); INSERT INTO t1 VALUES('x','y',1.5,'fizzle','fizzle',1.5); SELECT *, '|' FROM t1 ORDER BY a, b ;UPDATE t1 SET c=99, f=99 WHERE a=1; SELECT *, '|' FROM t1 ORDER BY a, b ;UPDATE t1 SET e=876, d=876 WHERE a=2; SELECT *, '|' FROM t1 ORDER BY a, b ;DELETE FROM t1 WHERE a=1; SELECT *, '|' FROM t1 ORDER BY a, b ;DROP TABLE t1; CREATE TABLE t1(a,b,c,d,e,f, PRIMARY KEY (a,b), UNIQUE (e,c), FOREIGN KEY (d,f) REFERENCES t1(e,c) ) WITHOUT rowid; INSERT INTO t1 VALUES(1,2,3,5,5,3); INSERT INTO t1 VALUES(2,3,4,6,6,4); INSERT INTO t1 VALUES('x','y',1.5,'fizzle','fizzle',1.5); SELECT *, '|' FROM t1 ORDER BY a, b ;UPDATE t1 SET c=99, f=99 WHERE a=1; SELECT *, '|' FROM t1 ORDER BY a, b ;UPDATE t1 SET e=876, d=876 WHERE a=2; SELECT *, '|' FROM t1 ORDER BY a, b ;DELETE FROM t1 WHERE a=1; SELECT *, '|' FROM t1 ORDER BY a, b ;PRAGMA count_changes = 1 ;CREATE TABLE one(a, b, c, UNIQUE(b, c)); CREATE TABLE two(d, e, f, FOREIGN KEY(e, f) REFERENCES one(b, c)); INSERT INTO one VALUES(1, 2, 3) ;INSERT INTO one VALUES(2, 3, 4); INSERT INTO one VALUES(3, 4, 5); INSERT INTO two VALUES(1, 2, 3); INSERT INTO two VALUES(2, 3, 4); INSERT INTO two VALUES(3, 4, 5) ;SELECT * FROM one ;SELECT * FROM two ;CREATE TABLE three( g, h, i, FOREIGN KEY(h, i) REFERENCES one(b, c) DEFERRABLE INITIALLY DEFERRED ) ;CREATE TABLE high("a'b!" PRIMARY KEY, b) WITHOUT rowid; CREATE TABLE low( c, "d&6" REFERENCES high ON UPDATE CASCADE ON DELETE CASCADE ) ;INSERT INTO high VALUES('a', 'b'); INSERT INTO low VALUES('b', 'a') ;UPDATE high SET "a'b!" = 'c' ;SELECT * FROM high ; SELECT * FROM low ;DELETE FROM high ;SELECT * FROM high ; SELECT * FROM low ;PRAGMA count_changes = 0 ;CREATE TABLE long(a, b PRIMARY KEY, c) WITHOUT rowid; CREATE TABLE short(d, e, f REFERENCES long); CREATE TABLE mid(g, h, i REFERENCES long DEFERRABLE INITIALLY DEFERRED) ;INSERT INTO long VALUES(1, 2, 3) ;INSERT INTO short VALUES(1, 3, 2) ;INSERT INTO mid VALUES(1, 3, 2) ;CREATE TABLE nought(a, b PRIMARY KEY, c) WITHOUT rowid; CREATE TABLE cross(d, e, f, FOREIGN KEY(e) REFERENCES nought(b) ON UPDATE CASCADE ) ;INSERT INTO nought VALUES(2, 1, 2) ;INSERT INTO cross VALUES(0, 1, 0) ;UPDATE nought SET b = 5 ;SELECT * FROM cross ;CREATE TABLE one(a INT PRIMARY KEY, b) WITHOUT rowid; CREATE TABLE two(b, c REFERENCES one); INSERT INTO one VALUES(101, 102) ;INSERT INTO two VALUES(100, 101) ;INSERT INTO short VALUES(1, 3, NULL) ;SELECT * FROM short ;CREATE TABLE main(id INT PRIMARY KEY) WITHOUT rowid; CREATE TABLE sub(id INT REFERENCES main(id)); INSERT INTO main VALUES(1); INSERT INTO main VALUES(2); INSERT INTO sub VALUES(2) ;CREATE TABLE pp(a PRIMARY KEY, b) WITHOUT rowid; CREATE TABLE cc(c PRIMARY KEY, d REFERENCES pp) WITHOUT rowid ;SELECT * FROM cc ;BEGIN; INSERT INTO pp VALUES(2, 'two'); INSERT INTO cc VALUES(1, 2) ;COMMIT ; SELECT * FROM cc ;DELETE FROM cc ; DELETE FROM pp ;INSERT INTO pp VALUES(2, 'two'); INSERT INTO cc VALUES(1, 2) ;SELECT * FROM pp ;SELECT * FROM cc ;BEGIN; INSERT INTO pp VALUES(3, 'three') ;COMMIT ; SELECT * FROM pp ;BEGIN; INSERT INTO cc VALUES(2, 2) ;COMMIT ; SELECT * FROM cc ;DELETE FROM cc ; DELETE FROM pp ;CREATE TABLE t1(a INT PRIMARY KEY, b, c, UNIQUE(b, c)) WITHOUT rowid; CREATE TABLE t2(e REFERENCES t1, f); CREATE TABLE t3(g, h, i, FOREIGN KEY (h, i) REFERENCES t1(b, c)) ;INSERT INTO t1 VALUES(1, 2, 3); INSERT INTO t2 VALUES(1, 2) ;INSERT INTO t2 VALUES(NULL, 3) ;UPDATE t2 SET e = 1 WHERE e IS NULL ;UPDATE t2 SET e = NULL WHERE f = 3 ;UPDATE t2 SET e = NULL ;UPDATE t1 SET a = 10; DELETE FROM t1; DELETE FROM t2 ;INSERT INTO t3 VALUES(1, NULL, NULL); INSERT INTO t3 VALUES(1, 2, NULL); INSERT INTO t3 VALUES(1, NULL, 3) ;INSERT INTO t1 VALUES(1, 1, 4); INSERT INTO t3 VALUES(3, 1, 4) ;UPDATE t1 SET a = 10 ;CREATE TABLE t1(a INT PRIMARY KEY, b, c, UNIQUE(b, c)) WITHOUT rowid; CREATE TABLE t2(e REFERENCES t1 ON UPDATE CASCADE ON DELETE CASCADE, f); CREATE TABLE t3(g, h, i, FOREIGN KEY (h, i) REFERENCES t1(b, c) ON UPDATE CASCADE ON DELETE CASCADE ) ;INSERT INTO t1 VALUES(1, 2, 3); INSERT INTO t1 VALUES(4, 5, 6); INSERT INTO t2 VALUES(1, 'one'); INSERT INTO t2 VALUES(4, 'four') ;UPDATE t1 SET a = 2 WHERE a = 1; SELECT * FROM t2 ;DELETE FROM t1 WHERE a = 4; SELECT * FROM t2 ;INSERT INTO t3 VALUES('hello', 2, 3); UPDATE t1 SET c = 2; SELECT * FROM t3 ;DELETE FROM t1; SELECT * FROM t3 ;CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(c, b)) WITHOUT rowid; CREATE TABLE t2(e REFERENCES t1 ON UPDATE SET NULL ON DELETE SET NULL, f); CREATE TABLE t3(g, h, i, FOREIGN KEY (h, i) REFERENCES t1(b, c) ON UPDATE SET NULL ON DELETE SET NULL ) ;INSERT INTO t1 VALUES(1, 2, 3); INSERT INTO t1 VALUES(4, 5, 6); INSERT INTO t2 VALUES(1, 'one'); INSERT INTO t2 VALUES(4, 'four') ;UPDATE t1 SET a = 2 WHERE a = 1; SELECT * FROM t2 ;DELETE FROM t1 WHERE a = 4; SELECT * FROM t2 ;INSERT INTO t3 VALUES('hello', 2, 3); UPDATE t1 SET c = 2; SELECT * FROM t3 ;UPDATE t3 SET h = 2, i = 2; DELETE FROM t1; SELECT * FROM t3 ;PRAGMA foreign_keys=ON; CREATE TABLE tdd08(a INTEGER PRIMARY KEY, b) WITHOUT rowid; CREATE UNIQUE INDEX idd08 ON tdd08(a,b); INSERT INTO tdd08 VALUES(200,300); CREATE TABLE tdd08_b(w,x,y, FOREIGN KEY(x,y) REFERENCES tdd08(a,b)); INSERT INTO tdd08_b VALUES(100,200,300) ;SELECT * FROM tdd08 ;CREATE TABLE tce71(a INTEGER PRIMARY KEY, b) WITHOUT rowid; CREATE UNIQUE INDEX ice71 ON tce71(a,b); INSERT INTO tce71 VALUES(100,200); CREATE TABLE tce72(w, x, y, FOREIGN KEY(x,y) REFERENCES tce71(a,b)); INSERT INTO tce72 VALUES(300,100,200); UPDATE tce71 set b = 200 where a = 100; SELECT * FROM tce71, tce72 ;CREATE TABLE tce73(a INTEGER PRIMARY KEY, b, UNIQUE(a,b)) WITHOUT rowid; INSERT INTO tce73 VALUES(100,200); CREATE TABLE tce74(w, x, y, FOREIGN KEY(x,y) REFERENCES tce73(a,b)); INSERT INTO tce74 VALUES(300,100,200); UPDATE tce73 set b = 200 where a = 100; SELECT * FROM tce73, tce74 ;CREATE TABLE t1(a,b,PRIMARY KEY(a,b)) WITHOUT ROWID; CREATE TABLE t2(a,b,PRIMARY KEY(a,b)) WITHOUT ROWID; INSERT INTO t1 VALUES(1,2),(3,4),(5,6); SELECT changes() ;INSERT INTO t2 SELECT * FROM t1; SELECT changes();