It can be annoying doing migrations where it turns out your assumptions about FKs were wrong. However, it's absolutely worse to deal with a system where relationships aren't enforced at the schema level. You still end up validating the existence of the related rows, but it happens all over your codebase in an ad hoc manner and you'll still end up with bizarre bugs.
Ask HN: Do you use foreign keys in relational databases?
241–250 of 251 posts
Re: Ask HN: Do you use foreign keys in relational databases?
#242Giving up foreign key constraints because they cause errors is basically the same mistake that the monk in http://thecodelesscode.com/case/115 made.
I didn't remember this little stories where sometimes so cruel.
Re: Ask HN: Do you use foreign keys in relational databases?
#243Most (non-gigascale) applications that are non-trivial enough to use a relational database in the first place will benefit from using foreign key constraints (ie delegating enforcement of foreign key relationships to the database). Though, context can matter: system architecture, RDBMS limitations, etc. (eg IIRC, FK constraints complicate certain kinds of migrations in MySQL at scale).
>He rather have a smooth data migration process than having an unexpected error and abort the whole operation
That's not a good reason. It doesn't sound like he's making an educated decision based on context-- it sounds like he's sacrificing data consistency to make his own work easier. If that's the case, perhaps he should do away with all integrity constraints (primary keys, checks, et al). Then, he could enjoy a "smooth process" for adding new data, too. No more pesky errors-- just blissful, silent corruption.
>To be fair, as far as I know, he never had major data problems.
Yet, anyway. He has intentionally limited his visibility over at least one kind of problem (invalid references). His problems might not be clear until they start causing noticeable issues elsewhere.
>He is not the only one I've met who insisted on not having FK. I've even seen large systems prohibit using JOIN statements.
Allergy to the relational model. Many such cases!
Re: Ask HN: Do you use foreign keys in relational databases?
#244Earlier quoted context omitted.
You made the parent commenter's point for them. You went over the heads of half the developers with join algorithms and index hints. That's just how it is, unless you're at a company with a very high bar for hiring and training.
But, besides index hints, the developers don't need to worry about those things if they use the database to perform joins. The database management system chooses for them and does it pretty well (counterproductive index hints are not unheard of). If they do it in application code, then they probably ought to learn about fancy sorting and joining algorithms. But they should really just do it in the database (using rea…
Using read replicas isn't always an option depending on your write throughput and consistency requirements.
The database can only do so well (and will spend a lot of CPU cycles working on your crazy query plan, because getting it wrong is more expensive, so now you effectively limit capacity regardless of how good your storage engine is).
Joins are great, tons of research went into making joins work, and lots of different join algorithms and optimizations based on data sizes, indexes, etc. But you really have to be careful, verses just denormalizing data across multiple tables/collections. Most applications are read-heavy, anyway... I generally plan for things to be successful, in which case joins don't usually work in the hot path.
Re: Ask HN: Do you use foreign keys in relational databases?
#245Earlier quoted context omitted.
> DB people love shoving all sorts of application logic I agree that application logic goes into the application, but data integrity is NOT application logic.
Sure it is! As a thought experiment consider evil dba whose job it is to crash your application or make it do wrong things just by manipulating the data in the DB but following the constraints. Totally trivial, right? So data integrity is at all times the responsibility of both the app and the db. And the set of constraints you can enforce with the app will always be a superset of what can be enforced by the db. And…
> So data integrity is at all times the responsibility of both the app and the db.
OK, so what you're saying is that data integrity is never the responsibility of just the app, right?
Re: Ask HN: Do you use foreign keys in relational databases?
#246I use FKs because I have built my career on refactoring old software. I have seen over and over firsthand the kinds of data integrity problems that come from leaving the decision to the business software and those who meddle asynchronously with data. You can always rewrite software. Rewriting bad data is not only difficult but often impossible.
>You can always rewrite software. Rewriting bad data is not only difficult but often impossible. Isn't that an argument against FKs? It's easier to rewrite the software to handle FKs than to deal with trying to setup FKs with bad data since it's difficult to fix.
> >You can always rewrite software. Rewriting bad data is not only difficult but often impossible.
> Isn't that an argument against FKs?
No, it's an argument for FK checks.
> It's easier to rewrite the software to handle FKs
Except once you have that bad data, you don't know what to do to fix it in whatever language your app is coded in any more than you do than in SQL. Once you know that, you can just as well do it in SQL as in any other language. Or, if it's just that you know that language better than the SQL language, by all means write a separate one-time fix-the-data app in that language, run it once to fix the data...
And then enable the FK checks so you won't have to do it again.
> than to deal with trying to setup FKs with bad data since it's difficult to fix.
Which is why you want FK checks in your DB from the beginning.
[Edit: Fix bad original editing.]
Re: Ask HN: Do you use foreign keys in relational databases?
#247Earlier quoted context omitted.
> But you could get duplicate key errors when turning fk on. Why? Fks don’t require unicity. Or do you mean that you target non-unique columns? That sounds… horrendous. But regardless sounds like something you’d want to resolve before declaring the migration finished.
You're right. I meant the troubles we get if we turn off all constraints to ease the migration, end up with duplicate keys in the target table because of mistakes, turn on constraints, errors.
> ...turn off all constraints to ease the migration, end up with duplicate keys in the target table because of mistakes, turn on constraints, errors.
[Emphasis added -- CRC.]
But that's a good thing, not a bad one -- it shows you you've made a mistake! Might even be you wouldn't have caught that mistake at all otherwise. How could it not be better to catch a mistake right as you made it and are still in the maintenance window to fix it, than to have it lingering undetected, getting ever more corrupt data, until it rears its head possibly years later, when some vital report shows your DB is full of garbage?
Re: Ask HN: Do you use foreign keys in relational databases?
#248Earlier quoted context omitted.
In the RDBMSes I'm aware of, you can disable foreign key checks while still adding the constraints themselves---they won't do anything as far as the DBMS is concerned, but can presumably still be picked up by whatever tool you are using to generate this link information.
You usually can for maybe a table, or just overall. But I don't think you can specifically for one FK. This is mostly just a small pipe dream I think :')
No, in all RDBMSes I know of, it's absolutely the other way around: Enabled or disabled (usually CHECKED / NOT CHECKED) is an attribute you set for each and every constraint, either at creation (where it usually has one or the other as default if you don't explicitly set it) or in an ALTER... statement.
Re: Ask HN: Do you use foreign keys in relational databases?
#249I always felt slightly ashamed for the fact that I don't use foreign keys. I feel better having read this thread!
> I always felt slightly ashamed for the fact that I don't use foreign keys.
Good, that's the correct reaction.
> I feel better having read this thread!
Don't.
Re: Ask HN: Do you use foreign keys in relational databases?
#250Earlier quoted context omitted.
Sure it is! As a thought experiment consider evil dba whose job it is to crash your application or make it do wrong things just by manipulating the data in the DB but following the constraints. Totally trivial, right? So data integrity is at all times the responsibility of both the app and the db. And the set of constraints you can enforce with the app will always be a superset of what can be enforced by the db. And…
> So data integrity is at all times the responsibility of both the app and the db. OK, so what you're saying is that data integrity is never the responsibility of just the app, right?
I get where you're leading but I don't think it follows. FK constraints are an optional nicety with tradeoffs rather than something fundamental -- cascade and set null are footguns (and business logic which shouldn't live in the db), and no action exists to catch bugs in your code. If your app isn't getting errors from the db saying it's trying to delete stuff with references then you could, in theory, turn off the constraints without any need to change the code. And if a relational database simply didn't have FK constraints at all, such as vitess/planetscale, you can still maintain data integrity.
What I've done in the past with prod dbs that lack FK constraints is add them back for development, testing, and CI.