php - Foreign Key advantage when not using ondelete or onupdate


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?




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




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

No, you won't, unless you specifyON DELETE CASCADE orON DELETE SET NULL.

Neither you will be able to insert intoitems unless you have a matching record inproducts.

People are also looking for solutions to the problem: php - Show comments with jquery


Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.