php - Foreign Key advantage when not using ondelete or onupdate
What is the advantage of having a
foreign key relationship if I am not having any
delete 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 a
product is removed from the
product 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 the
structure and logic I have, is there any advantage I get by linking the items and product tables using
Or, will I even be able to delete from products if there is a
foreign key entry in items?
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
No, you won't, unless you specify
ON DELETE CASCADEor
ON DELETE SET NULL.
Neither you will be able to insert into
itemsunless you have a matching record in