Logo Questions Linux Laravel Mysql Ubuntu Git Menu

Why is it necessary to explicitly specify foreign keys and references in databases?

Could you please explain me why it is necessary to specify those foreign keys when creating tables? I mean, I've created two tables which have a one-to-many relationship (on the ER-diagram) but I didn't specify the foreign keys and references. I can connect the tables using the where-clause and even perform joins and so on.

Probably, I don't get some basic concepts, although I've read some stuff about it. I guess it has something to do with data consistency or referential integrity or something.

So, could you explain me these concepts? Are those references and foreign keys are absolutely required if I have, let's say, 8-10 tables with one-to-many relationships and if I can assure that the data is inserted correctly into the database?

like image 542
Denis Yakovenko Avatar asked Mar 17 '23 07:03

Denis Yakovenko

2 Answers

It is not necessary to specify foreign key relationships. It is just a good idea.

When you specify the relationship, the database ensures relational integrity. That is, it ensures that the values in the foreign key column are legitimate values.

In addition, the cascade options on foreign keys are a big help when values are updated or deleted.

like image 84
Gordon Linoff Avatar answered Mar 18 '23 23:03

Gordon Linoff

The reason it's necessary is to ensure data integrity.
Suppose you have a table called orders, and a table called order details, both have a column called order id.
If you don't use foreign keys you might be inserting order details for an order that doesn't exists in the orders table.
Having a foreign key will make the database raise an error if you try to add order details to a non existing order.
It will also raise an error if you delete an order that already have details, unless you delete the order details first or specify cascade delete on the foreign key.

like image 26
Zohar Peled Avatar answered Mar 18 '23 23:03

Zohar Peled