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

Fear of RDBMSes is quite common. I used to suffer from it too. It’s just so annoying to have to switch your brain to a different programming paradigm every time you need to do something with the database that you start to make up all sorts of excuses as to why it’s really just better to “do it in the code”. Your coworkers argument about FKs making data migrations difficult is one of them.

Another classic is the “joins are slow” argument, which I believe goes back to a period in the late 1990s when in one, not highly regarded at the time, database, namely MySQL, they were indeed slow. But the reason “everyone” knew about this was precisely the oddness of this situation: in fact RDBMSes are highly optimized pieces of software that are especially good at combining sets of data. Much better than ORMs, anyway, or, god forbid, whatever you cobble together on your own.

There is, in my mind, only one valid reason to not use foreign keys in a database schema. If your database is mostly write only, the additional overhead of generating the indexes for the foreign keys may slow you down a little (for reading, these very same foreign keys in fact speed things up quite considerably). Even in such a case, however, I’d argue you’re doing it wrong and there should be a cache of some sort before things are written out in bulk to a properly setup RDBMS.

Your database is is the state of your system. Guard it!

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.

I agree with your colleague, and I insist on pushing my car everywhere because I fear gas as it is flammable.

In other words, the world is full of idiots; and any time I start forgetting about it, I read something like your post and I get a wake-up call.

What does R stand for in RDBMS is you don't use foreign keys and joins?

Please, keep using your FKs, stay safe and don't mingle too much with idiots.

I use FKs for most things in an RDBMS... but not for all things.

For example audit logs get no FKs, when a delete happens the audit logs about the deletion shouldn't be deleted.

I always FK a large table (millions or more rows) to a small table (tens to hundreds of rows).

But I will pause and ask hard questions about FK a large table to a large table... will this impact migrations? Do I need this FK? Is data integrity at risk without this FK even assuming a buggy app? Does the app utilise this FK for queries, or is there zero performance benefit from having the FK? If I don't have the FK are both tables always queryable by a PK? Should I have an index, potentially a UNIQUE index, in place of a FK?

Like most things... it depends. A dogmatic insistence on using them everywhere isn't always healthy, and the inverse is true that an avoidance everywhere isn't healthy.

The DB is there to store data and make it available, whilst enforcing data integrity... if it makes sense to use a FK to achieve those things do it, otherwise don't.

Foreign keys also let the query optimizer make better query plans. This is actually a bigger deal than most people think, the query optimizer can rewrite to semi joins or even eliminate joins completely if the optimizer has better guarantees about referential integrity.

There’s a pithy quote by someone famous in DB circles who said something like “normalise until it hurts, de-normalise until it’s fast enough” - I’m vague on the exact words used but that’s the gist of it.

I’ve never come across a scenario yet where this wasn’t sound advice.

I tend to lean heavily on my DB as well. E.g. I tend to push all state down to the DB and out of the application. I work in environments where it’s common for developers to want to disable FK constraints, and i temporarily do sometimes during specific bulk operations during releases for example. The usual reasons others will suggest relaxing FK constraints permanently will be due to the need for audit logs or soft deletes but i have patterns for these too.

Foreign key constraints are pretty awesome, all databases I’ve worked in so far have escape hatches for when they hurt too much.

Giving up foreign key constraints because they cause errors is basically the same mistake that the monk in http://thecodelesscode.com/case/115 made.

Foreign keys can be removed during migrations and added back after. You would also disable triggers and check constraints too.

I like foreign keys, check constraints and tight data types. Might as well constrain it and limit the scope for errors.

Application programmers can write some buggy code. The DB should provide a line of defence.

If you don’t have foreign keys that should be a design choice with a legitimate reason the entity can become orphaned.

You need a definition of what that means in real life. E.g PERSONID id 1012 and there is no associated record. This kinda means you need to look at another entity to know what the first entity “means”.

This might be useful for data that needs to sit in distributed databases.

To me "let's wholesale throw away this non-deprecated language feature" is a major red flag.

I've found that proponents of this usually either don't fully understand the feature in question or made some major mistake in the implementation, which in turn causes problems that manifest themselves when they try to use said feature.

You need FKs to ensure you don't delete data that's still needed. If your data is important, you have to use FKs.

You also need to index your FKs so that the database does not have to do a full table search before you can delete a row. This is often overlooked.

Your friend can do his data migrations without FKs and create them afterwards. This is quite a common procedure.

I'm going to assume that by "foreign keys", you mean "foreign key constraints" where the DB itself is insisting on particular relationships.

There are a few different schools of thought. I will list them, but the important thing to remember is not to be dogmatic. They are all right or wrong depending on your circumstance.

One school of thought says "I want all data in my DB to be normalized. I want it to be right when it goes in so it never breaks the application layer." That school would say foreign key constraints are critical.

Another school of thought says "I want all the data in my DB to be retrieved and inserted quickly. I want the application layer to do any error-checking that is necessary, or, I want to be in a situation where I can always fail gracefully if there's errors in data validation."

Still another school of thought says "I don't trust those programmers to write good application code, so I will insist on normalized data for that reason," and yet another says, "I don't have control over the DB schema, that's some DBA's job, so I will just do all my validation in-app."

The point of this being, there's tradeoffs either way you go.

Personally, I typically would rather have the application layer do the validation and even the joining of data, a lot of the time, when the application is high-volume. At the volumes my organization works with, it is very difficult to write performant SQL queries that use JOINs and other relationships as a developer - even as a DBA! - and often much easier, for me, to write performant application code. The DB is also a pet with many owners, whereas the infra for my applications is owned by my team. So, it's better for me to do relationship validation in code myself. (We also do not use a heavy ORM, again for performance reasons. Just Dapper.)

At my previous job, the situation was the opposite - we weren't under such load at most times that it mattered if the queries were performant, we had Entity Framework building relationship, and EF will blow up if you ask it to build relationships where none exist. So, we needed more normalized data, and that was what we went for. But even then, not in every situation.

Foreign key constraints are easy to remove if they become a problem but almost impossible to add once data integrity problems arise (and I've never seen them not arise in projects without FK constraints).

The small number of people with high enough scale that they can't use them know who they are, the rest of us need to think carefully when performing database migrations and reason out the order of operations required to maintain data integrity (sounds like a good idea anyway?).

There are many reasons to not use foreign keys, but it also depends on the kind of application. For small databases, foreign keys do make things simpler from a validation standpoint.

When building systems for scale where the databases may grow large, foreign keys can cause many issues -

- ORM features around foreign keys can easily bring your system down when joining large tables with incorrect/missing indexes during heavy loads

- As the table grows, not having foreign keys makes it simple in taking out large tables into big-data solutions in the future

- The schemas and relations are sometimes hard to understand during the initial phases of application development. Not having those relations makes changing schemas simpler and faster.

- Sharding tables is much simpler when there are no foreign keys

- It helps to add some of the reference logic in the application rather than the database. Databases are the bottlenecks when it comes to IOPS and scaling. The more processing you move to your application server, the better scalability you can achieve.

Yes and no, there are cases that you should not use FKs mostly for things like audit logs, order logs, stuff you never want deleted or modified in general.

In general it depends on whether you intend to use the database to drive a system, or whether you intend to use the data for reporting and data analysis.

Inconvenience in migrations is not a legit reason, if your migration would fail if you had FKs but doesn't because you don't you just broke your data, the errors are there to protect you. In other words your colleague is straight up wrong.

Your colleagues logic is akin to turning off the smoke detector because it starts beeping any time you barbecue indoors.

Of course I do. I think that it's crazy not to use database features to increase database integrity. I use foreign keys, I use not nulls, I use checks, I use triggers sometimes.

All my issues with databases usually stem from the fact that someone did not use enough checks and we got dirty data nobody knows what to do with.

My only non-conventional usage of database schemas is text field lengths. I have rule: it's either 20, 200 or 2000. It should be big enough to fit any non-insane value. Like 200 should be enough to fit a phone number. The main point is not to let absurdly broken data in.

I 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.

There is only upside to using foreign keys. When enabled, the benefits are obvious, so I won't discuss, and as for the drawbacks, the only drawback is performance. But if that's a concern, then guess what, on most databases, you can disable foreign keys.

Well, one might ask, what is the point of having foreign keys if they are disabled? And the answer is, there are several benefits. Here are a couple:

1. foreign keys, disabled or not, create a record of your data design that itself serves as documentation, and that can be programmatically queried, extracted, copied, modified etc, all of which reduces technical debt and is useful for other users or developers to understand your schema and work more efficiently.

2. You still have the option to enable the foreign keys. Furthermore, you can pass this job over to someone in your organization who may not have authority to create or modify foreign keys, but does have the authority to enable them and/or to fix the data however required to enable them

Yes. I use FK constraints and cascades. Experience has taught me doing these things in the application layer is very lengthy to get right, often error prone, and rarely as fast.

I use FKs and have migrated/updated the scheme multiple times over the years. If something goes wrong during migration that means I don't understand my own scheme properly. Getting rid of FKs for that reason feels like disabling all compiler warnings, or wrapping every single method in java in a try-catch block with an empty catch part.

I have not worked on a system where the performance mattered so much to consider not having a FK. I personally think that the key relationships should reflect the nature of the data. It prevents a lot of errors so that new devs don't create data which should not be able to exist. Don't make invalid data have a chance to exist or be representable. Make it difficult to pollute the db because relying on your app layer is risky

FK’s is what makes a relational database “relational”.

It sounds like your colleagues want NoSQL (key-value) given their no JOINs policy.

How is this even a question, besides denormalization cases, FKs are essential for data integrity and management if you have even slightly relational data (data that is composed of joining tables), it guarantees you a source of truth..

No, especially on large tables with billions of records. They make online schema changes impossible. More details: https://github.com/github/gh-ost/issues/331#issuecomment-266...

Why not just turn off foreign key checks during migrations and after validating the data, turn them back on? SQL Server lets you do this.

> difficulties during data migrations

It's not difficult; it merely requires that you think about the order of the migration operations.

> He'd rather have a smooth data migration process than having an unexpected error

This should be grounds for dismissal. Data integrity problems should be dealt with immediately instead of making it someone else's problem later. He's just kicking that "unexpected error" down the road to some poor soul that will spend weeks trying to figure out how the data got so messed up.

If you don't care about data integrity then use a different data storage solution.

My other beef with no foreign keys is that it makes a database a lot harder to understand.

There are any number of tools that will generate me a pretty and useful database schema diagram if I point them at a relational database. This is incredibly handy when you're new to a database and need to figure out which tables to query and update, and which (gasp) sprocs you need to call. I've been on projects where people have been poking around in the dark, and then a good database diagram has saved us days or weeks of effort trying to figure out how to make something work. As I say, there are plenty of tools capable of generating one of these in seconds or minutes[0].

However, if you don't have foreign keys, the utility of such a tool is severely diminished because you just get a big pile of nodes representing tables clustered at the top or bottom of your diagram (depending on exactly which layout algorithm is being used and how it's been configured).

[0] Many years ago I and three colleagues built one of these: Redgate's SQL Dependency Tracker (https://www.red-gate.com/products/sql-development/sql-depend...). It was pretty neat because you could build a diagram spanning databases, or even linked servers, and unlike most other tools at the time it could handle thousands and thousands of database objects, but the product name doesn't really help get across that it's fundamentally a diagramming tool. I built the dependency engine, the graph, and radar views. We used yFiles from yWorks for the graph layout calculations, with a bit of extra hackery, but I seem to remember yFiles lost compatibility with a newer version of .NET at some point so (or something along those lines) so RG ended up swapping it out for something else.

Besides the other reasons mentioned, FK's are also implicit documentation - even if you disable them.

Most (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!

In an OLTP system yes of course. In an OLAP system no since they’re often ignored anyway but are nice to be defined when trying to understand the schema. We logically use them in that the id column from says a supplier table might be found in the orders table.

Foreign keys are an implementation detail specific to the relational model. In the stereotypical example, an Order might have several Line Items, so each Line Item has a FK back to the Order, via the FK OrderID. (Ignore for a moment Assembly/Part where each Part can be in multiple Assemblies). Anyway the point is the that an Order has 1..N Line Items (we can assume an Order with 0 line items is degenerate and not allowed). What matters is that we can know the Line Items that belong-to an Order and the Order to which a Line Item belongs.

In a strict relational model implementation, the only way to reify that is by the FK of the Order in the Line Item, but in some other implementation, say a generic programming language, the Line Items might be an array or similar data structure that is part of the the Order, and the programming language implementation keeps track of the pointers or address offsets or whatever detail it cares about.

Anyway that's all beside the point.

In my experience, if the IDs are not autogenerated by the relational system, then it's relatively easy to migrate, however when data is full of IDs defined via some AUTO_INCREMENT behavior then migrations become an awful mess and any system (and this happens more frequently than you might expect) where a specific ID starts to have semantic meaning (oh, the customer's ID is 387437. whoops) then all bets are off and you might as well just give up and accept that your auto-generated IDs are now fixed for all time and can't be changed.

Oh and just to add, I have notebooks from previous employers where I have written THE Important IDs to Know, which started life as auto-generated numbers but which now are enshrined and encased in acrylic to the extent that years later they are important tribal knowledge.

Saying you refuse to use FKs because you keep running into problems with data migrations is like saying you refuse to use arrays in your code because you keep running into problems with out-of-bounds index errors.

Yes I use constraints, because I like having CASCADE for DELETE and UPDATE.

I think trying to manage consistency in your application is probably a bad idea.

"use FK, you fools!"

FK maintains your database integrity. If you care about data, just use it !

I've worked on a couple of systems that use foreign keys (and other constraints) specifically after a migration to ensure all the migrated data is consistent, but then drop them again to reduce the performance overhead of having them checked during production use.

Do... you not turn the foreign key constraints off during migration?

In SQLite it's a pragma, in Postgres you turn triggers off, those are the ones I've personally done but surely any relational database has this ability for this specific reason?

Absolutely "yes" on foreign key constraints.

There _is_ a scale, though. I have worked in enterprises where the database is the "contract" and where the business logic is implemented in the database, with triggers and all sorts of constraints and what not, in addition to foreign key constraints. But I now mostly work in service- or micro-service-oriented places where the "contract" is a REST(-ish) API, business logic is implemented in Java, and the database is exclusively owned by that one code-base, where not all business logic is replicated from the Java code into the database (still foreign keys, though!)

Your colleague doesn't understand relational databases, which, unfortunately, is fairly common - classes on RDBMSes are electives in most CS programs. It's scary/concerning this person is being allowed to operate like this - I'd suggest your company add design reviews and code reviews to reel this sort of rouge behavior in.

...Of course, if nobody understands relational databases at the company, then that won't help...

Even if data migration was difficult with foreign keys for some reason, they can be disabled at the time of migration - no need to put weird constraints on day-to-day operations.

I've joked before that I can often see which tables were created first and which ones were added over a longer time by checking where did people start giving up on foreign keys. But it's kinda true too.

I see them the same as stored procedures. If you use them, you better use them everywhere and have all your data consistency model in the fk and stored procedures. Also, figure out how to disable them during the more interesting online schema migrations. If you can't commit to that, they'll only cause issues down the line.

I can relate partly to the problem. Sometimes you want to import a single table from a backup and, because of FK constraints, a very specific order in the tables is required, and can span many tables depending on the complexity of the data.

As an alternative you can use Foreign keys without constraints. This way you get the conveniences of them without the migration problems. You can do this permanently (a bit wilder) or just before an import, and re-enable it later.

I go for constraints every time. Data Integrity is king as long as you can reasonably afford it.

Your data migration aborted on an unexpected error? Well in my view that's a problem and you need to figure out why that is happening and fix it.

But hey, you can always drop the constraint when you decide it's not worth it, and you can always add it back again after. Depends on the application too, sometimes it just doesn't matter if you make a mess.

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.

It is an old debate. You can get into awkward situations when you save and restore tables, everything has to happen in the right order and there's always the fear of some circular situation.

I'm remembering the time I was working at a place that had a huge number of Microsoft Access, Microsoft SQL Server and mysql databases and I was the first person they'd hired who knew how to do joins and they thought it was pretty scary.

> His main argument is difficulties during data migrations which he frequently encounters

He's just kicking bugs down the road. That's not engineering.

For me it's a casevof pick your poison.

If you have no FK, the day will come that your data corrupts. Tiny application bug, wrong manual data fix, incomplete datamodel communication. It's a case of when, not if. And when it happens, it can fester for weeks or months, corrupting all kinds of data in unfixable ways.

If you have FK, you can go all-in on relational. The stuff is so powerfull, you'd be dumb not to. But then comes the too-smart-optimizer problem. Just like with compilers and opengl shaders, the tiniest change might make you fall off the optimizer's preferred path and get a way-to-slow version. Even when no code has changed, a minor DB version or even a tiny shift in statistics will kill you.

Personally, I tend to consider integrity more important than performance, but both have a zone where they are good enough to make trading off worthwile.

So I do FKs and joins, and count on rigorous testing and monitoring to keep things in check. Someone else might decide otherwise. Both strategies require you to do the unsexy part of the wirk, or a harsh punishment will follow.

Well its an interesting subject, yes it is RDBMS i.e. relational database, tables=relations, alas it does not automatically mean that just because you have a relational database you must use FK. Most of relational databases theories were created at the time when databases were few megabytes in size so the theory start quickly collapsing with terabyte size databases and larger. I would say it is truly depends on your strategy - I've seen companies remove all FK and simplify databases management and vise versa. First and foremost I would test just how many FK violations are really there - if you have FK violations into thousands per 24 hrs you might need to take another look at your app on the other hand if you have very few FK violations does it make sense to lace your database with FK's thus significantly complicating administration? All and all the databases are designed to store your data and that is it, yes of course you can use the database to QA bad coding practices but it does not make it scale very well.

It would seem a bit crazy to me to ditch FKs for that reason. Why not just drop the constraint? I would much rather keep my data integrity.

The issue with managing the relationship just in code is if you ship a bug to break the relationship, you now have to manually fix your data, and if you want to find out when or where the bug was introduced, you're looking at commit history instead of a migration history. Same thing when it comes to making manual updates or adds in the db. Even if it's just on a dev stage, if your code makes an assumption about the constraint which isn't true, you can end up with bugs or exceptions on dev, which is also annoying. If you want to remove the assumption of the relationship from the code entirely, that would be more understandable, but not if instead it means replacing what would be an efficient constraint and join with a separate query.

I prefer relational databases for most cases unless I really need to use a non-relational store. I also use foreign keys whenever possible. From what I have seen, anything to enforce referential integrity is a benefit in most cases. It is more comforting to have a delete fail than have many other queries mysteriously fail down the line.

In most cases, a well designed database covers a multitude of sins and makes life for future you and your future team much easier. Some of the biggest dumpster fires of code I have seen started as a smoldering dumpster from terrible db choices that were made far harder to fix when it had been in production for a few years.

If someone put a gun to a developer's head and threatened to pull the trigger if the database was crap, sadly they would be forced to pull the trigger most of the time.

> He rather have a smooth data migration process than having an unexpected error and abort the whole operation that the migration is only a small part of.

that's some take, because if that "unexpected error" is not reported because FK integrity was turned off, that means the data containing integrity errors goes right in. Now your database is corrupt. Dealing with a DB where random rows here and there are not conforming to implied-only constraints is zero fun. in my own experience, things like the main page of the production site is a 500 error, with a stack trace deep into some logic nobody has looked at in two years, run the identical code on staging, works fine. Fun stuff! Seems like an odd choice to let errors like that stream right into your production DB without checking.

Yes I do! Your colleague reminds me of a colleague I had who would copy and paste code into node_modules via a shell script to share it because they didn't like to learn about npm publish. Do things the right way if you can and your life will be easier, even during the data migrations.

I do app databases for a living, so I thought I'd dump my 2 cents here. I am very much not on a high horse about it. I'd be perfectly happy to use FK if somebody felt strongly about it. I myself have never found them helpful. My databases are small enough and I write all the SQL access code by hand, so nobody is touching the data except me, and the SQL that I write myself. Things being half-deleted isn't a problem. In fact I can't ever remember where that has happened. I have lots of other problems, but that's not one of them. PKs, indexes, views, etc. I use all of that stuff, but not so much FK. YMMV. Prohibiting joins is throwing the baby out with the bathwater.

By default, yes, FKs and joins. However, I tend to work on high scale systems generally backed (primarily) by mysql. To scale out (tbs of data, hundreds of tables, millions of users, billions of daily interactions), we've had to remove some FKs, avoid db triggers, use caching heavily (including piggy backing requests where you don't serve an identical request from the db, you wait for the response and returned the memoized version). When you do choose to remove a FK, the need doesn't go away, you just move it. Now you have to look into data sync and out-of-band integrity checks and you have to have a plan on what to do when you go looking for data that is no longer there.

The entire point of using an RDBMS is to have constraints IMO. Foreign keys are one of the most basic forms of constraint.

"It's never gone wrong yet" is a good way to get yourself electrocuted when you're changing a light bulb.

Yes, when I did develop on MSSQL, I used them all the time. They force you to think, and hard, and saved my bottom quite a number of times. I then had to maintain for the last years a system with surrogates only and I hated it.

If you have any business app (OLTP) using FK is no brainer. I know devs whom fights for 99% code coverage and do not use any constraints in the database. Fixing bug in the code is easy. Fixing data inconsistency is not.

Hello frogcoder, Yes, I use FK's for non-trivial systems. Personally, I believe in the enforcement of FK's via DDL as they document the schema and serve their intended purpose of maintaining data integrity according to the rules of the schema. Enforcing constraints and any other system constraint is part of system design. From a debugging perspective, I would rather have the RDBMS return an integrity error when the layers using it don't follow the rules, instead of chasing down where the contraint was not enforced in every layer.

Yes.

Mainly to cascade delete though, since we were robust enough to insert proper data.

This was spurred on in new projects because in a very very large older project with many many tables we had problems with orphaned records.

(PostgreSQL)

I used to scare of it thinking why do I need it? everytime I delete sth it annoying me. I read blog post of GIthub not using FK key, and try to convince myself that FK isn't worth it.

One day, I decided let try this out. And turning out it wasy easiser than I think. When delete data just make sure to use cascade or ensure its associated data is deleted, which make sense.

Once I embrace it, FK now becomes my friend to enter data consistency, I cannot imagine how did I live without it before.

Yes, but it definitely depends on the model I'm putting into the database.

Specifically I hadn't thought through an Order model and the OrderLines ended up being dependent directly on the product meaning through an FK. The orders wouldn't "settle" once they had been completed, since the product could be updated and change the values of the orderline and order. Dumb dumb. It was one of the cases where denormalizing data, very much, makes sense

He's talking about FK constraints right?

We do not use them for write performance concerns.

There's plenty of nice features of the DB we're not allowed to use under the excuse of "performance". But I'm told this by people who live and breathe SQL, so I trust them and I hope they have evidence to back it up. Because a lot of these features we're not allowed to use would make our lives 100x easier if we could!

I can’t imagine not using FKs. Data integrity is more important than smooth migrations and if that’s the issue then he needs to learn more.

I'm removing them because I'm moving to a fully async realtime / offline-first architecture and pushing conflict resolution to the clients or to async server processes where automatic resolution is possible. I don't want to show errors to users during async background sync processes for data they may have populated days or weeks ago.

I'm working on a database currently that has "foreign keys", they are the relational fields but there's no foreign key relationship setup in the RDBMS. Also, there's multiple relations on one table instead of just 1-2, so it's a bit like everyone has their hands in the cookie jar. It's a pain in the ass.

Of course. Foreign keys are elementary. Try recursive CTEs. You can get ridiculous performance gains by not having to marshal data into the application space. It’s all about using the right tool for the job. If you’ve done solid system-level design then running migrations probably shouldn’t be the primary driver of your schema.

At work, we use foreign key constraints when it makes sense, that is over 50% of all cases considered. The factors considered are risk and performance: what is the risk of bad data to get in the tables and what is the impact of that happens vs the cost of extra processing time or extra hardware to compensate.

> He is not the only one I've met who insisted on not having FK.

A) The world is full of idiots.

> I've even seen large systems prohibit using JOIN statements.

B 1) WTF is wrong with these people? What do they think an RDBMS is for?!?

B 2) See above, exhibit A.

Is there a way to have foreign keys without the index and integrity constraint. I would love to be able to keep the relational mapping at the db layer. (I almost always actually want the integrity and indedx, but I'm just curious if its possible)

I use foreign keys with postgres, I used not to in mysql ~10 years ago. I feel it depends on the level of support of your db / how painful it is to override things if needed.

Performance can be another reason for skipping them but modern dbs are pretty good.

I'd love to have prohibition of using just any old key in a join, but Snowflake, and Redshift to a lesser extent, just don't care.

In fact, Snowflake really really doesn't care to the extent that I've seen non unique primary keys in tables.

> His main argument is difficulties during data migrations which he frequently encounters. He rather have a smooth data migration process than having an unexpected error and abort the whole operation that the migration is only a small part of.

NUTS!

> I suspect the errors might be mainly caused by not considering data integrity at all at the first place

Spot on.

----

I deal with interface with MANY ERP-like software with as many "database designs" as you can't imagine, and have face the fun of interface with many system in my long career.

Among DOZENS only 2 major packages have zero issues and are the only ones with competent schema designs.

Let me tell you how bad is it:

One of my interfaces is with a package that, somewhat, manage to mix all the ways to be wrong about RDBMS:

- MySql, not a good start

- Only Strings and Ints datatypes, and the Ints datatypes are not many

- This means ALL data is suspected (dates, money, and yes, strings too)

- The tables are named `UNCC_00001, UNCC_00002...`

- The fields are named `FT_0001, FT_0002...` and obviously the first is the PK. This pk is purely advisory, other fields can be part of the PK but you can't know looking at the schema

- NO FK at all, so ALL TABLES have integrity issues.

- NO indexes, because why make joins or lookups faster?

- Weird normalization, like yes, no, maybe?

- And no option to let me add some sanity to the DB, because the database is (in my niches) "property" of the software makers so can't be touched, only queried.

And not tell the rest, because this is only the query side, the CRUD side is nuts, and not wanna remember much about it.

---

RDBMS is one of the most simplest ways, to get FOR FREE, and minimal effort, a sane, well behaved and performant system.

Refuse to use them is NUTS. Refuse to use the abilities they have is NUTS.

I only have ONE actual company (in +20 years) with a real reason to go NoSQL, and that only was for a fraction of the thing (in fact, the data was mirrored to a RDBMS for reporting!). And maybe that was true 5-10 years ago, today I think modern SQL engines catchup more and more on the scenarios (Today I have used timescale just for speed up log processing, and was so much nicer that deal with weird log storage engines).

Fear of having integrity issues raised is NOT the reason not to use Foreign Keys. Performance at scale is. Foreign key constraint checks can be costly on write intensive applications with many tens of thousands of QPS.

> He rather have a smooth data migration process than having an unexpected error and abort

Personally, I would prefer errors to become immediately obvious, while I'm active and there's a roll-back ready to be applied.

I tend to use foreign keys everywhere. The only time that I would skip it is when I do not need to cascade deletes. These are mostly metadata tables that will be archived on a periodical basis.

Hard to believe so few talks why fkeys are a big no no in mysql. Go figure out how to use any online schema modification tool short of a blue green deployment or God forbid, triggers.

from https://thedailywtf.com/articles/directive-595

Dear Database Architect,

Directive 595 Part 2 is as follows.

  "Foreign and Primary Key constraints give lack of flexibility, more 
   costly evolution, inhibit the use of the database acting as a 
   service to applications and make it an inhibitor to evolution."

As such, please remove from all production databases.

Sincerely, Chief Architect Gerald

I don't use foreign key constraints on the database.

I create all fields as NOT NULL and use empty string in place of NULL.

Last time I tried foreign key constraints can't work in an environment like this.

Yes. You can always disable enforcement when you need to run a data migration. It’s no reason to avoid having them.

Referential integrity saves hours of pain from weird DB issues down the line.

I use FKs in databases that I have designed.

I also interact with VAX applications that use Rdb (now owned by Oracle) that avoid constraints of all types for performance reasons.

Christ Jesus, yes.

I mean, there can be reasons why not, but why use an RDBMS at all if you’re not going to take advantage of its features?

Looks like your colleague wants to ultimately manage a NoSQL server, and doesn't fully understands RDBMS'es.

Yes - is the alternative even a thing? I feel like I'm in Wonderland suddenly.

prohibit using JOIN statements is really cargo cult :-)

Join is simply an index scan. And is guaranteed to be cheaper then the ORM having to send many request to the server.

Nah.

Everyone here seems to be super diligent about FKs. I wonder if that's a sampling bias.

I've worked on a few projects where they were considered an unnecessary hassle, especially when the RDMS had some performance or functionality limitations when using them. Also "on delete cascade" seems scary. Plenty of systems even just set deleted=true instead of actually deleting (at least before GDPR).

Not every CRUD app treats data integrity as a holy grail. A social network for hamsters can lose a comment, no big deal. Some applications threat databases as a bag of key-value pairs, and the inconvenience of migrations ends up with a JSON in an "everything_else" column. Move fast and break relational integrity.

I always felt slightly ashamed for the fact that I don't use foreign keys. I feel better having read this thread!

I think that this would make a good HN poll, for example: https://news.ycombinator.com/item?id=21231804

Though the answers would probably vary and there's most likely lots of nuance per individual case (which might matter more than just yes/no), personally I can think of the following as examples:

  - yes, we use foreign keys
  - yes, but we use them in testing environments and turn them off in prod
  - no, we don't use them because our database doesn't support them (e.g. distributed like TiDB)
  - no, we don't use them and check integrity and orphaned data ourselves
  - no, because our system design doesn't allow us to use them meaningfully (e.g. OTLT and EAV)
  (also, talking about whether to cascade or not might be useful, e.g. whether you want to manually clean up related data, or not)

Someone else mentioned varying schools of thought, which rings true. Personally, my opinions about database design in general are along the lines of:

  - avoid EAV and OTLT outside of very specific cases, have multiple tables over few (e.g. employees, employee_contact_information, employee_vacations, employee_notes instead of employee_fields and/or employee_field_values)
  - have foreign key constraints across your tables, so that you might not end up with orphaned data, *consider* cascading the constraints (depends on requirements)
  - use views liberally, especially for complex requirements in regards to selecting data, so that your app (or ORM in it) can map against it in a simple manner
  - outside of batch operations, prefer to modify data through the app, instead of procedural SQL, since that's easier to debug; I'm yet to see someone use breakpoints/watches for stored procedures successfully

Though my ideal database design probably looks way different and scales slightly differently (which hasn't mattered as much yet) than someone else's.

There are people who want to build their entire database around a "classifier" system, about which I wrote previously here: https://news.ycombinator.com/item?id=32416093 (this also makes the DB hard to visualize as ER diagram because of meaningless links, and sometimes makes the DB hard to use without the app, e.g. type_enum_value vs table_name).

There are people who want to do everything in procedural SQL (I've seen application views call stored procedures to fetch all data and validate forms), there are those who don't want to touch it with a 10 foot pole.

It really varies a lot, though in my experience it's invaluable to be able to feed a database into something like DbVisualizer and get an overview about how the different tables are related to one another, basically like documentation: https://www.dbvis.com/

Yes, until performance prohibits. But that’s usually in the billions of records or more.

Are you trolling?

Yes, always.

Never did. Never saw any need for it.