Mysql foreign key on delete set null

Asked 3 years, 10 months ago. Active 3 years, 10 months ago. Viewed times. Improve this question. ZaZa ZaZa 25 5 5 bronze badges. FKs are not do-all. It may be better to write your own code and wrap it in a transaction. Add a comment. Active Oldest Votes. Improve this answer. Sign up or log in Sign up using Google. Sign up using Facebook. Note that a User is not required to have a Theme selected; if they don't, they just get the application's default look.

Now, suppose that for some reason, a Theme is removed from the system, so we need to delete the Theme from the table. If any User has that Theme selected, we will get an error when trying to delete the Theme due to the foreign key constraint on the column:. In order to delete this Theme, we must first unassign it from any user that has it selected. Wait a minute! Where's Mary? Deleting a Theme deleted all Users who had that Theme selected! Surely, this is not what we want.

So, what should happen? There are really only two options we have if we'd like to be able to delete Themes but still maintain referential integrity. And there you go! This can be very handy in cases like this, and it can make things much easier to manage if used properly.

If someone decides to delete picture 1 there would not be a picture for location 0 and 1. The goal of my question is, how to remove the reference from the locations-table automatically on row deletion, to set the cell with the matching references to NULL.

Both columns must have the same data type. Show 5 more comments. Active Oldest Votes. Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password. Post as a guest Name. Email Required, but never shown. The Overflow Blog. Podcast Helping communities build their own LTE networks. Podcast Making Agile work for data science.

Featured on Meta.