CODEWITHSUNDEEP
sqliteforeign-keys
Bugs Happen
Bugs Happen

Reputation: 2268

SQLite - How to delete parent row while keeping the child/children rows?

I understand the concepts of PRAGMA foreign_key and of ON DELETE RESTRICT/NO ACTION, but I am facing a different kind of situation.

I need to delete a parent row but keep the child row associated with it. For example:

CREATE TABLE A(id, name);
INSERT INTO A(id, name) VALUES (1, "Loreum");

CREATE TABLE B(id, id_A, name) FOREIGN KEY(id_A) REFERENCES A(id);
INSERT INTO B(id, id_A, name) VALUES (1, 1, "Opium");
DELETE FROM A WHERE id = 1;

I want to achieve this while keeping the child row intact. Is this possible at all?

EDIT

The example above separates my question from this question. Example might help some people, who only understand when there is code.

Upvotes: 1

Views: 881

Answers (1)

CL.
CL.

Reputation: 180020

You can do this with a deferred foreign key constraint:

PRAGMA foreign_keys = on;
CREATE TABLE A(id PRIMARY KEY, name);
INSERT INTO A(id, name) VALUES (1, "Loreum");
CREATE TABLE B(id, id_A, name, FOREIGN KEY(id_A) REFERENCES A(id) DEFERRABLE INITIALLY DEFERRED);
INSERT INTO B(id, id_A, name) VALUES (1, 1, "Opium");

BEGIN;
DELETE FROM A WHERE id = 1;
INSERT INTO A(id, name) VALUES (1, "another Loreum");
COMMIT;

Upvotes: 2

Related Questions