Rails: Foreign Key Constraints

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:

t.integer :product_id, :null => false, 
  :options => "CONSTRAINT fk_line_item_products REFERENCES products(id)"

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!

I tried a plugin to add foreign key constraints, but I got frustrated pretty quickly. I fell back to executing plain SQL:

execute %{
  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
}

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.

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')

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

Ugh, painful. (Apparently, the silence method is only available on controllers. There's a different logger for migrations.)

Oh well. My tests pass, and I have referential integrity.