Wednesday, September 2, 2009

Foreigner comes full circle

After actively developing multiple Rails projects over the last three years, I've learned when to not fight the framework, and I've also learned when it's appropriate to roll up your sleeves and write some Ruby code. I've stopped using composite primary keys, I've stopped attempting to namespace models, I always use the default "id" for a primary key, the list goes on.

One thing my mind has not changed on is using foreign keys. I use them, and I've finally completed Foreigner, a foreign key migration library that meets my initial requirements:
  • Do not change existing behavior of the application
  • Perform a no-op when the adapter does not support foreign keys
  • Follow similar API as 'add_index'
  • Dump foreign keys into schema.rb

Do not change existing behavior of the application

I found numerous existing foreign key plugins that attempted to automagically add foreign keys. For example, automatically adding foreign keys based on column names, or automatically adding associations to active record models base on foreign keys in the database. I believe both of these are mistakes. Automatically adding foreign keys changes existing migrations, and always seems to break things. Automatically creating active record associations puts too much guesswork into play. There's a difference between using foreign keys to enforce integrity (good), and using foreign keys to change the behavior of the application (bad).

Lastly, I thought long and hard about whether calls to t.references should automatically add a foreign key. I finally resolved on not doing this, and compromised by adding a new :foreign_key option. For example:
change_table :comments do |t|
t.references :author, :foreign_key => true

Perform a no-op when the adapter does not support foreign keys

While this does not help me, it is required to support adapters such as sqlite3. The AbstractAdapter implements all necessary methods with a no-op. With Foreigner installed, you can develop with sqlite3 and run production with MySql.

Follow similar API as 'add_index'

Adding foreign keys isn't much different than adding indexes, and everyone is familiar with using add_index. You add_foreign_key, remove_foreign_key and can even use change_table.

Dump foreign keys into schema.rb

The final feature I added to Foreigner is dumping foreign keys to schema.rb, so that you can avoid using the SQL structure. Similar to how indexes are added to schema.rb, Foreigner inspects the database and determines the foreign keys.

In conclusion...

There's been a lot of discussion in the past about whether or not you should be using foreign keys. This shows to me is that there is a large group of people who probably want this available out of the box with Rails. The library was designed to not affect those who don't want them; the primary purpose in doing so was to show that everyone can have it their own way. My next step is turning this into a gem.

Get the source:


Carl said...

Now this is change I can believe in! I sort of wondered why something like this wasn't included by default, since for some uses, foreign key integrity being handled by the database makes sense. They were generally designed to do it well. I also like your syntax for adding the keys, very logical.

T├Ánis said...

the remove_foreign_key does not work with PostgreSQL. It tries to execute
ALTER TABLE "app_user" DROP FOREIGN KEY "app_user_role_id_fk"
but the PostgreSQL ALTER TABLE command has "DROP CONSTRAINT" instead of "DROP FOREIGN KEY"

I tried it with PostgreSQL 8.3 and the docs of 8.4 ( ) don't have the syntax used by the gem either.

Other than this, the gem is very nice. Not having to write alter table statements in the migrations is great!

Matthew Higgins said...

Hey Tonis, the issue you mentioned is fixed and I'll re-release the gem soon.

Blythe said...

Cool. Great to see a new plugin! I get on a soap box about referential integrity at the database level. I was using
redhills foreign_key_migrations since rails 1 but I did have some trouble tracking down newer versions I did have some trouble finding newer versions of it on github and gemcutter.
I wondered what are the other differences and advantages between it and foreigner. Seems like you have all the bases covered with mysql, postgres, t.references from table and schema.rb support.

PT said...

Excellent plug-in. Thanks.

Mark E. said...

I'm trying to get a self-referential key added and having trouble.

I'm trying to create a table like "employees" with an "id" and "parent_id" where the parent is an Employee entry.

I can't figure out how to do this using the "t.references" mechanism. I specify:

create_table :employees do |t|
t.column :name, :string, :limit => 50, :null => false
t.references :employee, :foreign_key => {:column => 'parent_id'}

When I migrate, the following SQL is generated:
CREATE TABLE `employees`
(`id` int(11) DEFAULT NULL auto_increment PRIMARY KEY,
`name` varchar(50) NOT NULL,
`employee_id` int(11),
`created_at` datetime,
`updated_at` datetime,
FOREIGN KEY (`parent_id`) REFERENCES `employees`(id)

(MySQL obviously)

The error message is:
Mysql::Error: Key column 'parent_id' doesn't exist in table. It's correct. It is creating a column named "employee_id" instead of the specified column name.

Am I doing it wrong?

enodo said...

Question: The document says that the syntax is

remove_foreign_key from_table, options

How do you specify which key you want to remove? I tried

remove_foreign_key from_table, to_table

and it seems to work. Is the document wrong or am I misunderstanding something?

Mark E. said...

My previous question about self-referential links using a parent_id column was resolved using a newer version of Foreigner. I didn't test it using the same syntax. I did it this way...

add_foreign_key(:employees, :employees, :column => 'parent_id', :null => true)

Zeke Fast said...

Nice work! Very appreciated!

sheamus said...

Hey love the plugin. Now that that SQLite supports foreign keys, are there plans to update foreigner to generate the constraints?


Martin Payne said...

I think this is great, and I'm trying to use it with Rails 4.1.0.

However, my foreign_key entries aren't being added to schema.rb, though the migrations themselves work fine (so the gem is obviously in place).

This form is logged in the output of db:migrate:-
add_foreign_key(:accounts, :user, name: 'fk_accounts_users')
== 20140513100908 CreateAccounts: migrating ===================================
-- create_table(:accounts)
-> 0.0020s
-- add_foreign_key(:accounts, :user, {:name=>"fk_accounts_users"})
-> 0.0000s
== 20140513100908 CreateAccounts: migrated (0.0020s) ==========================

I've also tried:-
t.references :user, foreign_key: true
...which creates neither an fk nor an index. (Not sure if you create an index, or are relying on one already being there?)

Any thoughts what's going wrong? Am I missing a step? Thanks for any pointers.

PS ditto for sheamus' comment. Any plans to add sqlite support?