mirror of
https://github.com/tursodatabase/libsql.git
synced 2024-12-15 08:39:46 +00:00
99 lines
2.2 KiB
Plaintext
99 lines
2.2 KiB
Plaintext
PRAGMA foreign_keys = 1;
|
|
|
|
PRAGMA foreign_keys = on;
|
|
CREATE TABLE p1(a PRIMARY KEY, b) WITHOUT ROWID;
|
|
CREATE TABLE c1(x REFERENCES p1 DEFERRABLE INITIALLY DEFERRED);
|
|
|
|
INSERT INTO p1 VALUES(1, 'one');
|
|
INSERT INTO p1 VALUES(2, 'two');
|
|
INSERT INTO c1 VALUES(1);
|
|
INSERT INTO c1 VALUES(2);
|
|
|
|
|
|
PRAGMA foreign_keys = on;
|
|
CREATE TABLE p2(a PRIMARY KEY, b);
|
|
CREATE TABLE c2(
|
|
x PRIMARY KEY,
|
|
y REFERENCES p2 DEFERRABLE INITIALLY DEFERRED
|
|
) WITHOUT ROWID;
|
|
|
|
|
|
PRAGMA foreign_keys = on;
|
|
CREATE TABLE p3(a PRIMARY KEY, b) WITHOUT ROWID;
|
|
CREATE TABLE c3(x REFERENCES p3);
|
|
|
|
INSERT INTO p3 VALUES(1, 'one');
|
|
INSERT INTO p3 VALUES(2, 'two');
|
|
INSERT INTO c3 VALUES(1);
|
|
INSERT INTO c3 VALUES(2);
|
|
|
|
CREATE TRIGGER p3d AFTER DELETE ON p3 WHEN old.a=1 BEGIN
|
|
INSERT OR REPLACE INTO p3 VALUES(2, 'three');
|
|
END;
|
|
|
|
|
|
PRAGMA foreign_keys=ON;
|
|
CREATE TABLE t2(
|
|
a PRIMARY KEY, b, c, d, e,
|
|
FOREIGN KEY(b, c) REFERENCES t2(d, e)
|
|
) WITHOUT ROWID;
|
|
CREATE UNIQUE INDEX idx ON t2(d, e);
|
|
|
|
INSERT INTO t2 VALUES(1, 'one', 'one', 'one', 'one'); -- row is parent of self
|
|
INSERT INTO t2 VALUES(2, 'one', 'one', 'one', NULL); -- parent is row 1
|
|
|
|
|
|
CREATE TABLE t1 (
|
|
c1 PRIMARY KEY,
|
|
c2 NUMERIC,
|
|
FOREIGN KEY(c1) REFERENCES t1(c2)
|
|
) WITHOUT ROWID ;
|
|
CREATE INDEX t1c1 ON t1(c1);
|
|
CREATE UNIQUE INDEX t1c1unique ON t1(c2);
|
|
|
|
|
|
INSERT OR REPLACE INTO t1 VALUES(20000, 20000);
|
|
|
|
|
|
PRAGMA foreign_keys = true;
|
|
CREATE TABLE parent(
|
|
p TEXT PRIMARY KEY
|
|
);
|
|
CREATE TABLE child(
|
|
c INTEGER UNIQUE,
|
|
FOREIGN KEY(c) REFERENCES parent(p) DEFERRABLE INITIALLY DEFERRED
|
|
);
|
|
BEGIN;
|
|
INSERT INTO child VALUES(123);
|
|
INSERT INTO parent VALUES('123');
|
|
COMMIT;
|
|
|
|
|
|
PRAGMA integrity_check;
|
|
|
|
|
|
INSERT INTO parent VALUES(1200);
|
|
BEGIN;
|
|
INSERT INTO child VALUES(456);
|
|
UPDATE parent SET p = '456' WHERE p=1200;
|
|
COMMIT;
|
|
|
|
|
|
PRAGMA integrity_check;
|
|
|
|
|
|
PRAGMA foreign_keys = on;
|
|
CREATE TABLE c1(b);
|
|
INSERT INTO c1 VALUES(123);
|
|
|
|
|
|
ATTACH 'test.db2' AS aux;
|
|
CREATE TABLE aux.p1(a INTEGER PRIMARY KEY);
|
|
CREATE TABLE aux.c1(b REFERENCES p1(a) ON DELETE RESTRICT);
|
|
|
|
INSERT INTO aux.p1 VALUES(123);
|
|
|
|
|
|
DELETE FROM aux.p1 WHERE a=123;
|
|
|