small medium large xlarge

25 Aug 2008, 00:36
Charlie O'Keefe (5 posts)

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’.

09 Oct 2008, 21:59
Jason Franklin-Stokes (4 posts)

The general consensus is that the data in your database should outlive the software that you use to read, write and update the data in it.

Therefore if you include foreign key constraints on a database level any old piece o software can use the data – You make your data rails independent.

The other advantage is that you can produce diagrams showing the relations of your tables to one another (which is in fact helpful when things get larger).

Also adding these constrains gives you a safety net when deleting dependent fields across different tables. i.e. if your miss a declaration your code, the database won’t. – thus decreasing the risk of table rows floating around in the table with no parents.

Sqlite3 does not support foreign key restrains - however, you should no be using it in production mode. Use Postgress or MYSQL etc - which support this feature anyway.

You must be logged in to comment