Foreign key migrations: hands on : Ewout blogt.

In the previous article, I explained the benefits of backing a rails application with database constraints. Referential integrity was mentioned, along with some general gotchas. In this post, we’ll apply referential integrity to an existing rails application, using the foreign key migrations plugin. The sample project is on github.

The reason we use that plugin is it will automatically add the required foreign key constraints to associations named by the rails conventions. Add a column person_id to the comments table, and the plugin will assume you are referring to an id in the people table. Of course, the default behaviour can be overridden for foreign keys named differently. Convention over configuration.

Setup

Install foreign key migrations like any other plugin, it requires the redhillonrails_core plugin though.

./script/plugin install git://github.com/devwout/redhillonrails_core.git
./script/plugin install git://github.com/devwout/foreign_key_migrations.git

Create initial foreign key migration

./script/generate foreign_key_migration
rake db:migrate

When all foreign keys are named by the rails conventions, the migration should run without errors and result in a database with the proper foreign key constraints. Using sequel pro, it is easy to verify the presence of foreign key constraints: there should be an arrow next to each cell that refers to a related row.

Foreign keys named differently from the table they refer to may yield an error similar to this:

Mysql::Error: Can't create table './foreign_key_migrations_example/#sql-2ea7_163.frm' (errno: 150):
  ALTER TABLE comments ADD FOREIGN KEY (author_id) REFERENCES authors (id)

The error can be resolved by editing the generated migration, pointing it to the right table:

class CreateForeignKeys < ActiveRecord::Migration
  def self.up
    # next line was modified: the "authors" table does not exist
    add_foreign_key "comments", ["author_id"], "people", [:id]
    add_foreign_key "users", ["person_id"], "people", [:id]
  end
end

Disable foreign keys on older migrations

Now all foreign key constraints are created for migrations older then the foreign key migration. When the database is cleared and db:migrate is run again, the foreign key migration plugin will add foreign keys twice: once during the old migrations and once with the foreign key migration. To avoid that, we need to disable the plugin for all migrations older then the foreign key migration. Create a new file in config/initializers and add the following. The long number is the prefix of the foreign key migration itself.

class ActiveRecord::Base
  def self.foreign_key_migrations_enabled
    ActiveRecord::Migrator.current_version >= 20091220125415
  end
end

Test

Now would be a good time to run the test suite of the application and see if everything still passes. The redhill plugin overrides the schema_dumper, so the foreign key constraints are cloned to the test database when using ruby schema format, which is the default.

Migrate existing deployments

The addition of foreign key constraints to a database may yield errors: some of the constraints may already be violated. Therefore, it is a good idea to clone the production database on a test machine first and run the migration there. When errors are found, they can be fixed using SQL. Those SQL statements can then be applied to the production database, where after cap deploy:migrations should run without errors.

Forget about foreign key constraints

From now on, it is safe to forget about foreign key constraints during application development. When adding or modifying migrations, the plugin will add the foreign key constraints silently… until an error occurs.

Error in migration

Mysql::Error: Can't create table './foreign_key_migrations_example/posts.frm' (errno: 150):
  CREATE TABLE `posts` (
    `id` int(11) DEFAULT NULL auto_increment PRIMARY KEY,
    `body` text,
    `author_id` int(11),
    `created_at` datetime,
    `updated_at` datetime,
    FOREIGN KEY (author_id) REFERENCES authors (id))

This is a typical error that occurs when a new migration is added with nonstandard foreign key. It can be resolved by explicitly adding the referenced table to the migration.

create_table :posts do |t|
  t.text :body
  t.belongs_to :author, :references => :people
  t.timestamps
end

Error in application

ActiveRecord::StatementInvalid: Mysql::Error: Cannot delete or update a parent row:
a foreign key constraint fails
  (`foreign_key_migrations_example/posts`, CONSTRAINT `posts_ibfk_1`
   FOREIGN KEY (`author_id`) REFERENCES `people` (`id`)):
  DELETE FROM `people` WHERE `id` = 1

Here, the database is acting as a safety net. We just tried to delete a person, which still has an associated post. If the database would have let this happen, there would now be a post with nonexistent author. The solution is simple: when deleting a user, his posts should be deleted or orphaned. This behaviour can be specified in rails, something we should have done in the first place.

class Person < ActiveRecord::Base
  has_many :posts, :foreign_key => :author_id, :dependent => :destroy # or :nullify
end

Conclusion

Adding foreign key constraints to a rails application is quite easy using the foreign key migrations plugin. Once installed, it will mostly just work and provide an extra layer of protection for the data.

Ewout blogt.