In a perfect world, are foreign key constraints ever really needed?
+10
A:
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
2009-10-02 23:06:57
+1 - good point. They can be very handy when using schema-aware code generation tools such as CodeSmith.
TrueWill
2009-10-03 02:39:19
A:
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
2009-10-02 23:10:28
... 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
2009-10-02 23:13:00
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
2009-10-02 23:17:22
@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
2009-10-02 23:17:35
+12
A:
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
2009-10-02 23:18:01
+1
A:
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
2009-10-02 23:39:45