Adding foreign key constraints in rails migrations

by serge on June 3rd, 2009

More often than not models have associations. And foreign key constraints are sometimes used for keeping them in good shape. The role of such constraints can be indispensable or trivial, depending on how your database is managed.

Here’s a rails plugin to add foreign keys for associations. It doesn’t have any dependencies. And it’s code is simple so it can be read in one take. These are the two things in plugins world that I appreciate the most.

In migrations, when a table is created, it recognizes columns which are foreign keys by _id suffix. Then it adds foreign key declarations. Referred table name is guessed from column’s name according to rails conventions. This can be disabled by adding :references => false option.

If a column should refer to a table which name can’t be inferred that way, you can explicitly specify it by using a symbol or a string.

create_table :comments do |t|
  t.text :body
  t.integer :commented_post_id, :references => :posts
end

will produce

CREATE TABLE comments(
  id SERIAL PRIMARY KEY,
  body TEXT,
  commented_post_id INTEGER REFERENCES posts
);

And with strings you can do all the things that you shouldn’t want to do:

create_table :comments do |t|
  t.text :body
  t.integer :commented_post_id, :references => 'whatever(strange_pk) ON DELETE RESTRICT'
end

will become

CREATE TABLE comments(
  id SERIAL PRIMARY KEY,
  body TEXT,
  commented_post_id INTEGER REFERENCES whatever(strange_pk) ON DELETE RESTRICT
);

That can be handy when dealing with nasty legacy schema.

And you can add foreign key constraints for already created tables:

add_foreign_key_constraint(:posts, :comment_id, :comments, :p_id)
ALTER TABLE posts ADD FOREIGN KEY (comment_id) REFERENCES comments(p_id);

You get the idea. For more examples please see spec/migration_spec.rb here

So the next thing you may want after you’ve packed your database with foreign key constraints is to get rid of them. In your tests. Rails disables referential integrity checks before loading fixtures and enables them after that. So missing references in fuxtures are ok. And if you need to disable foreign key constraints for certain table you can always make a call.

remove_all_foreign_key_constraints('comments')

Another option is to automatically drop all constraints for tables that are referenced by test suite fixtures statement. That is what another branch with_fixture_patch of the plugin contains. I’m still considering if it can be useful…

To install run

./script/plugin install git://github.com/bgipsy/fk_constraints.git

from your app root directory.

Comments are closed for this entry.