BUG: DELETE / DROP TABLE fails with self referencing FK data

(1) By Martin Rode (martinrode) on 2022-06-16 07:18:14 [source]

Consider this SQL:

PRAGMA foreign_keys = "ON";

DROP TABLE IF EXISTS "foo";

CREATE TABLE "foo" (
    id INTEGER PRIMARY KEY NOT NULL,
    id_parent INTEGER REFERENCES "foo"(id) ON DELETE RESTRICT
);

INSERT INTO "foo" ("id", "id_parent") VALUES
    (1, null),
    (2, 1);

DELETE FROM "foo" WHERE id IN (1,2);
-- Error: stepping, FOREIGN KEY constraint failed (19)

DROP TABLE IF EXISTS "foo";
-- Error: stepping, FOREIGN KEY constraint failed (19)

This SQL works fine in Postgres and (all?) other databases
It fails in Sqlite, because the DELETE works row by row it seems
For our application its essential that the DELETE work, how can I work around this problem without dropping the RESTRICT FK?
You cannot even DROP the table, which IMHO is really a problem which should be fixed!

(2) By Donald Griggs (dfgriggs) on 2022-06-16 13:30:11 in reply to 1 [link] [source]

Hi, Martin,

I'd think you'd want to enable PRAGMA defer_foreign_keys and make the procedures a single transaction. The code below runs for me without error. (I used version 3.38.5)

I would think making the series of commands atomic with a transaction is a bit cleaner to boot.

A small point -- even though "ON" does work, I use single quotes since I'm providing a value.

Even if "defer_foreign_keys" was not available, disabling/re-enabling foreign keys wouldn't affect other connections, and seems like a reasonable tactic as well.

Donald

PRAGMA foreign_keys = 'ON';

PRAGMA defer_foreign_keys = 'ON';

PRAGMA       foreign_keys;
PRAGMA defer_foreign_keys;

BEGIN;
DROP TABLE IF EXISTS "foo";

CREATE TABLE "foo" (
    id INTEGER PRIMARY KEY NOT NULL,
    id_parent INTEGER REFERENCES "foo"(id) ON DELETE RESTRICT
);

INSERT INTO "foo" ("id", "id_parent") VALUES
    (1, null),
    (2, 1);

DELETE FROM "foo" WHERE id IN (1,2);


DROP TABLE IF EXISTS "foo";
COMMIT;

(3) By Dan Kennedy (dan) on 2022-06-16 14:15:02 in reply to 1 [link] [source]

It fails in Sqlite, because the DELETE works row by row it seems

That's what "RESTRICT" means. Delete the "ON DELETE RESTRICT" from your CREATE TABLE and it should work as you expect.

https://sqlite.org/foreignkeys.html#fk_actions>

Dan.

(4) By David Raymond (dvdraymond) on 2022-06-16 15:54:57 in reply to 1 [link] [source]

The point in the docs that mention on delete restrict: https://www.sqlite.org/foreignkeys.html#fk_actions

RESTRICT: The "RESTRICT" action means that the application is prohibited from deleting (for ON DELETE RESTRICT) or modifying (for ON UPDATE RESTRICT) a parent key when there exists one or more child keys mapped to it. The difference between the effect of a RESTRICT action and normal foreign key constraint enforcement is that the RESTRICT action processing happens as soon as the field is updated - not at the end of the current statement as it would with an immediate constraint, or at the end of the current transaction as it would with a deferred constraint. Even if the foreign key constraint it is attached to is deferred, configuring a RESTRICT action causes SQLite to return an error immediately if a parent key with dependent child keys is deleted or modified.

(5) By anonymous on 2022-07-04 17:25:57 in reply to 1 [link] [source]

Having similar problem.

Reproduction.

Create a table, table 1. Create another table, table 2. Set FK's in table 2 to reference table 1.

Insert rows in table 1. DO NOT insert any in table 2.

Try to delete rows from table 1. Failure occurs in 3.36.0

No mention of bug fix in 3.39.0

(6) By Dan Kennedy (dan) on 2022-07-05 10:49:26 in reply to 5 [link] [source]

Can you post an SQL script that demonstrates this problem?

Thanks,

Dan.