不使用ondelete或onupdate时的外键优势

Question

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

Or, will I even be able to delete from products if there is a foreign key entry in items?

Answer 1

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