3

In a perfect world, are foreign key constraints ever really needed?

Arron S
  • 5,511
  • 7
  • 50
  • 57

7 Answers7

20

Foreign keys enforce consistency in an RDBMS. That is, no child row can ever reference a non-existent parent.

There's a school of thought that consistency rules should be enforced by application code, but this is both inefficient and error-prone. Even if your code is perfect and bug-free and never introduces a broken reference, how can you be certain that everyone else's code that accesses the same database is also perfect?

When constraints are enforced within the RDBMS, you can rely on consistency. In other words, the database never allows a change to be committed that breaks references.

When constraints are enforced by application code, you can never be quite sure that no errors have been introduced in the database. You find yourself running frequent SQL scripts to catch broken references and correct them. The extra code you have to write to do this far exceeds any performance cost of the RDBMS managing consistency.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
14

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

Dave Markle
  • 95,573
  • 20
  • 147
  • 170
  • +1 - good point. They can be very handy when using schema-aware code generation tools such as CodeSmith. – TrueWill Oct 03 '09 at 02:39
12

The world is not perfect that's why they are needed.

Otávio Décio
  • 73,752
  • 17
  • 161
  • 228
5

A world cannot be perfect without foreign keys.

3

Yes, if you want to ensure referential integrity.

Taylor Leese
  • 51,004
  • 28
  • 112
  • 141
2

In addition to consistency enforcement and documentation, they can actually speed up queries. The query optimizer can see a foreign constraint, understand its effect, and make a plan optimization that would be impossible w/o the constraint in place. See Foreign Key Constraints (Without NOCHECK) Boost Performance and Data Integrity. (SQL Server specific)

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
1

Additionally to the documentation effect Dave mentioned, FK constraints can help you to have write lesser code and automate some bits.

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

codymanix
  • 28,510
  • 21
  • 92
  • 151
  • ... but why would you want that? If you delete a customer, wouldn't you still want a record of purchases made by them for bookkeeping purposes? – Chris Sobolewski Oct 02 '09 at 23:13
  • Chris: I see what you're saying, but in that case you probably don't want to delete the customer, just set a flag in their record saying they are no longer active. – bcat Oct 02 '09 at 23:17
  • @chris : that's why it's configurable! For parent-child relationships, it might be ok, but for a purely referential relationship, obviously you wouldn't... – Dave Markle Oct 02 '09 at 23:17