Jump to content

Referential Integrity/Foreign Keys


jwwceo

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???
Link to comment
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.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.