Ruby on Rails – Foreign Key Constraints in MySQL

As an ex-DBA, one of the things that annoys me about Rails is that migrations don’t have a way of setting up referential integrity (I’m still using Rails 1.2.3, so this may have changed). But apart from that, I lurv Rails :-)

Here’s some code I wrote to for adding and removing foreign key constraints on MySQL (using InnoDB, of course). There’s other code out there to do the same thing, but they didn’t do what I wanted, or required installing a plugin.

In the lib directory, add the file migration_helpers.rb:

module MigrationHelpers
  def foreign_key(from_table, from_column, to_table, to_column, on_delete='SET NULL', on_update='CASCADE')
    constraint_name = "fk_#{from_table}_#{to_table}"
    execute %{alter table #{from_table}
      add constraint #{constraint_name}
      foreign key (#{from_column})
      references #{to_table}(#{to_column})
      on delete #{on_delete}
      on update #{on_update}
    }
  end
  def drop_foreign_key(from_table, to_table)
    constraint_name = "fk_#{from_table}_#{to_table}"
    execute "alter table #{from_table} drop foreign key #{constraint_name}"
    execute "alter table #{from_table} drop key #{constraint_name}"
  end
end

Then, in a migration you would do something like the following:

require "migration_helpers"
class Vmtypes < ActiveRecord::Migration
  extend MigrationHelpers  # see lib/migration_helpers.rb
  def self.up
    create_table :vmtypes, :primary_key => :vmtype_id do |t|
      t.column :vmname, :string
    end
    execute "alter table asset add column vmtype_id int after vmhost_id;"
    foreign_key :asset, :vmtype_id, :vmtypes, :vmtype_id, 'RESTRICT'
  end

  def self.down
    drop_foreign_key :asset, :vmtypes
    drop_table :vmtypes
    remove_column :asset, :vmtype_id
  end
end

I wrote this code for MySQL; I don’t know whether it would work with other backends. Notice the MySQL oddity that you add a foreign key using one line of code, but drop a foreign key using two lines of code (foreign key then key).

On of the features of my code for adding the foreign key is that it allows (requires) you to specify the column names. I did this because I like the primary keys in my tables to be named foo_id rather than id, to make things a little more self documenting.

The next thing I want to work out is how to create/use tables with non-integer primary keys – seems like an issue that a few people have struggled with…:

Update 9-June-2009:

I’ve updated my code to allow suffixes on foreign key names – useful (for example) when having multiple foreign keys going to the same destination table:

module MigrationHelpers
 def foreign_key(from_table, from_column, to_table, to_column, suffix=nil, on_delete='SET NULL', on_update='CASCADE')
   constraint_name = "fk_#{from_table}_#{to_table}"
   constraint_name += "_#{suffix}" unless suffix.nil?
   execute %{alter table #{from_table}
     add constraint #{constraint_name}
     foreign key (#{from_column})
     references #{to_table}(#{to_column})
     on delete #{on_delete}
     on update #{on_update}
   }
 end
 def drop_foreign_key(from_table, to_table, suffix=nil)
   constraint_name = "fk_#{from_table}_#{to_table}"
   constraint_name += "_#{suffix}" unless suffix.nil?
   execute "alter table #{from_table} drop foreign key #{constraint_name}"
   execute "alter table #{from_table} drop key #{constraint_name}"
 end
end


Share This


 


5 Responses to Ruby on Rails – Foreign Key Constraints in MySQL

  1. Hi. I am using this in rails 2.2.2 and it works like a charm, the only thing is, that now, instead of “require file_name” one should use “include ModuleName”

  2. [...] and getting all the validates_presence_of, has_many… :through…, etc. stuff set up. I even used this lovely module to ensure I have proper foreign key [...]

  3. Once you’re Rails app is properly specced out, wouldn’t any db referential integrity checks would simply be overhead?

    • That’s a viewpoint some developers would take; dba’s (like me) on the other hand would say databases often last longer than expected, and are used in ways that were never expected, hence it makes sense to keep referential integrity. You’re going to have to decide, based on your situation.

      • I completely agree with Sonia. The farther into the core of an application you can push constraints that ensure data integrity, the better. Even if the database doesn’t outlive the Rails app, it might be necessary to open up access paths to the data that circumvent the Rails app. If this happens, you want the database to self-enforce as many constraints as possible.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>