How do others set up foreign key relationships in migrations

Question

Hi,

I'm developing some things in Ruby on Rails, and I've currently got several models with relationships between them. Now, the models specify the relations, so I know that RoR will enforce integrity, but what about at the DB level ?

Do other people set up foreign key relationships in DB tables ? and if so, how ?, there doesn't seem to be any way to setup/destroy a db relationship in a migration (maybe using raw SQL)

Thanks

Paul.

Answer 1

There isn't a way to do it from the migration short of using SQL, which means that:

1. It's DB-specific
2. You have to use SQL

The first isn't really that big a deal (how often do you switch databases on a project anyway?), and the second is simply a fact of life. So, use them if you want.

Answer 2

Incidentally these things should always be set up at the database level. There are other ways to access and change data in the database besides the application. You should never set these types of rules in the application unless you want useless data. All things that touch on data integrity must be at the database level even if you have to (GASP) use SQL.

Answer 3

Here's a guide on how to do it: http://seb.box.re/2006/7/29/foreign-key-and-rails-migration

There is also a plugin for this here: http://github.com/harukizaemon/foreign_key_migrations/tree/master

However, Rails does not easily support foreign keys in migrations for a reason. Basically they're not really necessary when using ActiveRecord.

Here's a good explanation of why they are not necessary and their usage is discouraged in rails: http://www.motionstandingstill.com/i-dont-use-foreign-key-constraints-with-rails/2008-10-17/

Opinions differ on this subject. There's a good discussion here: http://forum.softiesonrails.com/forums/3/topics/138