php - Foreign Key advantage when not using ondelete or onupdate
607
What is the advantage of having aforeign key relationship if I am not having anydelete or update constraints set?
Suppose I have a table for items that users can post for reselling.
item_id item_product_id item_title other_fields
1 1 New TV for sale ...
1 1 Old TV for sale ...
1 2 Radio for sale ...
And the product table would be:
product_id product_name
1 TV
2 Radio
Now if aproduct is removed from theproduct table, I would not want the items to be deleted as well. The user can see that his item's product exists no longer and can select another closest appropriate product. So my question is - if this is thestructure and logic I have, is there any advantage I get by linking the items and product tables usingforeign keys?
Or, will I even be able to delete from products if there is aforeign key entry in items?
Answer
Solution:
One advantage is that your RDMS won't let you insert invalid data to begin with. If there is no product with ID 5, then you will be absolutely unable to create an item for it (whether from a script or from the mysql command line). This goes under the heading of data integrity.
Also, it might be worthwhile for you to checkout this question
Answer
Solution:
No, you won't, unless you specify
ON DELETE CASCADEorON DELETE SET NULL.Neither you will be able to insert into
itemsunless you have a matching record inproducts.