SQLITE: Multiple foreign key referenced to multiple table cascade delete

Question

In SQLite I would like to delete parent table row and it has to delete all the related child tables. I have gone through all the questions in StackExchange and other websites but my query no where satisfied.

I have four tables.

TableA:
id, primary key
name
TableB:
id, primary key
issues,
tb_aid, #foreign key ref to TableA id
tb_cid, #foreign key ref to TableC id
tb_did, #foreign key ref to TableD id
tb_eid, #foreign key ref to TableE id
TableC:
id, primary key
column1,
tb_bid, #foreign key ref to TABLE B id
TableD:
id,
name
TableE
id,
name

I tried JOIN but unfortunately its not working in SQLite. I dont know how to use TRIGGER. Is it possible to use ON CASCADE DELETE for the above case.

Answer 1

You can use on cascade delete

Example

Turn on FK Support

PRAGMA foreign_keys = ON

Create sample data

CREATE TABLE TableA
(
id INTEGER primary key ,
name varchar
);
CREATE TABLE TableC
(
id INTEGER primary key,
name varchar
);
CREATE TABLE TableB
(
id INTEGER primary key,
issues varchar,
tb_aid INTEGER, 
tb_cid INTEGER ,
CONSTRAINT fk_TBLC
FOREIGN KEY (tb_cid)
REFERENCES TableC(id)
ON DELETE CASCADE,
CONSTRAINT fk_TBLA
FOREIGN KEY (tb_aid)
REFERENCES TableA(id)
ON DELETE CASCADE
);
INSERT INTO TableA(id,name) VALUES(1,'test');
INSERT INTO TableC(id,name) VALUES(1,'test');
INSERT INTO TableB(id,issues,tb_aid,tb_cid) VALUES(1,'test',1,1);

Delete the value from TableA

DELETE FROM TableA where id = 1;

Select from tableB

SELECT * FROM TableB;

Result

id issues tb_aid tb_cid

DB<>Fiddle

You do have bidirectional FK's in your example, while I do not think it is a good design, you could use something like the DB Fiddle below.

DB<>Fiddle

• Randi Vertongen: @user2660409 no problem, glad it helped!