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.

SchemaPlus - new and better automatic_foreign_key¶ ↑

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.

Installation¶ ↑

gem install automatic_foreign_key
rails generate automatic_foreign_key:install

Rails 3.1 compatibility¶ ↑

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"

Rails 3.0 compatibility¶ ↑

Fully compatible with Rails 3.

Rails 2.x compatibility¶ ↑

Only generator is not compatible

Usage¶ ↑

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

Auto Indices¶ ↑

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

NOTE¶ ↑

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.

Column Indices¶ ↑

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

Configuration¶ ↑

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

Dependencies¶ ↑

• RedHill on Rails Core (redhillonrails_core).

Support¶ ↑

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

NOTE¶ ↑

• Former name was foreign_key_migrations by harukizaemon.

License¶ ↑

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