Search code examples
mysqlforeign-keysconstraintstable-relationships

MySQL Constraints/Cascades: Will This Work?

I have a table - threads - and another - posts.

Each thread is like a subject with the posts being replies. If a thread is deleted, all related posts need to be deleted.

All posts have an id and a thread_id field. Here's what I have, just wondering if this is correct.

ALTER TABLE `posts`
ADD CONSTRAINT `FK_threads`
ADD FOREIGN KEY(`thread_id`)
REFERENCES `threads`(`id`)
ON DELETE CASCADE
ON UPDATE CASCADE;

Does this need to be the other way around, or is it just completely wrong all together?

Solution

  • Almost correct. You don't need ADD before FOREIGN KEY

    ALTER TABLE `posts`
ADD CONSTRAINT `FK_threads`
FOREIGN KEY(`thread_id`)
REFERENCES `threads`(`id`)
ON DELETE CASCADE
ON UPDATE CASCADE;

    Here is SQLFiddle that shows DELETE CASCADE and UPDATE CASCADE in action