Automatic Foreign Key¶ ↑

Automatic Foreign Key is an ActiveRecord extension that automatically generates foreign-key constraints when creating tables. It uses SQL-92 syntax and as such should be compatible with most databases that support foreign-key constraints.

As a bonus, the extension also allows you to create indices on columns via options to the schema column definition statements.

automatic_foreign_key is deprecated and SchemaPlus is its successor. SchemaPlus is based on automatic_foreign_key code but it is more flexible and new features are added only there. API isn’t changed so migration should be very easy. Visit github.com/lomba/schema_plus to read more. Also very cool SchemaValidations and SchemaAssociations gems are built on SchemaPlus.

You can find some details on migration on SchemaPlus Wiki.

Although automatic_foreign_key is deprecated, potential bugs will be fixed at least until the end of 2011.

gem install automatic_foreign_key
rails generate automatic_foreign_key:install

You need at least 1.3.0 version. Also explicit reference to redhillonrails_core 2.0.0.pre must be set.

gem "automatic_foreign_key", "~> 1.3.0"
gem "redhillonrails_core", "~> 2.0.0.pre"

Fully compatible with Rails 3.

Only generator is not compatible

In the simplest case, the plugin assumes that if you have a column named customer_id that you want a foreign-key constraint generated that references the id column in the customers table:

create_table :orders do |t|
  t.column :customer_id, :integer, :null => false
  ...
end

If you have multiple columns referencing a table or for whatever reason, your column name isn’t the same as the referenced table name, you can use the :references option:

create_table :orders do |t|
  t.column :ordered_by_id, :integer, :null => false, :references => :customers
  ...
end

If you have a column with a name ending in _id for which you do not wish a foreign-key to be generated, you can use :references => nil:

create_table :orders do |t|
  t.column :external_id, :integer, :null => false, :references => nil
  ...
end

Sometimes you may (for legacy reasons) need to reference a primary key column that is named something other than id. In this case you can specify the name of the column:

create_table :orders do |t|
  t.column :ordered_by_pk, :integer, :null => false, :references => [:customers, :pk]
  ...
end

You also have the option of specifying what to do on delete/update using :on_delete/:on_update, respectively to one of: :cascade; :restrict; and :set_null:

create_table :orders do |t|
  t.column :customer_id, :integer, :on_delete => :set_null, :on_update => :cascade
  ...
end

If your database supports it (for example PostgreSQL) you can also mark the constraint as deferrable:

create_table :orders do |t|
  t.column :customer_id, :integer, :deferrable => true
  ...
end

By convention, if a column is named parent_id it will be treated as a circular reference to the table in which it is defined.

Sometimes you may (for legacy reasons) need to name your primary key column such that it would be misinterpreted as a foreign-key (say for example if you named the primary key order_id). In this case you can manually create the primary key as follows:

create_table :orders, :id => false do |t|
  ...
  t.primary_key :order_id, :references => nil
end

There is also a generator for creating foreign keys on a database that currently has none:

ruby rails generate automatic_foreign_key:migration

The plugin fully supports and understands the following active-record configuration properties:

• config.active_record.pluralize_table_names

• config.active_record.table_name_prefix

• config.active_record.table_name_suffix

It’s very common to create an index on foreign key. You can instruct AutomaticForeignKey to add an index after adding foreign key.

create_table :orders, :id => false do |t|
  ...
  t.integer :order_id, :index => true
end

If you want to pass some options for index use hash params.

create_table :bills, :id => false do |t|
  ...
  t.integer :order_id, :index => { :unique => true, :name => 'foo_index' }
end

Auto indexing option is useless for MySQL users as their RDBMS adds indices on foreign keys by default. However PostgreSQL users may have fun with that feature.

You can create an index on any column by specifying the :index option.

create_table :users do |t|
  ...
  t.string :role, :index => true
end

If you want to pass some options for rails’ +add_index()+ you can use hash params.

create_table :users do |t|
  ...
  t.string :userid, :index => { :unique => true }
end

You can also define a multi-column index by specifying a :with option listing one or more additional columns to be passed to +add_index()+.

create_table :users do |t|
  t.integer :group_id
  t.integer :member_number, :index => { :with => :group_id, :unique => true }
  t.integer :country_code
  t.string  :area_code
  t.string  :local_phone,   :index => { :with => [:country_code, :area_code], :unique => true }
end

For customization purposes create config/initializers/automatic_foreign_key.rb file:

AutomaticForeignKey.setup do |config|
  config.auto_index = true # create indices on FKs by default
  config.on_update = :cascade # cascade as default on_update action
  config.on_delete = :restrict # restrict as default on_delete action

  config.disable = false # set to true if you want to disable auto foreign keys
end

• RedHill on Rails Core (redhillonrails_core).

Don’t hesitate to ask questions on our mailing list. groups.google.com/group/rails-db

• Former name was foreign_key_migrations by harukizaemon.

This plugin is copyright 2011 by Michał Łomnicki and is released under the MIT license.