Ruby-on-rails – Foreign keys with Rails’ ActiveRecord::Migration

foreign-keysmigrationpostgresqlruby-on-rails

I'm new to Ruby on Rails (I know Ruby just decently though) and looking at the Migration tools, it sounds really awesome. Database schemas can finally (easily) go in source control.

Now my problem with it. When using Postgres as the database, it does not setup foreign keys. I would like the benefits of foreign keys in my schema such as referential integrity. So how do I apply foreign keys with Migrations?

Best Answer

Rails philosophy is that integrity checks is business logic that belongs in the model. Thats why you are seeing what you are seeing in the DB; whatever_id is just an int and not a "real" fk in sight. Its not a mistake, its by design and its a little freaky at first. Generally the only reason that drives people to work with fks in the DB level is when the DB is accessed by more than one app or its a legacy system. There is plenty of discussion and some great links here: Why do Rails migrations define foreign keys in the application but not in the database?

Related Solutions

Mysql – How to see all foreign keys to a table or column

For a Table:

SELECT 
  TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_SCHEMA = '<database>' AND
  REFERENCED_TABLE_NAME = '<table>';

For a Column:

SELECT 
  TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_SCHEMA = '<database>' AND
  REFERENCED_TABLE_NAME = '<table>' AND
  REFERENCED_COLUMN_NAME = '<column>';

Basically, we changed REFERENCED_TABLE_NAME with REFERENCED_COLUMN_NAME in the where clause.

Ruby-on-rails – How to write a migration to rename an ActiveRecord model and its table in Rails

Here's an example:

class RenameOldTableToNewTable < ActiveRecord::Migration
  def self.up
    rename_table :old_table_name, :new_table_name
  end

  def self.down
    rename_table :new_table_name, :old_table_name
  end
end

I had to go and rename the model declaration file manually.

Edit:

In Rails 3.1 & 4, ActiveRecord::Migration::CommandRecorder knows how to reverse rename_table migrations, so you can do this:

class RenameOldTableToNewTable < ActiveRecord::Migration
  def change
    rename_table :old_table_name, :new_table_name
  end 
end

(You still have to go through and manually rename your files.)

Best Answer

Related Solutions

Mysql – How to see all foreign keys to a table or column

Ruby-on-rails – How to write a migration to rename an ActiveRecord model and its table in Rails

Related Topic