I created some migration helpers to allow me to add foreign keys (like in the example in AWDWR) and they look like:

module MigrationHelpers

def foreign_key(from_table,from_column,to_table)
   constraint_name = "fk_#{from_table}_#{from_column}" 
   execute %{alter table #{from_table}
        add constraint #{constraint_name}
        foreign key (#{from_column})
            references #{to_table}(id)}
end

def remove_foreign_key(from_table,from_column)
  constraint_name = "fk_#{from_table}_#{from_column}" 
  execute %{alter table #{from_table}
        drop foreign key #{constraint_name}}
end

end

And when I try calling them in migrations, I get:

rake aborted! Mysql::Error: Can’t create table ’./freightzone_development/#sql-134d_45.frm’ (errno: 150): alter table users add constraint fk_users_company_id foreign key (company_id) references company(id)

Has anyone run into this before? Is there a better way to add foreign keys after a table has already been created?

ok…so I did try and figure this out for a few hours before I posted this..honest.

I ended up stumbling onto the fix while looking at the differences between a working migration that used these methods and the broken migration. The problem was that I hadn’t pluralized the table for the foreign key and so it was looking for ‘companies’ instead of ‘company’.

If you get a MySql 150 error, check to see if you have the table names right.

Just out of curiosity, why do you need to add key contraints to the table?

Coming over from .Net development on SQL Server, I’m used to using foreign key constraints for referential integrity. As I explore Rails, I started down the path of trying to do that in Rails. I figured once I got this working I could add on the option of “ON UPDATE” and “ON DELETE”.

Is that not the norm in Rails? Does everyone really rely on Active Record associations alone?

Since I switched to Rails, I’ve never had to worry about any constraints. It feels really weird at first, but it’s quite liberating actually.

Rails provides callback hooks you can implement in your model to perform actions that ON UPDATE and ON DELETE used to do (see the docs on after_save and after_destroy, for example). I much prefer to keep all my logic in Ruby, instead of context-switching between the application code and SQL Server trigger code.

Now, one place where constraints might be necessary, is if your database is going to be accessed by a non-Rails application. If the database will be accessed by other applications, then you might want to centralize such rules in SQL Server to defend against bad-behaving apps.

But if the db is just to support your Rails application, then I think there’s no reason to write most triggers or key constraints in the db.

I don’t agree with that, I think it’s a good idea to create foreign keys. Almost every key you are going to create should be an index anyway, so with a foreign key you get index as well as the added bonus of referential integrity. (talking about MySQL)

If you don’t do foreign keys, do you do indexes?

-James

I’m doing both in my migration…

require ‘migrations_helpers’

class AddRelationshipUsersCompanies < ActiveRecord::Migration extend MigrationHelpers

def self.up
  #foreign_key(from_table, from_column, to_table)
  add_index :users, :company_id
  foreign_key(:users, :company_id, :companies)
end

def self.down
  #remove foreign key
  remove_foreign_key :users, :company_id
  remove_index :users, :company_id
end

end

I agree with James that it’s still a good idea to do it, even if it’s just a Rails app. Sort of like a last wall of protection against violation of data integrity. Just my 2 cents.

“Am I right or wrong? Join the discussion.” – What a carrot! Nice one Jeff.

Use DB constraints!!! It’s a no-brainer. It’s easy and it’ll keep your DB more robust. Just because ActiveRecord rocks doesn’t mean you have to be lazy. Infact it’s so easy it’s not a matter of laziness. Just do it! I use foreign_key_migrations (http://www.redhillconsulting.com.au/rails_plugins.html) – It’s well maintained and hasn’t failed me yet. Seamless foreign keys and indexes in you migrations. (Disclaimer: I don’t know them!).

ps Thanks for the Softies blog. I’ve been there from the start as a .NET to Rails guy myself.

I went through a phase where I stopped worrying about foreign keys. Eventually though I went back to implementing them on the DB because my DB (Firebird in this case) was dramatically faster with foreign keys. But that may be because Firebird adds indexes for all foreign keys.

But also, a well structured DB with foreign keys will prevent mistakes in the code that could have ill effects.

I admit that I haven’t been using foreign key constraints since I started using Rails, but as a number of people in your comment thread have noted, there are some troubling problems with ActiveRecord validations.

I did work on one project that used DB constraints with Postgres and Rails. It had some annoying aspects to it, because the model would get changed but not the database constraints. This led to some problems, like records that couldn’t be deleted because doing so would violate a constraint. So if you do use ‘em, make sure you keep your DB in synch with your Rails validations.

I use constraints because in most databases I know they make for faster queries but the biggest reason is because I can’t say Rails will be the only way I interact with the database, nor that I’ll be the only person having to deal with it. Most other frameworks and tools do take foreign keys into account and so it seems like a missed oppurtunity to ignore a simple feature. It’s a common courtesy to the next developer that has to deal with what I’ve created.

The other day someone wanted to see a Visio diagram of the schema and without any FKs Visio wouldn’t have been able to model it correctly. Another example is recently I’ve had to work with a new ERP system and for the missing holes in the API we have to connect directly to the database and without those FKs things would have been a pain in the arse. I view foreign keys as a form of self-documenting code.

They almost always make your database faster, they’re cheap, they’re self-documenting, almost every other tool and framework uses them to good effect so I can’t see any good reason to not use them.

I think that Database constraints are good, really, and faster than ActiveRecords, of course, but you have to think per project if you need to worry about them.

In other hand, if you use DB constraints, they will protect your app from your self and they keep your hands always clean :D

Database constraints are good.

It’s nice to have validations occur at the application level, and it’s nice to save a round trip to the DB, however.. IF you ever port your application to the next latest and greatest framework, it’s nice to have those constraints in place.

I’m surprised nobody has mentioned any problems with tests. It was our experience that the constraints on the DB put up a good fight when trying to tear down our fixtures after each test. We tried a couple approaches one of which was to order the fixtures just right, but that took too much maintenance so we inevitably ended up creating a rake task to remove all constraints from out test database. Has anyone else experienced problems with tearing down fixtures while there are constraints in the DB?