Jump to content


Photo

Referential Integrity/Foreign Keys


  • Please log in to reply
1 reply to this topic

#1 jwwceo

jwwceo
  • Members
  • PipPipPip
  • Advanced Member
  • 212 posts

Posted 28 August 2006 - 07:17 PM

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???


#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 28 August 2006 - 07:27 PM

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.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users