[lgl]

>I haven't seen a Foreign Key smartly implemented in a LONG time

This has also been my experience as a web developer mostly. I rarely see any applications that make use of foreign keys constraints supplied by the database server. Usually I see relations being handled by code on the application side.

Even when I build apps that are using SQL, I always implement this stuff in code instead of FKs even though I know how they work and when designing a database schema in an app like (for example) mysql workbench, I have the option to add them easily. But I always use the "ignore foreign keys" option and then implement the constraints in code. I just find it a bit more sane to have all of the logic inside the app.

I know I'm probably "doing it wrong" but would love to hear some other opinions about this from the hn crowd that does web development. I'm guessing that more "enterprisey" apps like CRM's and ERP's will probably use more of the native database stuff.

[mpartel]

Application-level checks are prone to race conditions, especially at the default transaction isolation levels of many popular SQL databases.

For example: transaction X deletes a parent row and its child rows while a concurrent transaction Y inserts a new child node. Without a FK, this can leave an orphaned child row.

For similar reasons, SELECT before INSERT is generally not a safe replacement for a UNIQUE constraint.

You don't even need that high a user volume to see these errors in practice.

It's a shame that ORMs don't tend to encourage the use of database-level constraints, but even if I just want to build something quickly in e.g. Rails, I still add the constraints as an extra check because I'd rather get the occasional 500 than an inconsistent database.

[j8014]

For the most part it doesn't matter in a lot of "web development", most web devs education is critically lacking when it comes to databases in general.

The moment you or your customers need business intelligence and complex queries, you quickly realize how important FKs and indexes actually are. I firmly believe that you should always have FKs and indexes, referential integrity at the db level is a great thing to have and is cheap and easy to implement.

[matwood]

Even if your apps do not make direct use of FKs (through cascading deletes for example), they, along with other constraints, are smart to have in place for the protection they provide. If every order requires a customer, why not also enforce that rule in the database? The application still has to handle it properly, but the database makes sure it's handled.

Of course you're probably thinking that it's no big deal when you're writing the app and the database at the same time. I will sort of concede that point (although I think constraints, FKs, etc... should all be used unless later performance testing shows they must be removed in certain cases). But, data nearly always outlives the original application. When it's time to add to/update/rewrite the original application code is when you'll be ecstatic to have the business rules enforced in the db.

[lgl]

Thanks everybody, pretty much all great and valid points. This is all usually very much glanced over and oversimplified in most online docs and tuts in the "web development" area which is unfortunate since it's usually all mostly about keeping and maintaining said data.

I frequently get the self-awareness that there is a lot of sql functionality that I'm probably missing, misusing or completely ignorant of.

Particularly, the fk example just stood out because even though it's usually an integral part of database design i don't think enough importance is given to them "overall".

As @mpartel stated, as most orm's have similar functionality built in, their usage is just basically ignored. Also good example about the transactions stuff, I rarely see the use of foreign keys encouraged (and on your example pretty much almost required) in online tutorials and docs when discussing transactions.

More of a rant really, thanks again for the input guys! +1

[iamwil]

When I started as a web dev, I simply didn't know about them. The frameworks I was using didn't really emphasize it, so I glossed over it.

[james-mcelwain]

Besides referential integrity, foreign keys may improve the query plan (depending on vendor).

But either way, I can't imagine any reasons why you wouldn't want constraints to ensure you database is in a consistent state. There can always be bugs in you application code, manual db updates, etc.

[Scarbutt]

If you have to ask then should use them to maintain integrity, only devs who know what they are doing can have the luxury of ignoring them because of sharding, performance, complex schema migrations etc..