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

Postgres has index concurrently which is better or few other instant things which MySQL 8 also has but you still need to be careful.

But some things like adding a column with default value is still hard.

https://www.braintreepayments.com/blog/safe-operations-for-h... this article has a good guideline on this.

You're exactly right. This is the crux of it. In many domains (not every domain, but every one I've ever worked in) you can delete/rewrite/change the app code without much fuss but the data is vital.

For that reason having constraints enforced by the system that stores the data, external to the app code which developers will inevitably mess up, is so useful and important.

So many issues in my experience have been similar to the one you describe. If the right constraints had been present so much work and so many headaches could have been avoided.

I feel like people who claim they're not needed or not important or "the app code will do it" need to be wrangled into maintenance work of old systems for a year or two until they repent. Rather than boshing out an ill-considered prototype and then moving on before the bugs are discovered.

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.

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

On point 3 it should be noted that it's almost always a mistake to optimize for scale at the start of a projects lifetime. There will be exceptions, but in general this is true.

You can always migrate that data to a more useful format if you find it starts hurting you at scale, if you start with the assumption you need the scale you're hurting yourself in the here and now for theoretical future benefit.

> The real reason people use joins is because they want to pack a lot of details onto the user's screen when they are looking at a list view

This is completely, emphatically wrong. I'm somewhat miffed at the air of authority you're using here. People use joins for the normalization of data.