CODEWITHSUNDEEP
mysqlsql
BPS
BPS

Reputation: 1231

Why ON CASCADE DELETE is not working?

I want to delete rows from 2 tables in one query, so I do:

DELETE FROM form_questionnaire;

This is removing all rows from form_questionnaire but leaves rows in questionnaire. Why rows in questionnaire are not removed if there is ON DELETE CASCADE in form_questionnaire.questionnaire_id?

My tables

CREATE TABLE questionnaire(
    id INTEGER AUTO_INCREMENT PRIMARY KEY NOT NULL,
    content JSON,
    creator VARCHAR(50) NOT NULL,
    created TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE form_questionnaire(
    form_id INTEGER,
    questionnaire_id INTEGER,
    FOREIGN KEY(form_id) REFERENCES form(id),
    FOREIGN KEY(questionnaire_id) REFERENCES questionnaire(id) ON DELETE CASCADE
) ENGINE=INNODB;

database server

MySQL 5.7.21-20

Upvotes: 0

Views: 301

Answers (2)

Amit Joshi
Amit Joshi

Reputation: 16389

I am not sure if I am understanding this incorrect OR it is your typing mistake. Foreign key will delete all rows from CHILD table (where FOREIGN KEY is defined) if related rows from master table are deleted.

That said, if you delete rows from questionnaire table, all related rows from form_questionnaire will be deleted automatically due to CASCADE DELETE.

What you have mentioned in question is opposite and does not happen so.

Upvotes: 3

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

You're thinking about cascades the wrong way around. What your current foreign key constraint is saying is "When you delete any row(s) in questionnaire, also delete any rows in this table (form_questionnaire) which reference those rows". That's the cascade.

Upvotes: 5

Related Questions