Cascade Delete with Two Foreign Key Constraints

Databases
1

How can I do this using Xojo and an internal SQLite database?

Table A
-a_id

Table B
-b_id

Table C
-a_id_fk
-b_id_fk

I want to cascade delete a row on Table C if both a_id and b_id are deleted from their respective tables.

The row on Table C should not delete if just one of the referenced rows are deleted.

Thank you

2

In SQLite you’ll have to write a trigger to do it

http://sqlite.org/lang_createtrigger.html

The tricky part will be getting it so that the trigger only runs when you delete from table_a and table_b in either order

I’m not sure that sqlites trigger syntax is full fledged enough to do this

3

Could you set the fk values on Table C to NULL when the original row is deleted? Then you could have a trigger on Table C that removes the row when both the a and b FKs are NULL.

Completely untested, but I’m thinking of something like this:

[code]CREATE TRIGGER Delete_A
AFTER DELETE ON A
BEGIN
UPDATE C SET a_id_fk = NULL
WHERE a_id_fk = OLD.a_id
END;

CREATE TRIGGER Delete_B
AFTER DELETE ON B
BEGIN
UPDATE C SET b_id_fk = NULL
WHERE b_id_fk = OLD.b_id
END;

CREATE TRIGGER Remove_c
AFTER UPDATE ON C
BEGIN
DELETE FROM C WHERE a_id_fk IS NULL AND b_id_fk IS NULL
END;[/code]

4

I’m not sure, can it work with foreign keys and a “on delete” action
if you first enable foreign keys on the sqlite database ?

5

Thanks everyone for pointing me in the right direction. So far this seems to work.

I set up the foreign keys on Table C to cascade ON DELETE SET NULL. The I created a trigger on Table C:

CREATE TRIGGER after_update_table_c AFTER UPDATE OF a_id, b_id ON Table_C FOR EACH ROW WHEN (NEW.a_id IS NULL AND NEW.b_id IS NULL) BEGIN DELETE FROM Table_C WHERE c_id = NEW.c_id; END;