Software Development | Ruby on Rails
Foreigner - Foreign Key Plugin / Gem

Rails supports relationships between entities in the models, including handling any foreign key constraints. That means that most Rails people don't seem to worry about including foreign key constraints in their database scheme. For the moment at least I can't put aside all those database lessons at university and feel I should address foreign key constraints in the database as well as the model.

It seems a chap called Matt Huggins agrees with me. He has just release a plugin / gem for this purpose: Matt Huggins: Foreigner. It is a gem for Rails 3 and a plugin for earlier versions of Rails.

Once it is installed using it is trivial. Use add_foreign_key in the self.up part of the migration and remove_foreign_key in the self.down. Here are a couple of examples with matching add_foreign_key and remove_foreign_key. The example is for a network diagram with nodes which are linked by arcs.

The simple scenario is to link a child rows (nodes) to a parent row (diagram). 

class CreateNodes < ActiveRecord::Migration
  def self.up
    create_table :nodes do |t|
      ...
    end
    add_foreign_key(:nodes, :diagram)
  end

  def self.down
    remove_foreign_key(:nodes, :diagram)
    drop_table :nodes
  end
end

The second example is slightly more complicated becase an arc links two nodes. In the case were the foreign key doesn't have the default name then add_foreign_key must specify the column to use. I had to delve into foreigner's code to see what parameters were needed for the remove_foreign_key. You don't have to specify the parent table but you do have to specifying the column to use. 

class CreateArcs < ActiveRecord::Migration
  def self.up
    create_table :arcs do |t|
      ...
    end
    add_foreign_key(:arcs, :node, :column => 'from_node_id')
    add_foreign_key(:arcs, :node, :column => 'to_node_id')
  end

  def self.down
    remove_foreign_key(:arcs, :column => 'to_node_id')
    remove_foreign_key(:arcs, :column => 'from_node_id')
    drop_table :arcs
  end
end

Aside from the ease of writing these the real beauty of this plugin / gem is that it works with SQLITE3. Or more accurately it doesn't break with SQLITE3. As it happens SQLITE3 doesn't support foreign key constraints at all. Foreigner knows this and just ignores the constraints for SQLITE3. In fact the only database that foreigner will add the constraints to is MySQL. Suits me - SQLITE3 for development and test; MySQL for production.