CODEWITHSUNDEEP
sqlite
user1882812
user1882812

Reputation: 946

SQLite on delete cascade doesnt work

i have created some tables:

"CREATE TABLE IF NOT EXISTS WORKOUTPLANS (ID INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE , NAME TEXT NOT NULL  UNIQUE, DESCRIPTION TEXT NOT NULL  UNIQUE)";
"CREATE TABLE IF NOT EXISTS ENTITY (ID INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE , NAME TEXT NOT NULL  UNIQUE , DESCRIPTION TEXT NOT NULL  UNIQUE)";
"CREATE TABLE IF NOT EXISTS EXERCISE (ID INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE , NAME TEXT NOT NULL  UNIQUE , DESCRIPTION TEXT NOT NULL  UNIQUE , REPS TEXT NOT NULL , WEIGHT TEXT NOT NULL)";
"CREATE TABLE IF NOT EXISTS WPEN (PLANID, ENID, FOREIGN KEY(PLANID) REFERENCES WORKOUTPLANS(ID) ON DELETE CASCADE, FOREIGN KEY(ENID) REFERENCES ENTITY(ID) ON DELETE CASCADE)";
"CREATE TABLE IF NOT EXISTS ENEX (ENTITYID, EXERCISEID, FOREIGN KEY(ENTITYID) REFERENCES ENTITY(ID) ON DELETE CASCADE, FOREIGN KEY(EXERCISEID) REFERENCES EXERCISE(ID) ON DELETE CASCADE)";

So as you can see i have to m:n relationships and in this connecting tables i have constraints with on delete cascade. i know that i have to activate foreign keys in sqlite each time i connect to the database and so did i.

however, when i delete a row in WORKOUTPLANS it is just deleting the row in WORKOUTPLANS and in WPEN not in ENTITY, EXERCISE and ENEX

Why is the on delete cascade not working?

Upvotes: 1

Views: 2553

Answers (1)

ken.ganong
ken.ganong

Reputation: 1028

It is only deleting from WORKOUTPLANS and WPEN because the others don't have on delete cascade actions for WORKOUTPLANS.

Here is a summary of your schema for on delete cascade where the left is a table that has a foreign key constraint with on delete cascade for the table on the right:

WPEN -> WORKOUTPLANS
WPEN -> ENTITY
ENEX -> ENTITY
ENEX -> EXERCISE

Therefore, if you delete a row in WORKOUTPLANS, it will also delete from WPEN. If you delete from ENTITY it will also delete from WPEN and ENEX. If you delete from EXERCISE, it will also delete from ENEX.

Here is a good reference link for the on delete actions.

Here is a good stackoverflow answer for what it sounds like you want.

Upvotes: 1

Related Questions