Referential Integrity/Foreign Keys

[jwwceo]

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

[Barand]

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.