SQLite Foreign Key Support (arriving in 3.6.19) | Hacker News Reader

SQLite Foreign Key Support (arriving in 3.6.19)

sqlite.org

41 pointsjsrn10 comments

why oh why would that be needed? It seems to me that in a venn diagram of applications that need FK constraints and applications that use SQLite there should be almost no overlap.

Isn't data integrity desirable everywhere?

If you couldn't afford it in production, it might at least be useful during development.

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.

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.

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.

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.

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.

That need FK?

Nobody needs FK: Anything you can implement with FK, you can implement with tedium, code duplication and transactions.

Having FK in SQLite means less tedium, less code duplication, and/or fewer transactions.

I can't imagine why anyone would be upset about this: It's easy to remove with a single `-DSQLITE_OMIT_FOREIGN_KEY` if you need the smaller code size.

You would implement those rules in code anyway as ideally data consistency rules in the database such as foreign keys or check constraints should be a "last line of defence".

Besides architectural reasons, the practical reason behind that is that it is very hard to figure out what went wrong based on a database error you get when you violate a constraint. It is a lot easier to detect things like that in code and not allow them to propagate into the database in the first place.

Note that I'm definitely not saying that database constraints are not important, I think they are very important and I use them whenever I can, all I'm saying is database constraints in themselves are not really enough.

Reply on news.ycombinator.com