I got to the bit on page 144 that says: “Rails migrations doesn’t provide a database-independent way to specify these foreign key constraints, so we had to resort to adding native DDL clauses (in this case, those of SQLite3) as options…SQLite3 version 3.4.0 will only parse but will not otherwise enforce foreign key constraints.”
and the footnote continues: “…Rails migrations don’t let you specify constraints. However, when it comes to database integrity, many (including Dave and Sam) think an ounce of extra checking can save pounds of late-night production system debugging. If this appeals to you, additional information can be found on page 286.”
This is disturbing, and it raises several questions in my mind. For one, if SQLite doesn’t currently do anything with foreign key constraints, why bother specifying them in a DDL specific to SQLite? Second, what are we supposed to do with the code shown here if we want to move into production? Surely SQLite is for development.
Also, if Rails doesn’t support database-level foreign key constraints, I’m left wondering if it will even work if I add them to the database I end up using - for example, will it delete items in the right order? After seeing this and doing some searching, I’ve already found discussions of how adding constraints causes trouble when running tests, as the test fixtures are loaded and unloaded.
I’m wondering if there are plans to fix this issue by adding support in Rails for specifying database-independent foreign keys, perhaps in an upcoming version? This seems like a rather glaring hole in the idea that ‘you specify your models and Rails abstracts away the details of the underlying database’.