[petalmind]

You can't enforce FKs if you just split a database into several, due to regulatory, performance/maintenance, organizational or architectural (m*croservices) reasons.

Enforced PKs in modern setting basically only work in a single uhmmm "transaction domain" (in a sense of ACID). You can certainly use two-phase commits or whatever, but that would kill performance so much that nobody (I think) seriously uses that.

Like, I think most modern "NoSQL" solution doesn't have enforced FKs.

Even in a single transaction domain you will get performance improvements if you get rid of enforced FKs. I remember a very old podcast (maybe 2005 or even earlier), a conversation with Werner Vogels where he explained how they were fixing performance issues with Oracle that they were using at that time. For me it was an eye-opener.

The story basically is that when they created an order, they allocated a new ID for an order, then inserted rows in the "order_items" table, and only at the end they inserted a row in "orders" table. All this was NOT in a transaction.

If there was an error during this, they just left the dangling "order_items" rows, and cleaned them up regularly with the script.

Otherwise, Amazon (the web shop) could not handle the load, and it was 20 years ago on a state-of-the-art Oracle relational database(r)(tm) with all Amazon's resources.

This is when I realized that God is dead and everything is allowed.

[mkleczek]

This is a false alternative:

The fact that there are situations when implementing referential integrity is difficult does not mean you shouldn’t do it at all.

Secondly: relational model is based on logical-physical independence. Referential integrity is specified at the logical level and does not dictate any particular implementation. OTOH violating 1NF makes it impossible to even specify RI constraints.

NoSQL databases do not implement RI exactly because it is not possible to even specify it due to the lack of formal data model. And that’s not their strength but one of the many weaknesses.

The story about optimising Oracle performance by giving up data integrity is only an illustration of why you should not use Oracle :) Just use Postgres.

There is no problem with RI in Postgres as long as you remember to have an index on FK columns. Using CTEs allows you to perform data manipulation in a single statement with constraints enforced at the very end.

Similarily: giving up data integrity due to microservice architecture cargo cult is… tail wagging the dog.

[petalmind]

> The fact that there are situations when implementing referential integrity is difficult does not mean you shouldn’t do it at all.

> Referential integrity is specified at the logical level and does not dictate any particular implementation. OTOH violating 1NF makes it impossible to even specify RI constraints.

Now we're bargaining. If referential integrity does not dictate any particular implementation, then having an application-level RI and no database-level constrains should also be acceptable. Yet this is not a common FK enthusiasts position.

We just had a discussion of DSQL where a lot of people were saying things like "no FKs thus no real database". Well, I dunno.