SQLite – Multiple Foreign Keys with Cascade Delete

cascadedeleteforeign keysqlite

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.

Best Answer

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

Related Question