I like referential integrity. Rails doesn't provide support for database-level foreign key constraints. "Agile Web Development with Rails" uses code such as the following to add foreign key constraints:
I tried a plugin to add foreign key constraints, but I got frustrated pretty quickly. I fell back to executing plain SQL:
To be fair, MySQL's errors aren't always the most helpful. I encountered a couple of syntax errors. One was because I misspelled a column name. Another was because I put the name of the foreign key constraint in the wrong place. In both cases, I ended up with the following:
Since I'm trying out test driven development, I knew I had to write a test before I could go mucking around with my schema. It turned out to be fairly hard to test that the database itself enforces a foreign key constraint. After all, Rails loves to try to enforce those constraints itself. Here's the RSpec test I ended up with:
Oh well. My tests pass, and I have referential integrity.
t.integer :product_id, :null => false,That doesn't work for me. The migration ran just fine, but when I looked at the database, the constraint wasn't present. I hate it when things fail silently!
:options => "CONSTRAINT fk_line_item_products REFERENCES products(id)"
I tried a plugin to add foreign key constraints, but I got frustrated pretty quickly. I fell back to executing plain SQL:
execute %{The nice thing about plain SQL is that I can understand exactly what's going on. There are no layers of abstraction in my way, and if my SQL is wrong, MySQL will complain.
CREATE TABLE roles_users (
role_id INT NOT NULL,
user_id INT NOT NULL,
FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
INDEX (role_id),
INDEX (user_id)
) ENGINE = INNODB
}
To be fair, MySQL's errors aren't always the most helpful. I encountered a couple of syntax errors. One was because I misspelled a column name. Another was because I put the name of the foreign key constraint in the wrong place. In both cases, I ended up with the following:
ERROR 1025 (HY000): Error on rename of './myapp_development/mymodel' to './myapp_development/#sql2-87-40c' (errno: 152)Lovely! Oh well, at least it's better than failing silently.
Since I'm trying out test driven development, I knew I had to write a test before I could go mucking around with my schema. It turned out to be fairly hard to test that the database itself enforces a foreign key constraint. After all, Rails loves to try to enforce those constraints itself. Here's the RSpec test I ended up with:
require File.expand_path(File.dirname(__FILE__) + '/../spec_helper')Ugh, painful. (Apparently, the silence method is only available on controllers. There's a different logger for migrations.)
describe Role do
context "the roles table" do
it "should enforce foreign key constraints in the database" do
verbose = ActiveRecord::Migration.verbose
begin
ActiveRecord::Migration.verbose = false
result = ActiveRecord::Migration.execute("SHOW CREATE TABLE roles_users")
sql = result.fetch_row[1]
[/CONSTRAINT/, /FOREIGN KEY/, /REFERENCES/].each do |phrase|
sql.should =~ phrase
end
ensure
ActiveRecord::Migration.verbose = verbose
end
end
end
end
Oh well. My tests pass, and I have referential integrity.
Comments
As far as I'm concerned, it's the right balance of useful and not too magical.
Thanks, again!