14

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?

Earlz
  • 62,085
  • 98
  • 303
  • 499

4 Answers4

13

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?

Community
  • 1
  • 1
mikewilliamson
  • 24,303
  • 17
  • 59
  • 90
  • Well, my goal is a new project, and no other app will be accessing it.. so maybe I don't need it? It feels weird though lol – Earlz Apr 03 '10 at 23:19
  • 1
    It does feel weird. Eventually you see the sense of it though. To address your speed concern below, you should make a point of using the add_index command in your migrations. An fk adds an index to the database automatically. In Rails you just add them yourself as needed... If you remember :) – mikewilliamson Apr 03 '10 at 23:22
  • FYI fk = foreign key, int = integrity – Ameen Oct 09 '13 at 08:19
  • Update: Best practice as of Rails 4.2+ is to now use foreign key protections at the database level and not rely simply on validations and integrity checks at the model level. See: https://thoughtbot.com/blog/referential-integrity-with-foreign-keys – Aaron Gray Nov 29 '22 at 16:07
7

Check this out: http://github.com/matthuhiggins/foreigner

But first make sure you really need them (e.g. referential integrity is something that theoretically shouldn't break as long as your code is OK, and you know about :dependent => :destroy and the difference between user.delete and user.destroy).

glebm
  • 20,282
  • 8
  • 51
  • 67
  • Well, I'm thinking not only about referential integrity, but also on the speed benefits of foreign keys whenever you join 2 key'd tables. – Earlz Apr 03 '10 at 23:13
  • 1
    @Earlz, I know this is a big can of worms in the rails community but if you're using mysql, it seems like a no-brainer. This plugin works awesomely. `FK in Rails == HAPPY` :) – maček Apr 04 '10 at 07:11
  • 2
    BTW, the Rails stand on fks doesn't affect the speed of joins, you can still define an index on any field. – sahglie Nov 03 '10 at 22:17
1

There are a number of plugins available (search google) for Rails that will create foreign keys for you when you use a special symbol in your migrations (foreign_key_migrations is one from the Advanced Rails Recipes book). Just be aware that Rails does not play well with this concept especially when you are trying to delete objects (as mentioned by glebm).

simianarmy
  • 1,485
  • 10
  • 13
0

I just came across this post. Maybe someone will find it useful. That's how the constraints are created:

http://guides.rubyonrails.org/migrations.html#using-reversible

class ExampleMigration < ActiveRecord::Migration
def change
create_table :products do |t|
  t.references :category
end

reversible do |dir|
  dir.up do
    #add a foreign key
    execute <<-SQL
      ALTER TABLE products
        ADD CONSTRAINT fk_products_categories
        FOREIGN KEY (category_id)
        REFERENCES categories(id)
    SQL
  end
  dir.down do
    execute <<-SQL
      ALTER TABLE products
        DROP FOREIGN KEY fk_products_categories
    SQL
  end
end

add_column :users, :home_page_url, :string
rename_column :users, :email, :email_address
end
Extrapolator
  • 774
  • 1
  • 8
  • 13