I remember working on a large database without foreign keys for a while. When asked why did not use foreign keys, I was told that they didn't like the opaqueness of CASCADE, which I could understand. I guess I did not give it much thought afterwards, until I later ended up in a shop, where the database did include foreign keys, but only with RESTRICT. It was eye-opening how useful foreign keys were, when they were just integrity checks.

OP. I agree! RESTRICT is by far the best rule to use, and makes the most sense. Perhaps to balance my post a bit, and for what it's worth, I don't advocate for "don't ever use foreign keys" as a blanket statement. My experience was one where using foreign keys did not make sense. I do wish they were more operationally friendly.

what does 'more operationally friendly' mean? TIA

Like the issues I mention in my post: modifying the data type of a column that is used by a foreign key; otherwise the fact you can't run Online DDL on a table that participates in foreign key relationship ; that INSTANT does not support (yet?) adding/removing foreign key constraints ; that cascaded writes are not written the the binary logs.

These are all things that the casual developer doesn't deal with when designing a schema and writes an app that INSERTs/DELETEs/UPDATEs to tables with foreign keys. But once there's a need for a change; once you wire 3rd party tools onto your database, that's where the operations hit a wall.

I didn't realise grep_it and you (shlomi-noach) were the same, apologies

We are not the same. My mistake for writing "OP".

Whoops. I wrote "OP" when I really meant "Post author". I'm a bit rusty with HN notations.

eBay released a blog/paper over two decades ago talking about how they achieved scale in part by removing FK constraints from their database. It was vogue for the next decade when SAN/DAS IOPS were ~1-3k, and FK constraints could lead to more disk writes (like Postgres multixact).

What's wrong with non-integer primary keys?

besides performance for using something that is not often tested by devs working in performance, bad support for locale collating and open you for bugs such as indexing on email but allowing application to store both upper and lower case etc.

I would expect they're referring to UUIDs or something

You can pack fewer of them into a page, which means more disk IO for things like index scans.

I was a DBA for 5 years and we outlawed cascading anything be they updates or deletes. So you’re not doing anything wrong.