Ask HN: Are foreign key constraints still useful? | Hacker News Reader

Ask HN: Are foreign key constraints still useful?

2 pointsozozozd7 comments

There was a time we offloaded a lot of business logic to the database server. Well, it was the only server "up" so it felt like it was ok.

So we piled on a ton of business logic on the database server. Triggers, stored procedures, you name it, we did it. And then it dawned on us that the code that lives in the database server extremely is unmaintainable - or MS decided to stop pursuing the "SQL server is where everything lives" strategy. Not sure, bit of a blur.

But, we still offload some logic to the database in the form of constraints, most popular one being the foreign key constraint.

In 15+ years of professional programming, I can't remember a time DB constraints protected a system I was working on. Almost 99% of the time, DB constraints pop up when I am making changes to my local environment, and the 1% was when there was a fire, and I had to make changes to a prod database in an urgent fashion. I never saw a log line that indicated DB constraints to be a useful protection.

And then there is the NoSQL world where obviously life goes on without these constraints and no one is trying to port foreign key constraints to NoSQL.

I feel like DB constraints are relic from a bygone era and the requirements that necessitated DB constrains are long gone.

Do you agree or am I going crazy?

You answered your own question in the description. "DB constraints pop up when I am making changes to my local environment” and that gave you the confidence to promote to QA, Staging and finally Production. A well tested application will find those problematic issues long before it goes to prod. I’m just glad that all business logic is not thrown in the DB as in the past. The separation makes for a much quicker and robust system. Don’t think DB constraints are a relic they are very much a part of the relational database management system and still used in those systems very successfully. Another topic for another thread is the relevance of the RDBMS itself not just one subset of it.

I should’ve been clearer: They pop up when I am making manual changes. I don’t ever recall being protected by constraints.

In your view systems with NoSQL databases are untested?

I think Fk constraints are still valid and a good thing to do.

Like you mentioned, you changed something and they pop up. You define that there is a necessary relationship between certain things and define it once in your schema. Your sql server takes care of enforcing it. You don’t need to remember every time about this relationship. So there is one thing less you need to take care of/which can cause issues.

I appreciate FK constraints as a form of schema documentation.

Automated tools can use 'em too.

People were having this conversation circa 2000 when MySQL didn't have FK constraints, transactions and many features other databases had.

What were the requirements that necessitated FK constraints?

Reply on news.ycombinator.com