Rails and referential integrity

Question

I would like to know if it's possible to write a migration instead of of the following raw SQL statement:

execute <<-SQL
  ALTER TABLE records
    ADD CONSTRAINT fk_records_domains
    FOREIGN KEY (domain_id)
    REFERENCES domains(id) ON DELETE CASCADE
SQL

I would like avoid using sql because i'm getting problems when trying to rollback such migration:

execute <<-SQL
  ALTER TABLE records
    DROP FOREIGN KEY fk_records_categories
SQL





rake db:rollback
==  Integrity: reverting ======================================================
-- execute("      ALTER TABLE records\n        DROP FOREIGN KEY                                      fk_records_categories\n")
rake aborted!
An error has occurred, all later migrations canceled:

Mysql2::Error: Error on rename of './BlackshardDev/records' to './BlackshardDev/#sql2-44cc-16c' (errno: 152):       ALTER TABLE records
    DROP FOREIGN KEY fk_records_categories

I know that activerecord can handle the referential integrity but i would like to be able to manage it also with the backend. Thanks

score 3 · Accepted Answer · answered Feb 27 '12 at 01:20

According to the Rails Migrations guide:

The Active Record way claims that intelligence belongs in your models, not in the database. As such, features such as triggers or foreign key constraints, which push some of that intelligence back into the database, are not heavily used.

…

Although Active Record does not provide any tools for working directly with such features, the execute method can be used to execute arbitrary SQL. You could also use some plugin like foreigner which add foreign key support to Active Record (including support for dumping foreign keys in db/schema.rb).

Foreigner's add_foreign_key and remove_foreign_key probably do what you're asking for, but I don't have any direct experience with it.

score 2 · Answer 2 · answered Feb 27 '12 at 21:14

For anyone that is lookig for a solution example here it is, migration and it's rollback working:

  def up
    change_table :records do |t|
        t.foreign_key :domains, :dependent => :delete
    end
    change_table :cryptokeys do |t|
        t.foreign_key :domains, :dependent => :delete
    end
    change_table :domainmetadata do |t|
        t.foreign_key :domains, :dependent => :delete
    end

  end

  def down
    change_table :records do |t|
        t.remove_foreign_key :domains
    end
    change_table :cryptokeys do |t|
        t.remove_foreign_key :domains
    end
    change_table :domainmetadata do |t|
        t.remove_foreign_key :domains
    end
  end

Rails and referential integrity

2 Answers2

Linked