CODEWITHSUNDEEP
mysqldatabase
user1854392
user1854392

Reputation: 91

How can I keep data of a foreign key when its parent is deleted?

I am making a database that has:

I want it to be the case that when the Artist is deleted from the database, the CD they have made must not be deleted automatically, but it should still be possible to delete the Artist they have made.

I am a novice MySQL coder, and I presume it's something to do with ON DELETE CASCADE/RESTRICT/SET NULL or something like that but not 100% sure, I'm pretty sure it is NOT ON DELETE CASCADE though.

Upvotes: 2

Views: 1667

Answers (2)

Martin Wilson
Martin Wilson

Reputation: 3386

If you want to keep the CD records then you'll need to set their foreign key field to the parent (Artist) to NULL, (or to a different Artist record) before you delete the Artist. In MySQL you can get the foreign keys set to NULL automatically using ON DELETE SET NULL.

You should consider whether you really want to do this- it will leave your data in a strange state. Why do you need to delete an Artist but not its corresponding CDs?

Upvotes: 2

Eitan M.
Eitan M.

Reputation: 11

You should first delete all references to the artist you want to delete, then there should be no problem deleting the artist. You can't delete an object if there are still references to this object.

Upvotes: 0

Related Questions