CODEWITHSUNDEEP
phpmysqlsqlforeign-keysrdbms
Undefined Variable
Undefined Variable

Reputation: 4267

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

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

Upvotes: 0

Views: 55

Answers (2)

Jeff Lambert
Jeff Lambert

Reputation: 24661

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

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425663

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 specify ON DELETE CASCADE or ON DELETE SET NULL.

Neither you will be able to insert into items unless you have a matching record in products.

Upvotes: 0

Related Questions