Skip to content
Advertisement

How to cascade on delete in a foreign key of a mysql table correctly?

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.

User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement