CODEWITHSUNDEEP
mysqlsqlforeign-keysforeign-key-relationship
Denis Yakovenko
Denis Yakovenko

Reputation: 3535

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?

Upvotes: 4

Views: 2944

Answers (3)

Kedar Mhaswade
Kedar Mhaswade

Reputation: 4695

The driver for the foreign key constraint is the need for 'data integrity'. And the DBMS (the database server software) helps you prevent any accidental (unintentional) modification of the data when you specify the foreign key constraints. It's like you are helping the DBMS help you. Thus, if you specify the constraints, for instance, an accidental deletion of a product may be prevented when there are outstanding orders for that product.

You'll agree that when you carefully analyze the constraints and specify them in SQL at the time of creating the database (tables) then it helps ensure integrity.

This is useful when you are choosing to keep the 'knowledge of your entities' at the database level itself. This is a fine beginning approach in that your tables (relations) are more-or-less self-contained. An alternative approach to have all those consistency checks at a level higher than database. This is the approach, for instance, taken by an MVC framework like Rails where models are the layer where the constraints are applied and the tables themselves do not need to specify the foreign key and other constraints.

Which approach is better is up to your taste, usually, but you should use the building blocks in their spirit.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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.

Upvotes: 4

Zohar Peled
Zohar Peled

Reputation: 82474

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.

Upvotes: 2

Related Questions