Why is it necessary to explicitly list foreign keys and references in databases?
Could you please explain to me why it is necessary to specify these foreign keys when creating tables? I mean, I created two tables that have a one-to-many relationship (in the ER diagram), but I have not specified foreign keys and links. I can connect tables using where-clause and even do joins and so on.
I may not have a basic understanding, although I read something about it. I am guessing it has something to do with data integrity or referential integrity or whatever.
So, can you please explain these concepts to me? Are these references and foreign keys absolutely necessary if I have, say, 8-10 tables with one-to-many relationships, and if I can assure that the data is correctly inserted into the database?
source to share
There is no need to specify foreign key relationships. It's just a good idea.
When you specify a relationship, the database provides relational integrity. That is, it ensures that the values in the foreign key column are valid values.
Also, parameters cascade
for foreign keys are of great help when values are updated or deleted.
source to share
The reason is to ensure data integrity .
Suppose you have a table named orders
and a table named order details
, both have a column named order id
.
If you are not using foreign keys, you can insert order details for an order that does not exist in the order table.
Having a foreign key will cause the database to throw an error if you try to add order data to a non-existing order.
It will also raise an error if you delete an order that already has data, unless you delete the order data first, or specify a cascading deletion by foreign key.
source to share
The driver for the foreign key constraint is the need for "data integrity". And the DBMS (Database Server Software) helps prevent accidental (unintentional) data modification when specifying foreign key constraints. It is like you are helping the DBMS to you. Thus, if you specify restrictions, for example, accidental deletion product
can be prevented if there are outstanding ones for this product orders
.
You will agree that when you carefully parse constraints and specify them in SQL during the creation of the database (s), it helps ensure consistency.
This is useful when you decide to keep your "knowledge of your entities" at the database level. This is a great approach since your tables (relationships) are more or less autonomous. An alternative approach to check all of these consistencies above the database. This is an approach, for example, used by MVC frameworks like Rails , where models are the layer where the constraints are applied and the tables themselves do not require foreign key or other constraints to be specified.
Which approach is best for your liking, usually, but you should use the building blocks in your own spirit.
source to share