4 ms·
I am probably in the minority and would get shouted at by a DBA of yore, but I use foreign keys for referential integrity and… that’s it. We use soft deletes fo
by Dachande663 3y ago
I am probably in the minority and would get shouted at by a DBA of yore, but I use foreign keys for referential integrity and… that’s it. We use soft deletes for almost all rows, so the whole cascading side is less relevant for us than getting an error in test or even production because a missing relation has been used. Combined with using non-integer primary keys, it’s godsend.
- Svip 3y agoI 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.
- shlomi-noach 3y agoOP. 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.
- _a_a_a_ 3y agowhat does 'more operationally friendly' mean? TIA
- shlomi-noach 3y agoLike 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.
- _a_a_a_ 3y agoI didn't realise grep_it and you (shlomi-noach) were the same, apologies
- shlomi-noach 3y agoWe are not the same. My mistake for writing "OP".
- shlomi-noach 3y agoWhoops. I wrote "OP" when I really meant "Post author". I'm a bit rusty with HN notations.
- tomnipotent 3y agoeBay 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).
- Phelinofist 3y agoWhat's wrong with non-integer primary keys?
- takethisdownplz 3y agobesides 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.
- evilspammer 3y agoI would expect they're referring to UUIDs or something
- tomnipotent 3y agoYou can pack fewer of them into a page, which means more disk IO for things like index scans.
- gigatexal 3y agoI was a DBA for 5 years and we outlawed cascading anything be they updates or deletes. So you’re not doing anything wrong.