Rails holds some conventions that enforcement of data integrity should be done in the application, not in the database. For example, Rails even supports some database designs that cannot use foreign keys, such as Polymorphic Associations. Basically, Rails conventions have treated the database as a static data storage device, not an active RDBMS. Rails 2.0 … Read more
When discussing auditing, I would go back to the purpose behind it. It isn’t really a backup but rather a history of what has been. For example, for StudentScore, you would want to be sure not to lose the fact that the student originally had a 65% when they now have a 95%. This audit … Read more
There are two things you can do (these are complementary, not alternatives): Create your foreign key constraints as DEFERRABLE. Then, call “SET CONSTRAINTS DEFERRED;”, which will cause foreign key constraints not to be checked until the end of the transaction. Note that the default if you don’t specify anything is NOT DEFERRABLE (annoyingly). Call “ALTER … Read more
It really depends on the behavior you want. In case 1, destroy will be called on each associated order, and therefor so will the ActiveRecord callbacks. In case 2, these callbacks are not triggered, but it will be way faster and guarantees referential integrity. In an application’s infancy, I’d recommend going with :dependent => :destroy … Read more
CHECK constraints cannot currently reference other tables. The manual: Currently, CHECK expressions cannot contain subqueries nor refer to variables other than columns of the current row. One way is to use a trigger like demonstrated by @Wolph. A clean solution without triggers: add redundant columns and include them in FOREIGN KEY constraints, which are the … Read more
Looks like my answer is here… Like MySQL in general, in an SQL statement that inserts, deletes, or updates many rows, InnoDB checks UNIQUE and FOREIGN KEY constraints row-by-row. When performing foreign key checks, InnoDB sets shared row-level locks on child or parent records it has to look at. InnoDB checks foreign key constraints immediately; … Read more
You can solve this by allowing NULL in the foreign key column tblImageFlags.resolutionTypeID. PS Bonus points to whomever tells me whether, in the case of databases, it’s “indexes” or “indices”. The plural of index should be indexes. According to “Modern American Usage” by Bryan A. Garner: For ordinary purposes, indexes is the preferable plural, not … Read more
Reasons to use Foreign Keys: you won’t get Orphaned Rows you can get nice “on delete cascade” behavior, automatically cleaning up tables knowing about the relationships between tables in the database helps the Optimizer plan your queries for most efficient execution, since it is able to get better estimates on join cardinality. FKs give a … Read more
Postgres 15 or newer Postgres 15 (currently beta) adds the clause NULLS NOT DISTINCT. The release notes: Allow unique constraints and indexes to treat NULL values as not distinct (Peter Eisentraut) Previously NULL values were always indexed as distinct values, but this can now be changed by creating constraints and indexes using UNIQUE NULLS NOT … Read more