CODEWITHSUNDEEP
sqliteforeign-keyscascading-deletes
user2660409
user2660409

Reputation: 85

SQLITE: Multiple foreign key referenced to multiple table cascade delete

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.

Upvotes: 0

Views: 2169

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521279

You may use cascading deletion here. For example, for TableC, you might use the following definition:

CREATE TABLE TableC (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    column1 TEXT,
    tb_bid INTEGER NOT NULL,
    CONSTRAINT fk_b FOREIGN KEY (tb_bid) REFERENCES TableB (id) ON DELETE CASCADE
)

The purpose of the constraint in the above table definition is that it links the tb_bid column as a foreign key pointing to the primary key id column in TableB. This means that if a parent record in TableB gets deleted, SQLite will cascade that deletion down to all child records in TableC.

You might define your TableB as follows:

CREATE TABLE TableB (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    issues TEXT,
    tb_aid INTEGER NOT NULL,
    tb_cid INTEGER NOT NULL,
    tb_did INTEGER NOT NULL,
    tb_eid INTEGER NOT NULL,
    CONSTRAINT fk_a FOREIGN KEY (tb_aid) REFERENCES TableA (id) ON DELETE CASCADE,
    CONSTRAINT fk_c FOREIGN KEY (tb_cid) REFERENCES TableC (id) ON DELETE CASCADE,
    CONSTRAINT fk_d FOREIGN KEY (tb_did) REFERENCES TableD (id) ON DELETE CASCADE,
    CONSTRAINT fk_e FOREIGN KEY (tb_eid) REFERENCES TableE (id) ON DELETE CASCADE
)

Upvotes: 2

Related Questions