MYSQL Foreign Key ON DELETE and ON UPDATE

Question

I have a website which has a users table and a movies table. I am creating a favourites table so that users can save movies to their favourites list. The question is concerning the favourites table which I have been adviced to use the below :

CREATE TABLE Favorites (
  user_id INT NOT NULL,
  movie_id INT NOT NULL,
  PRIMARY KEY (user_id, movie_id),
  FOREIGN KEY (user_id) REFERENCES Users(user_id),
  FOREIGN KEY (movie_id) REFERENCES Movies(movie_id)
);

I noticed when reading about foreign keys there is an 'ON DELETE' and 'ON UPDATE' option where you can set to restrict, cascade etc...

If a user has favourited many movies, and then one of the movies is deleted from the movie table, what would happen if it the foreign key was set to "CASCADE"? would any rows from the favourites table be deleted also? What would happen if the foreign key was set to "RESTRICT"?

I am just after a basic explanation as I do not currently fully understand this.

wchiquito wchiquito · Accepted Answer · 2015-10-01T11:29:09

13.1.17.3 Using FOREIGN KEY Constraints

...

CASCADE: Delete or update the row from the parent table, and automatically delete or update the matching rows in the child table. Both ON DELETE CASCADE and ON UPDATE CASCADE are supported. Between two tables, do not define several ON UPDATE CASCADE clauses that act on the same column in the parent table or in the child table.

Note

Currently, cascaded foreign key actions do not activate triggers.

...

RESTRICT: Rejects the delete or update operation for the parent table. Specifying RESTRICT (or NO ACTION) is the same as omitting the ON DELETE or ON UPDATE clause.

NO ACTION: A keyword from standard SQL. In MySQL, equivalent to RESTRICT. The MySQL Server rejects the delete or update operation for the parent table if there is a related foreign key value in the referenced table. Some database systems have deferred checks, and NO ACTION is a deferred check. In MySQL, foreign key constraints are checked immediately, so NO ACTION is the same as RESTRICT.

...

MYSQL Foreign Key ON DELETE and ON UPDATE

1 Answers