Are foreign key restrictions necessary?

Question

In an ideal world, are foreign key constraints really necessary?

Answer 1

Foreign keys provide consistency in the DBMS. That is, no child row can refer to a nonexistent parent.

There is a school of thought that consistency rules must be followed by application code, but this is inefficient and error prone. Even if your code is perfect and error free and never enters the wrong link, how can you be sure that all other codes that access the same database are also perfect?

When restrictions apply in an RDBMS, you can rely on consistency. In other words, the database never lets you make changes that break links.

When the restrictions are met by the application code, you can never be sure that there were no errors in the database. You find yourself working with frequent SQL scripts to catch broken links and fix them. The extra code that you must write to do this far exceeds any operational cost for the RDBMS management sequence.

Answer 2

In addition to protecting the integrity of your data, FK constraints also help document the relationships between your tables in the database itself.

Answer 3

The world is not perfect, why are they needed.

Answer 4

The world cannot be perfect without foreign keys.

Answer 5

Yes, if you want to provide referential integrity.

Answer 6

In addition to enforcement and documentation, they can speed up queries. The query optimizer can see the external constraint, understand its effect, and do a plan optimization that would not be possible without the constraint. See External Key Constraints (without NOCHECK) Improve data performance and integrity . (For SQL Server)

Answer 7

In addition to the documentation effect mentioned by Dave, FK restrictions can help you write less code and automate some bits.

If you, for example, delete a customer record, all of his invoices and invoice lines are also automatically deleted if you have "ON DELETE CASCADE" on their FK constrainst.