… and a quasi-religious intolerance for foreign key support but that was OK …
I thought it was time grow over that a few weeks ago, so I used them in some PG table setup. And again they managed to mess things up. I’ll stick to religiously intolerating them.
If you’re in a situation where you feel like a foreign key is causing you problems, then you’re in a situation where you think you want to have an inconsistent database. Whenever I’ve thought that, I’ve always been wrong. YMMV.
I haven’t met this mind set before.
I have been kick in the pants by DB’s for doing stupid things with foreign keys, but every time that has happened I have thought…“Yup, that was stupid of me, that’s a bug, thanks for telling me.”
So under what circumstances do they mess up?
They don’t mess up: I do.
This case was a table A with a bunch of entries, generated from somewhere. Table B was the new table, and had tags, pointing to A, with a foreign key. So I put this live and someone spend a few hours tagging things. Everyone happy.
Then the nightly script which maintained table A ran. It worked by deleting everything and then inserting everything relevant again, which was a perfectly fine thing to do before table B showed up. But of course the first time the cron ran the ‘CASCADE DELETE’ removed all tags.
To be sure, it’s me messing up, but still they don’t seem to align with me :)
Ah, the “rm -rf” of the DB world. :-D
ON TRUNCATE CASCADE
“Some men just want to watch the world burn”
I have definitely gotten into situations with deeply nested foreign keys where I really would have liked to delete a bunch of rows but the database stopped me with an inscrutable message.
And if you have a highly normalized schema, then insert order and failure handling can be nontrivial, especially if your language is a single threaded dumpster fire. And especially in the presence of an ORM.
In fact, ORM and foreign keys in general seem to find edge cases in production.