SQLite How to remove parent line while saving child / children lines?

Question

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

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

 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 by keeping the child string intact . Is this even possible?

EDIT

The above example separates my question from this question . An example may help some people who only understand when there is code.

Answer 1

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;