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

I use foreign keys quite often in my schemas because of data integrity, while my colleague has a no FK policy. 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. I suspect the errors might be mainly caused by not considering data integrity at all at the first place, but I can feel his pain. To be fair, as far as I know, he never had major data problems.

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.

Personally, I see the data integrity out weights the inconveniences, do you use FK for your systems, what are your experiences?

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.

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.

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

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