On 4/8/22 09:27, Magnus Hagander wrote:
On Fri, Apr 8, 2022 at 3:23 PM Perry Smith <pedz@xxxxxxxxxxxxxxxx <mailto:pedz@xxxxxxxxxxxxxxxx>> wrote:It has been a long time since I’ve done Rails stuff. What follows is the best I can recall but please take it with a grain of salt. The first problem is that generally Rails does not put constraints in the database. There were others like me who thought that was insane and would put constraints in the database — this includes foreign key constraints, check constraints, etc. About the only constraint that could be added into the DB using native Rails was the “not null” constraint. When foreign and other constraints were added, it broke something they call “Fixtures” which are present db states that are plopped into the DB during testing. To “fix” that, I (and others) would add this into our code base: (I’m adding this to see what you guys think of it — is it safer / better or just as insane?) def disable_referential_integrity(&block) transaction { begin execute "SET CONSTRAINTS ALL DEFERRED" yield ensure execute "SET CONSTRAINTS ALL IMMEDIATE" end } endThis is perfectly normal code and nothing wrong with it. DEFERRED constraints are how you are *supposed* to handle such things. It defers the check of the foreign key to the end of the transaction, but it will still fail to commit if the foreign key is broken *at that point*. But it lets you do things like modify multiple tables that refer to each other, and have the changes only checked when they're all done.
Indeed, especially because this code does not require any elevated permissions, guarantees referential integrity at commit time and guarantees that no inconsistent, intermediate state will ever be visible to another, concurrent session.
It only affects constraints that have been declared DEFERRABLE. Those that are not are silently ignored (as per SQL standard).
A lot of frameworks didn't support foreign keys because one of the most popular databases at that time didn't support them. Well, the SQL parser of that particular database would accept the syntax, but the engine would not enforce anything. Even the manual of that database stated that "foreign keys are mostly for documentation purposes and are not needed as long as the application does all operations in the correct order." They changed that part of the documentation when support for InnoDB was added. Therefore I would not put all blame on the Rails developers.
Best Regards, Jan
