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

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.

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.

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

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.

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.

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.

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

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.

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)

  - 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

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/

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.

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?).

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.

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