Hacker News Reader

Comment by megamark16 | Hacker News Reader

Do foreign keys offer any other benefits in production other than data integrity? Are there any query performance improvements that come with defining foreign key relationships? I'm just wondering as I am currently working in a legacy system (using SQL Server 2005) that has no foreign key relationships defined (although there are relationships all over the place) and I am wondering if explicitly defining them would improve performance at all.

View on HN

I haven't personally seen any cases where foreign keys improve performance; their benefit is the fact that relationships between tables are correct. However, I have seen times where foreign keys have seriously degraded the performance of some queries. If you are doing a lot of inserts into a table with many foreign keys, all of those constraints have to be checked on every insert.

If you have foreign key constraints programs like Visio which "reverse engineer" an entity relationship diagram by querying your DB schema can do a much better job mapping out the relationships.

from msdn for SQL Server, http://msdn.microsoft.com/en-us/library/ms998577.aspx in reference to Figure 14.2. Now the query shown is a poorly written query, but the article claims that with a foreign key, the query optimizer can optimize (read: correct) the query. I can see the scenario happening with some auto generated queries...

Foreign keys in production offer safety, safety, safety. If incoming data were ALWAYS clean (3rd party data feeds) or that a DBA never had to make an ad hoc update to production to satisfy something ultra business critical, then perhaps Foreign keys are just for development, but until then, I root safety.

A common benefit of foreign keys is to allow cascading deletes (haven't read if this new SQLite FK enables this).

This is usually a performance benefit for the DB and app processes. It also helps to not_repeat_yourself by having the "business rule" in only one place.

We use triggers to do cascading deletes in sqlite3 without foreign keys. It's not as concise or declarative as a foreign key constraint, though.

Reply on news.ycombinator.com