Jump to content

Archived

This topic is now archived and is closed to further replies.

jwwceo

Referential Integrity/Foreign Keys

Recommended Posts

I have 3 tables in my databse...shirts, colors, and shirtcolor. The third table, shirtcolor, has just 2 columns; the shirt_id from shirts and color_id from colors.

Whenever I delete a shirt I also want to delete all the rows from shirtcolor where there is a row with the same shirt_id. Likewise, if I delete a color I want all references to it in shirtcolor to be deleted as well....

Do I need to set up foreign keys in my third table or can I just make the delete queries on my own manually.

if I can do them manually what is the point of the foreign keys? if I assigna foreign key, will the related rows be deleted automatically???

Share this post


Link to post
Share on other sites
The point of foreign keys is to prevent you from deleting a shirt and leaving unattached references to it in the shirtcolor table.

Therefore if you do it manually you need to delete from shirtcolor before you delete the shirt.

You can define "cascading delete" and "cascading update" for foreign keys (See MySQL manual) which automate the process if that is what you want, although it is not always desireable to set it up this way.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.