I have three tables with foreign keys which should cascade on delete nevertheless when I run this
"DELETE FROM Tagmap WHERE excerptID = ?"
only the mapping in Tagmap
and nothing happens in Excerpt
nor Tag
. What am I doing wrong?
CREATE TABLE IF NOT EXISTS excerpt( excerptID INT UNSIGNED NOT NULL AUTO_INCREMENT, title VARCHAR(255) NOT NULL, text VARCHAR(2500) NOT NULL, comments VARCHAR(2500) NOT NULL, PRIMARY KEY (excerptID) ) ENGINE=INNODB CHARACTER SET utf8mb4; CREATE TABLE IF NOT EXISTS tag( tagID INT UNSIGNED NOT NULL AUTO_INCREMENT, description VARCHAR(255) NOT NULL , PRIMARY KEY (tagID) ) ENGINE=INNODB CHARACTER SET utf8mb4; CREATE TABLE IF NOT EXISTS tagmap ( excerptID INT UNSIGNED NOT NULL, tagID INT UNSIGNED NOT NULL, PRIMARY KEY (excerptID, tagID), CONSTRAINT excerptFK FOREIGN KEY (excerptID) REFERENCES excerpt (excerptID) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT tagFK FOREIGN KEY (tagID) REFERENCES tag (tagID) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=INNODB;
Advertisement
Answer
This is not how foreign keys work. You delete from the parent table (here, tag
, or excerpt
), and then children record are automatically dropped. If you think about it, the other way around wouldn’t be safe, since there might be multiple children referencing the same parent.
So, you would typically run this query:
DELETE FROM excerpt WHERE excerptID = ?
And the on delete cascade
option of the foreign key constraint will automatically delete all records in tagmap
with the corresponding excerptID
.