SQLite Forum

Method to delete all unreferenced rows from a parent table?
Login

Method to delete all unreferenced rows from a parent table?

(1) By anonymous on 2020-04-15 00:49:13 [source]

I am looking for a robust way to delete all unreferenced rows from a parent table.
The child tables all use ON DELETE RESTRICT on their foreign keys.

While I could manually walk all of the child tables to build a list of references, it would be duplicating logic that the foreign key system already includes, and would need to be rewritten any time a new child table is created.

I had been hoping to get the DELETE statement to just skip the affected rows, but I can't find a way to do it.  The ideal solution would effectively be DELETE OR IGNORE.

Any ideas?

(2.1) By Keith Medcalf (kmedcalf) on 2020-04-15 01:42:37 edited from 2.0 in reply to 1 [link] [source]

Everytime you delete a child try also to delete the parent and ignore the error returned (if any) from the attempt (to delete the parent).

This will work as long as at least one of the child tables does not have ON DELETE CASCADE or ON DELETE SET NULL (ie, if ALL the child tables have ON DELETE CASCADE or ON DELETE SET NULL, then all the children of that parent wherever they may be found will be deleted or set null).

Otherwise you will have to "compute" the parents to delete as in:

delete from parent where key not in (select key from child1
                                     union
                                     select key from child2
                                     ...);

Example:

create table parent (id integer primary key, value);
create table child1 (value, pid integer references parent);
create table child2 (value, pid integer references parent);
create table child3 (value, pid integer references parent on delete cascade);
insert into parent values (1, 'parent 1');
insert into child1 values ('child 1', 1);
insert into child1 values ('child 2', 1);
insert into child2 values ('child 1', 1);
insert into child2 values ('child 2', 1);
insert into child3 values ('child 1', 1);
insert into child3 values ('child 2', 1);

begin immediate;
delete from child1 where value == 'child 1';
delete from parent where id == 1;
Error: near line 14: FOREIGN KEY constraint failed
commit;

select * from parent;
-- 1|parent 1
select * from child1;
-- child 2|1
select * from child2;
-- child 1|1
-- child 2|1
select * from child3;
-- child 1|1
-- child 2|1

(3) By anonymous on 2020-04-15 03:53:30 in reply to 2.1 [link] [source]

"Otherwise you will have to "compute" the parents to delete"

That's what I was afraid of.  I was hoping to batch it all into a single DELETE FROM "parent"; and let sqlite squelch the errors, but if I must delete per-row anyway then precomputing is what I'll have to do.

Thank you for the assistance.