Ask HN: Do you use foreign keys in relational databases?

I just ran into severe data corruption at a large client because a programmer four years ago wrote an empty catch block. The system would open a transaction, hit a fault, roll back, then continue writing to the database as if it’s still in the context of the transaction.

I spent some time trying to pin down exactly what it did, and found that many writes went through because of a missing foreign key constraint.

In short: if a particular table of minor importance had a foreign key constraint, there would have been no damage whatsoever, because it would have faulted immediately after the rollback.

You can’t rig up a constraint against every dumb write. But you can rig them up against some of the dumb writes. And sometimes that’s enough.

Leaving aside my initial snark reactions¹ as they are not really relevant.

What you say may be true if the data is not arranged in a manner conducive to efficient queries of the type you are trying to make (for instance if the DB was optimised for a different sort of output because the needs were (or were expected to be) different at design time. BUT, read performance is not relevant to foreign keys. A constraint is assessed as INSERT/UPDATE/other time to maintain referential integrity and has no effect on later reads. You can do your own linking in the application if you want, but I'm keeping my foreign keys to stop bad data getting in - they won't affect your process of getting data out either way.

Also note that a foreign key does not imply an index exists in most DBMSs⁴ so if you are expecting the constraint to help performance when referring to a table from its parent then you may be disappointed. An index will exist where the key is referring to as FKs will always refer to a primary key or unique index but the other side is not usually indexed unless you explicitly ask for it to be. I've seen a few people run into this trap, expecting an index to be there because an FK constraint is, and coming to the conclusion that JOINs are just slow because one isn't so their queries that would benefit from it are slow.

----

[1] Sorry, not a good enough person: “sounds like you need a better DBA!”²

[2] Well, a better database developer. Even the best can't get good performance from an inappropriate design. Or maybe a time machine, everyone who has worked with BDs long enough will have been stuck with bad or inappropriate design³ we have no power or time to fix…

[3] Possibly of our own making!

[4] Some DBs create one automatically, and some ORMs & other data manipulation libraries built on top do too. But it is generally not done because it is far from always necessary, so it could waste space, and you may want a compound index instead depending on other properties of the data ans desired outputs.

Most of the time when I had performce issues it isn't EF. It's a missed index or higher level query issue.