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 VAL
