Hi *, I am sorry to introduce myself to this list with such a long posting. And I am even more sorry to post such critical content! Forgive me.
I am developing an ORM-tool, which as a part of it's tasks, emulates ON DELETE actions of the underlying RDBMS. Accidentally I came across some wierdnesses in SQLite. After studying the documentation without satisfying results, I started to write a bunch of automated exploration tests. My conclusion is: - The order of execution of ON DELETE action is not well documented. - It often relies on the order of declaration in the DDL. - The DDL's impact on the order of execution may vary. - If two foreign keys come into conflict, SQLite silently ignores one of them instead of raising an error. Even with a more detailled documentation, I find the implementation of ON DELETE actions in a way "unstable" and "risky". Unstable, for it depends on the manifestation of a DDL. The order of foreign key declarations within an DDL however can easily be changed, which sometimes results in an surprising change of SQLite's behaviour. Think of tools auto-generating DDL from some user-defined metadata. Risky, for the implicit skipping of actions risks the user's data (especially, but not limited to, when ON DELETE RESTRICT is skipped). My questions are: Have I actually to try to emulate SQLite's current behaviour? If so: Is there hope for more detailled documentation? Or is there hope for another implementation? Here is the SQL-output of my tests. It contains some, outlining the problems I see. The SQLite version is 3.7.16. /* * Set 1. * * A table "track" declares two different foreign keys to a table "artist". * * In Set 1a, those foreign keys are built using the same column. * In Set 1b, each foreign key is built using a different column. * * Obviously, order of foreign key declarations often matters. * * The sometimes puzzling results could be avoided, if SQLite would prevent * the declaration of conflicting foreign keys from one table to another. */ /* * Set 1a. * ON DELETE conflicts with two foreign keys built using the same column. * * CASCADE vs. RESTRICT: RESTRICT wins * RESTRICT vs. CASCADE : CASCADE wins * RESTRICT vs. SET NULL: SET NULL wins * SET NULL vs. RESTRICT: RESTRICT wins * SET NULL vs. CASCADE : CASCADE wins * CASCADE vs. SET NULL: SET NULL wins * * Obviously, the order of declaration is decisive. The first declaration * is ignored; the second one matters. */ -- CASCADE vs. RESTRICT: RESTRICT wins PRAGMA foreign_keys = ON; CREATE TABLE artist (id PRIMARY KEY); INSERT INTO artist VALUES(1); CREATE TABLE track (artist, FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE CASCADE FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE RESTRICT); INSERT INTO track VALUES(1); DELETE FROM artist /* Error: foreign key constraint failed */; SELECT COUNT(*) AS result FROM artist ; /* Result: 1 */; SELECT COUNT(*) AS result FROM track WHERE artist = 1; /* Result: 1 */; -- RESTRICT vs. CASCADE: CASCADE wins PRAGMA foreign_keys = ON; CREATE TABLE artist (id PRIMARY KEY); INSERT INTO artist VALUES(1); CREATE TABLE track (artist, FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE RESTRICT FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE CASCADE); INSERT INTO track VALUES(1); DELETE FROM artist; SELECT COUNT(*) AS result FROM artist ; /* Result: 0 */; SELECT COUNT(*) AS result FROM track ; /* Result: 0 */; -- RESTRICT vs. SET NULL: SET NULL wins PRAGMA foreign_keys = ON; CREATE TABLE artist (id PRIMARY KEY); INSERT INTO artist VALUES(1); CREATE TABLE track (artist, FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE RESTRICT FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE SET NULL); INSERT INTO track VALUES(1); DELETE FROM artist; SELECT COUNT(*) AS result FROM artist ; /* Result: 0 */; SELECT COUNT(*) AS result FROM track WHERE artist IS NULL; /* Result: 1 */; -- SET NULL vs. RESTRICT: RESTRICT wins PRAGMA foreign_keys = ON; CREATE TABLE artist (id PRIMARY KEY); INSERT INTO artist VALUES(1); CREATE TABLE track (artist, FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE SET NULL FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE RESTRICT); INSERT INTO track VALUES(1); DELETE FROM artist /* Error: foreign key constraint failed */; SELECT COUNT(*) AS result FROM artist ; /* Result: 1 */; SELECT COUNT(*) AS result FROM track WHERE artist = 1; /* Result: 1 */; -- SET NULL vs. CASCADE: CASCADE wins PRAGMA foreign_keys = ON; CREATE TABLE artist (id PRIMARY KEY); INSERT INTO artist VALUES(1); CREATE TABLE track (artist, FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE SET NULL FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE CASCADE); INSERT INTO track VALUES(1); DELETE FROM artist; SELECT COUNT(*) AS result FROM artist ; /* Result: 0 */; SELECT COUNT(*) AS result FROM track ; /* Result: 0 */; -- CASCADE vs. SET NULL: SET NULL wins PRAGMA foreign_keys = ON; CREATE TABLE artist (id PRIMARY KEY); INSERT INTO artist VALUES(1); CREATE TABLE track (artist, FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE CASCADE FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE SET NULL); INSERT INTO track VALUES(1); DELETE FROM artist; SELECT COUNT(*) AS result FROM artist ; /* Result: 0 */; SELECT COUNT(*) AS result FROM track WHERE artist IS NULL; /* Result: 1 */; /* * Set 1b. * ON DELETE conflicts with two foreign keys built using two different columns. * * RESTRICT vs. CASCADE : CASCADE wins * CASCADE vs. RESTRICT: RESTRICT wins * RESTRICT vs. SET NULL: RESTRICT wins * SET NULL vs. RESTRICT: RESTRICT wins * SET NULL vs. CASCADE : CASCADE wins * CASCADE vs. SET NULL: CASCADE wins * * The order of declaration in this case is decisive only * if RESTRICT comes into conflict with CASCADE (see the first two tests). * SET NULL is always ignored, if there is a conflicting CASCADE or RESTRICT. * * I triangulated Set 1b with reversed columns, so that * FOREIGN KEY (artist1) ... ON DELETE x FOREIGN KEY (artist2) ... ON DELETE y * was changed to * FOREIGN KEY (artist2) ... ON DELETE x FOREIGN KEY (artist1) ... ON DELETE y * This did not have any impact on the results, so I did not put those tests * into this text. */ -- RESTRICT vs. CASCADE: CASCADE wins PRAGMA foreign_keys = ON; CREATE TABLE artist (id PRIMARY KEY); INSERT INTO artist VALUES(1); CREATE TABLE track (artist1,artist2, FOREIGN KEY (artist1) REFERENCES artist(id) ON DELETE RESTRICT FOREIGN KEY (artist2) REFERENCES artist(id) ON DELETE CASCADE); INSERT INTO track VALUES(1,1); DELETE FROM artist; SELECT COUNT(*) AS result FROM artist ; /* Result: 0 */; SELECT COUNT(*) AS result FROM track ; /* Result: 0 */; -- CASCADE vs. RESTRICT: RESTRICT wins PRAGMA foreign_keys = ON; CREATE TABLE artist (id PRIMARY KEY); INSERT INTO artist VALUES(1); CREATE TABLE track (artist1,artist2, FOREIGN KEY (artist1) REFERENCES artist(id) ON DELETE CASCADE FOREIGN KEY (artist2) REFERENCES artist(id) ON DELETE RESTRICT); INSERT INTO track VALUES(1,1); DELETE FROM artist /* Error: foreign key constraint failed */; SELECT COUNT(*) AS result FROM artist ; /* Result: 1 */; SELECT COUNT(*) AS result FROM track WHERE artist1 = 1 AND artist2 = 1; /* Result: 1 */; -- RESTRICT vs. SET NULL: RESTRICT wins PRAGMA foreign_keys = ON; CREATE TABLE artist (id PRIMARY KEY); INSERT INTO artist VALUES(1); CREATE TABLE track (artist1,artist2, FOREIGN KEY (artist1) REFERENCES artist(id) ON DELETE RESTRICT FOREIGN KEY (artist2) REFERENCES artist(id) ON DELETE SET NULL); INSERT INTO track VALUES(1,1); DELETE FROM artist /* Error: foreign key constraint failed */; SELECT COUNT(*) AS result FROM artist ; /* Result: 1 */; SELECT COUNT(*) AS result FROM track WHERE artist1 = 1 AND artist2 = 1; /* Result: 1 */; -- SET NULL vs. RESTRICT: RESTRICT wins PRAGMA foreign_keys = ON; CREATE TABLE artist (id PRIMARY KEY); INSERT INTO artist VALUES(1); CREATE TABLE track (artist1,artist2, FOREIGN KEY (artist1) REFERENCES artist(id) ON DELETE SET NULL FOREIGN KEY (artist2) REFERENCES artist(id) ON DELETE RESTRICT); INSERT INTO track VALUES(1,1); DELETE FROM artist /* Error: foreign key constraint failed */; SELECT COUNT(*) AS result FROM artist ; /* Result: 1 */; SELECT COUNT(*) AS result FROM track WHERE artist1 = 1 AND artist2 = 1; /* Result: 1 */; -- SET NULL vs. CASCADE: CASCADE wins PRAGMA foreign_keys = ON; CREATE TABLE artist (id PRIMARY KEY); INSERT INTO artist VALUES(1); CREATE TABLE track (artist1,artist2, FOREIGN KEY (artist1) REFERENCES artist(id) ON DELETE SET NULL FOREIGN KEY (artist2) REFERENCES artist(id) ON DELETE CASCADE); INSERT INTO track VALUES(1,1); DELETE FROM artist; SELECT COUNT(*) AS result FROM artist ; /* Result: 0 */; SELECT COUNT(*) AS result FROM track ; /* Result: 0 */; -- CASCADE vs. SET NULL: CASCADE wins PRAGMA foreign_keys = ON; CREATE TABLE artist (id PRIMARY KEY); INSERT INTO artist VALUES(1); CREATE TABLE track (artist1,artist2, FOREIGN KEY (artist1) REFERENCES artist(id) ON DELETE CASCADE FOREIGN KEY (artist2) REFERENCES artist(id) ON DELETE SET NULL); INSERT INTO track VALUES(1,1); DELETE FROM artist; SELECT COUNT(*) AS result FROM artist ; /* Result: 0 */; SELECT COUNT(*) AS result FROM track ; /* Result: 0 */; /* * Set 2. The triangle. * * As before, there are two tables "artist" and "track", * track declaring a foreign key to "artist". * * Now we introduce a third table "cd". * "cd" also declares a foreign key to "artist". * In Set 2a this foreign key declares ON DELETE CASCADE. * In Set 2b this foreign key declares ON DELETE SET NULL. * In Set 2c this foreign key declares ON DELETE RESTRICT. * * And we add a foreign key from "track" to "cd". * * The two foreign keys "track"->"artist" and "track"->"cd" * - are built using different columns * - and have conflicting ON DELETE actions. * * The tested question is: What happens, if there is a DELETE in "artist"? * * I have not tested SQLite's behaviour, * - if the table "cd" was created _after_ table "track" * - if the two foreign keys of table "track" were built using the same column. * Those variations could very well have an impact on the results. */ /* * Set 2a * "cd" -> "artist" with ON DELETE CASCADE * * RESTRICT vs. CASCADE : RESTRICT wins * CASCADE vs. RESTRICT: CASCADE wins * CASCADE vs. SET NULL: CASCADE wins * SET NULL vs. CASCADE : CASCADE wins * RESTRICT vs. SET NULL: RESTRICT wins * SET NULL vs. RESTRICT: RESTRICT wins * * The order of declaration in this case is (as in Set 1b) decisive only * if RESTRICT comes into conflict with CASCADE (see the first two tests). * SET NULL is always ignored, if there is a conflicting CASCADE or RESTRICT. */ -- RESTRICT vs. CASCADE: RESTRICT wins PRAGMA foreign_keys = ON; CREATE TABLE artist (id PRIMARY KEY); INSERT INTO artist VALUES(1); CREATE TABLE cd (id PRIMARY KEY,artist, FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE CASCADE); INSERT INTO cd VALUES(1,1); CREATE TABLE track (artist,cd, FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE RESTRICT FOREIGN KEY (cd) REFERENCES cd(id) ON DELETE CASCADE); INSERT INTO track VALUES(1,1); DELETE FROM artist /* Error: foreign key constraint failed */; SELECT COUNT(*) AS result FROM artist ; /* Result: 1 */; SELECT COUNT(*) AS result FROM cd ; /* Result: 1 */; SELECT COUNT(*) AS result FROM track ; /* Result: 1 */; -- CASCADE vs. RESTRICT: CASCADE wins PRAGMA foreign_keys = ON; CREATE TABLE artist (id PRIMARY KEY); INSERT INTO artist VALUES(1); CREATE TABLE cd (id PRIMARY KEY,artist, FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE CASCADE); INSERT INTO cd VALUES(1,1); CREATE TABLE track (artist,cd, FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE CASCADE FOREIGN KEY (cd) REFERENCES cd(id) ON DELETE RESTRICT); INSERT INTO track VALUES(1,1); DELETE FROM artist; SELECT COUNT(*) AS result FROM artist ; /* Result: 0 */; SELECT COUNT(*) AS result FROM cd ; /* Result: 0 */; SELECT COUNT(*) AS result FROM track ; /* Result: 0 */; -- CASCADE vs. SET NULL: CASCADE wins PRAGMA foreign_keys = ON; CREATE TABLE artist (id PRIMARY KEY); INSERT INTO artist VALUES(1); CREATE TABLE cd (id PRIMARY KEY,artist, FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE CASCADE); INSERT INTO cd VALUES(1,1); CREATE TABLE track (artist,cd, FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE CASCADE FOREIGN KEY (cd) REFERENCES cd(id) ON DELETE SET NULL); INSERT INTO track VALUES(1,1); DELETE FROM artist; SELECT COUNT(*) AS result FROM artist ; /* Result: 0 */; SELECT COUNT(*) AS result FROM cd ; /* Result: 0 */; SELECT COUNT(*) AS result FROM track ; /* Result: 0 */; -- SET NULL vs. CASCADE: CASCADE wins PRAGMA foreign_keys = ON; CREATE TABLE artist (id PRIMARY KEY); INSERT INTO artist VALUES(1); CREATE TABLE cd (id PRIMARY KEY,artist, FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE CASCADE); INSERT INTO cd VALUES(1,1); CREATE TABLE track (artist,cd, FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE SET NULL FOREIGN KEY (cd) REFERENCES cd(id) ON DELETE CASCADE); INSERT INTO track VALUES(1,1); DELETE FROM artist; SELECT COUNT(*) AS result FROM artist ; /* Result: 0 */; SELECT COUNT(*) AS result FROM cd ; /* Result: 0 */; SELECT COUNT(*) AS result FROM track ; /* Result: 0 */; -- RESTRICT vs. SET NULL: RESTRICT wins PRAGMA foreign_keys = ON; CREATE TABLE artist (id PRIMARY KEY); INSERT INTO artist VALUES(1); CREATE TABLE cd (id PRIMARY KEY,artist, FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE CASCADE); INSERT INTO cd VALUES(1,1); CREATE TABLE track (artist,cd, FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE RESTRICT FOREIGN KEY (cd) REFERENCES cd(id) ON DELETE SET NULL); INSERT INTO track VALUES(1,1); DELETE FROM artist /* Error: foreign key constraint failed */; SELECT COUNT(*) AS result FROM artist ; /* Result: 1 */; SELECT COUNT(*) AS result FROM cd ; /* Result: 1 */; SELECT COUNT(*) AS result FROM track ; /* Result: 1 */; -- SET NULL vs. RESTRICT: RESTRICT wins PRAGMA foreign_keys = ON; CREATE TABLE artist (id PRIMARY KEY); INSERT INTO artist VALUES(1); CREATE TABLE cd (id PRIMARY KEY,artist, FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE CASCADE); INSERT INTO cd VALUES(1,1); CREATE TABLE track (artist,cd, FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE SET NULL FOREIGN KEY (cd) REFERENCES cd(id) ON DELETE RESTRICT); INSERT INTO track VALUES(1,1); DELETE FROM artist /* Error: foreign key constraint failed */; SELECT COUNT(*) AS result FROM artist ; /* Result: 1 */; SELECT COUNT(*) AS result FROM cd ; /* Result: 1 */; SELECT COUNT(*) AS result FROM track ; /* Result: 1 */; /* * Set 2b * "cd" -> "artist" with ON DELETE SET NULL * * RESTRICT vs. CASCADE : RESTRICT wins * CASCADE vs. RESTRICT: CASCADE wins * CASCADE vs. SET NULL: CASCADE wins * SET NULL vs. CASCADE : SET NULL wins * RESTRICT vs. SET NULL: RESTRICT wins * SET NULL vs. RESTRICT: SET NULL wins * * In this set of tests, the relevance of order is reversed: * The first declaration wins, the second one is ignored. * * Perhaps things would change, if one would change the order of CREATE TABLE ... */ -- RESTRICT vs. CASCADE: RESTRICT wins PRAGMA foreign_keys = ON; CREATE TABLE artist (id PRIMARY KEY); INSERT INTO artist VALUES(1); CREATE TABLE cd (id PRIMARY KEY,artist, FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE SET NULL); INSERT INTO cd VALUES(1,1); CREATE TABLE track (artist,cd, FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE RESTRICT FOREIGN KEY (cd) REFERENCES cd(id) ON DELETE CASCADE); INSERT INTO track VALUES(1,1); DELETE FROM artist /* Error: foreign key constraint failed */; SELECT COUNT(*) AS result FROM artist ; /* Result: 1 */; SELECT COUNT(*) AS result FROM cd WHERE artist = 1; /* Result: 1 */; SELECT COUNT(*) AS result FROM track WHERE artist = 1 AND cd = 1; /* Result: 1 */; -- CASCADE vs. RESTRICT: CASCADE wins PRAGMA foreign_keys = ON; CREATE TABLE artist (id PRIMARY KEY); INSERT INTO artist VALUES(1); CREATE TABLE cd (id PRIMARY KEY,artist, FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE SET NULL); INSERT INTO cd VALUES(1,1); CREATE TABLE track (artist,cd, FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE CASCADE FOREIGN KEY (cd) REFERENCES cd(id) ON DELETE RESTRICT); INSERT INTO track VALUES(1,1); DELETE FROM artist; SELECT COUNT(*) AS result FROM artist ; /* Result: 0 */; SELECT COUNT(*) AS result FROM cd WHERE artist IS NULL; /* Result: 1 */; SELECT COUNT(*) AS result FROM track ; /* Result: 0 */; -- CASCADE vs. SET NULL: CASCADE wins PRAGMA foreign_keys = ON; CREATE TABLE artist (id PRIMARY KEY); INSERT INTO artist VALUES(1); CREATE TABLE cd (id PRIMARY KEY,artist, FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE SET NULL); INSERT INTO cd VALUES(1,1); CREATE TABLE track (artist,cd, FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE CASCADE FOREIGN KEY (cd) REFERENCES cd(id) ON DELETE SET NULL); INSERT INTO track VALUES(1,1); DELETE FROM artist; SELECT COUNT(*) AS result FROM artist ; /* Result: 0 */; SELECT COUNT(*) AS result FROM cd WHERE artist IS NULL; /* Result: 1 */; SELECT COUNT(*) AS result FROM track ; /* Result: 0 */; -- SET NULL vs. CASCADE: SET NULL wins PRAGMA foreign_keys = ON; CREATE TABLE artist (id PRIMARY KEY); INSERT INTO artist VALUES(1); CREATE TABLE cd (id PRIMARY KEY,artist, FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE SET NULL); INSERT INTO cd VALUES(1,1); CREATE TABLE track (artist,cd, FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE SET NULL FOREIGN KEY (cd) REFERENCES cd(id) ON DELETE CASCADE); INSERT INTO track VALUES(1,1); DELETE FROM artist; SELECT COUNT(*) AS result FROM artist ; /* Result: 0 */; SELECT COUNT(*) AS result FROM cd WHERE artist IS NULL; /* Result: 1 */; SELECT COUNT(*) AS result FROM track WHERE artist IS NULL AND cd = 1; /* Result: 1 */; -- RESTRICT vs. SET NULL: RESTRICT wins PRAGMA foreign_keys = ON; CREATE TABLE artist (id PRIMARY KEY); INSERT INTO artist VALUES(1); CREATE TABLE cd (id PRIMARY KEY,artist, FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE SET NULL); INSERT INTO cd VALUES(1,1); CREATE TABLE track (artist,cd, FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE RESTRICT FOREIGN KEY (cd) REFERENCES cd(id) ON DELETE SET NULL); INSERT INTO track VALUES(1,1); DELETE FROM artist /* Error: foreign key constraint failed */; SELECT COUNT(*) AS result FROM artist ; /* Result: 1 */; SELECT COUNT(*) AS result FROM cd WHERE artist = 1; /* Result: 1 */; SELECT COUNT(*) AS result FROM track WHERE artist = 1 AND cd = 1; /* Result: 1 */; -- SET NUL vs. RESTRICT: SET NULL wins PRAGMA foreign_keys = ON; CREATE TABLE artist (id PRIMARY KEY); INSERT INTO artist VALUES(1); CREATE TABLE cd (id PRIMARY KEY,artist, FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE SET NULL); INSERT INTO cd VALUES(1,1); CREATE TABLE track (artist,cd, FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE SET NULL FOREIGN KEY (cd) REFERENCES cd(id) ON DELETE RESTRICT); INSERT INTO track VALUES(1,1); DELETE FROM artist; SELECT COUNT(*) AS result FROM artist ; /* Result: 0 */; SELECT COUNT(*) AS result FROM cd WHERE artist IS NULL; /* Result: 1 */; SELECT COUNT(*) AS result FROM track WHERE artist IS NULL AND cd = 1; /* Result: 1 */; /* * Set 2c * "cd" -> "artist" with ON DELETE RESTRICT * * RESTRICT vs. CASCADE : RESTRICT wins * CASCADE vs. RESTRICT: RESTRICT wins * CASCADE vs. SET NULL: RESTRICT wins * SET NULL vs. CASCADE : RESTRICT wins * RESTRICT vs. SET NULL: RESTRICT wins * SET NULL vs. RESTRICT: RESTRICT wins * * In all these tests, the ON DELETE RESTRICT from "cd" to "artist" wins, * no matter how the foreign keys "track"->"artist" and "track"->"cd" * are declared. * * Perhaps things would change, if one would change the order of CREATE TABLE ... */ -- RESTRICT vs. CASCADE: RESTRICT wins PRAGMA foreign_keys = ON; CREATE TABLE artist (id PRIMARY KEY); INSERT INTO artist VALUES(1); CREATE TABLE cd (id PRIMARY KEY,artist, FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE RESTRICT); INSERT INTO cd VALUES(1,1); CREATE TABLE track (artist,cd, FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE RESTRICT FOREIGN KEY (cd) REFERENCES cd(id) ON DELETE CASCADE); INSERT INTO track VALUES(1,1); DELETE FROM artist /* Error: foreign key constraint failed */; SELECT COUNT(*) AS result FROM artist ; /* Result: 1 */; SELECT COUNT(*) AS result FROM cd WHERE artist = 1; /* Result: 1 */; SELECT COUNT(*) AS result FROM track WHERE artist = 1 AND cd = 1; /* Result: 1 */; -- CASCADE vs. RESTRICT: RESTRICT wins PRAGMA foreign_keys = ON; CREATE TABLE artist (id PRIMARY KEY); INSERT INTO artist VALUES(1); CREATE TABLE cd (id PRIMARY KEY,artist, FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE RESTRICT); INSERT INTO cd VALUES(1,1); CREATE TABLE track (artist,cd, FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE CASCADE FOREIGN KEY (cd) REFERENCES cd(id) ON DELETE RESTRICT); INSERT INTO track VALUES(1,1); DELETE FROM artist /* Error: foreign key constraint failed */; SELECT COUNT(*) AS result FROM artist ; /* Result: 1 */; SELECT COUNT(*) AS result FROM cd WHERE artist = 1; /* Result: 1 */; SELECT COUNT(*) AS result FROM track WHERE artist = 1 AND cd = 1; /* Result: 1 */; -- CASCADE vs. SET NULL: RESTRICT wins PRAGMA foreign_keys = ON; CREATE TABLE artist (id PRIMARY KEY); INSERT INTO artist VALUES(1); CREATE TABLE cd (id PRIMARY KEY,artist, FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE RESTRICT); INSERT INTO cd VALUES(1,1); CREATE TABLE track (artist,cd, FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE CASCADE FOREIGN KEY (cd) REFERENCES cd(id) ON DELETE SET NULL); INSERT INTO track VALUES(1,1); DELETE FROM artist /* Error: foreign key constraint failed */; SELECT COUNT(*) AS result FROM artist ; /* Result: 1 */; SELECT COUNT(*) AS result FROM cd WHERE artist = 1; /* Result: 1 */; SELECT COUNT(*) AS result FROM track WHERE artist = 1 AND cd = 1; /* Result: 1 */; -- SET NULL vs. CASCADE: RESTRICT wins PRAGMA foreign_keys = ON; CREATE TABLE artist (id PRIMARY KEY); INSERT INTO artist VALUES(1); CREATE TABLE cd (id PRIMARY KEY,artist, FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE RESTRICT); INSERT INTO cd VALUES(1,1); CREATE TABLE track (artist,cd, FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE SET NULL FOREIGN KEY (cd) REFERENCES cd(id) ON DELETE CASCADE); INSERT INTO track VALUES(1,1); DELETE FROM artist /* Error: foreign key constraint failed */; SELECT COUNT(*) AS result FROM artist ; /* Result: 1 */; SELECT COUNT(*) AS result FROM cd WHERE artist = 1; /* Result: 1 */; SELECT COUNT(*) AS result FROM track WHERE artist = 1 AND cd = 1; /* Result: 1 */; -- RESTRICT vs. SET NULL: RESTRICT wins PRAGMA foreign_keys = ON; CREATE TABLE artist (id PRIMARY KEY); INSERT INTO artist VALUES(1); CREATE TABLE cd (id PRIMARY KEY,artist, FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE RESTRICT); INSERT INTO cd VALUES(1,1); CREATE TABLE track (artist,cd, FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE RESTRICT FOREIGN KEY (cd) REFERENCES cd(id) ON DELETE SET NULL); INSERT INTO track VALUES(1,1); DELETE FROM artist /* Error: foreign key constraint failed */; SELECT COUNT(*) AS result FROM artist ; /* Result: 1 */; SELECT COUNT(*) AS result FROM cd WHERE artist = 1; /* Result: 1 */; SELECT COUNT(*) AS result FROM track WHERE artist = 1 AND cd = 1; /* Result: 1 */; -- SET NULL vs. RESTRICT: RESTRICT wins PRAGMA foreign_keys = ON; CREATE TABLE artist (id PRIMARY KEY); INSERT INTO artist VALUES(1); CREATE TABLE cd (id PRIMARY KEY,artist, FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE RESTRICT); INSERT INTO cd VALUES(1,1); CREATE TABLE track (artist,cd, FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE SET NULL FOREIGN KEY (cd) REFERENCES cd(id) ON DELETE RESTRICT); INSERT INTO track VALUES(1,1); DELETE FROM artist /* Error: foreign key constraint failed */; SELECT COUNT(*) AS result FROM artist ; /* Result: 1 */; SELECT COUNT(*) AS result FROM cd WHERE artist = 1; /* Result: 1 */; SELECT COUNT(*) AS result FROM track WHERE artist = 1 AND cd = 1; /* Result: 1 */; -- kind regards - Roland Wilczek Certified ScrumMaster (CSM) Nachtigallenstraße 11 53179 Bonn Tel.: 0228 / 336 70 40 9 Mobil: 0171 / 72 36 849 _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
