Why do Rails migrations define foreign keys in the application but not in the database?

Question

If I define a Customer and Order model in which a Customer "has many" Orders and the Order "belongs to" the Customer, in Rails we talk about Order having a foreign key to the Customer through customer_id but we don't mean that this is enforced in the database.

Because Rails does not define this as a database-level constraint, there is a risk of your data's integrity being violated, perhaps outside the application (or inside if you receive simultaneous requests?), unless you enforce the constraint in the database manually.

Why does Rails not define the foreign key at the database level or is there a way to get Rails to do this?

class Customer < ActiveRecord::Base
  has_many :orders
end

class Order < ActiveRecord::Base
    belongs_to :customer
end

ActiveRecord::Schema.define(:version => 1) do

  create_table "customers", :force => true do |t|
    t.string   "name"
  end

  create_table "orders", :force => true do |t|
    t.string   "item_name"
    t.integer  "customer_id"
  end

end

Tirrell

Answer 1

Rails holds some conventions that enforcement of data integrity should be done in the application, not in the database.

For example, Rails even supports some database designs that cannot use foreign keys, such as Polymorphic Associations.

Basically, Rails conventions have treated the database as a static data storage device, not an active RDBMS. Rails 2.0 is finally supporting some more realistic features of SQL databases. It's no surprise that the result will be that developing with Rails will become more complex than it was in version 1.0.

Hispaniola

Which features of Rails 2.0 were you referring to? – Tirrell

One feature I'm thinking of is support for primary key columns that aren't named "id". I'm not a regular Rails developer, so I'm not tracking all of their new features. – Hispaniola

Ok. Overall, to correct for this design in Rails, would you recommend defining database-level foreign keys to reinforce the application-level foreign key? Or is this a judgment call that really depends on the application? I was under the impression that it is always recommended to define a database-level foreign key in such situations as in my example above. – Tirrell

I agree it's a best practice to define constraints in the database. That's the only way you can be assured data integrity is enforced consistently. If you do it in the app, then anyone who accesses the database without going through your app won't be subject to the same constraints, and they will mess up the data. However, I have no idea how to persuade Rails migrations to implement real database constraints. This is one reason I don't use Rails. – Hispaniola

Sure. What I was referring to, though, was the option of defining your constraints manually in the database, after you run your Rails migrations. The unfortunate consequence is that not all of your DDL is being applied in one place. – Tirrell

Right; that may be what you have to do. You get into some catch-22 situations, thought, if you try to enforce referential integrity with both database constraints and application logic. Cascading updates are impossible, for example. – Hispaniola

Couldn't the before_update method be used for cascading updates as Mike Gunderloy explains at this link: workingwithrails.com/forums/4-ask-a-rails-expert/topics/… Or would this method require the absence of database-level constraints? before_update :update_employees def update_employees employees.each do { |e| e.update_attribute(:date_valid => self.date_valid) } end – Tirrell

I mean cascading updates of the primary key (not commonly needed when you use a surrogate key). If you have foreign key constraints in child tables referencing that primary key, how do you change the primary key value and references to it in child tables, using application-driven updates? You can't change the child first, nor can you change the parent first. The only answer is to define foreign keys with ON UPDATE CASCADE (if that's what you want) and then execute the update against the parent only. If Rails thinks it can change both, this breaks. – Hispaniola

"You can't change the child first, nor can you change the parent first." You can't change the child first because of the database-level foreign key towards the parent primary key. But why can you not change the parent first? (Apologies if I've exhausted your patience). – Tirrell

Try it. You get an error when you change a parent key value, if child records reference it (unless you declared the child's foreign key with ON UPDATE CASCADE). Even if you use the MySQL multi-table UPDATE syntax and try to change both in one statement, you get an error. – Hispaniola

guys, awesome question, awesome answer, awesome discussion. Thank you VERY much! – Sadoff

Answer 2

After having worked with this issue for a while, I don't think it's part of the core Rails philosophy that foreign keys should not be enforced by the database.

The application level validations and checks are there to provide easy, quick, human readable (think error messages) checks that work in 99.99% of the time. If your application requires more than that, you should use database level constraints.

I think this "philosophy" evolved because of the original testing frameworks used: foreign keys just proved to be a gigantic hassle when using fixtures. It's like when a "bug" becomes a "feature" because no one fixes it. (If I'm misremembering history, someone correct me.)

At a minimum, there is a growing movement within the Rails community to enforce integrity with the database. Check out this blog post from last month. She even links to some plugins that help provide support for handling errors (and another blog post that links to more plugins). Do a few more Google searches; I've seen other plugins that add support to migrations to create foreign keys, too.

Now, what is part of the core Rails philosophy is: Don't worry about stuff unless you actually need to. For a lot of web applications, it's probably ok if a small (probably tiny) percentage of records contain invalid data. Pages that might be affected might only very rarely be viewed, or the error can be handled gracefully already. Or maybe it's cheaper (as in, cold hard cash) to handle problems by hand for the next 6 months as the application grows than it is to spend the development resources planning for every contingency now. Basically, if your use cases don't make it seem all important, and it can really only be caused by a race condition that may happen 1/10000000 requests... well, is it worth it?

So my prediction is that tools will spring up to handle the whole situation better by default, and eventually these will get merged into Rails 3. In the meantime, if your app really needs it, add them. It'll cause a slight testing headache, but nothing you can't get through with mocks and stubs. And if your app doesn't really need it... well you're all good already. :)

Garbanzo

This seems realistic, reasonable and practical. Great blog post ("she" not "he" btw). Thanks for the link. I'm going to try out that plugin. – Tirrell

Answer 3

After many decades in the industry, I firmly believe that a good database design will save an application from many problems, especially when it undergoes enhancements. If it is known that a particular constraint will keep the database integrity even after a programming slip (I am sure that I am not the only one to do that), then it by all means should be applied to the database if possible. So I would encourage people to use foreign keys when ever possible. I would also consider using tests to provide data integrity. For we all know about murphy's law.

Obelisk

thanks for sharing your experience, much appreciated, sir. – Toinette

Answer 4

One mistake a lot of folks make is confusing migrations with the model. The migrations simply modify the database, and have nothing to do with the models you have defined. As a result of this confusion, a lot of foreign key plugins try combine the model with the migrations and do too much magical stuff.

For migrations, I'd use http://github.com/matthuhiggins/foreigner/tree/master. You shouldn't need to change your models to get foreign keys to work with Rails.

Breastbone

Answer 5

It does create a customer_id column (obviously). For the most part, though, Rails believes in enforcing constraints and validations at the application level, rather than the database level; that's why columns, by default, in Rails may contain NULL values, even if you have validates_presence_of or something like that. The view of Rails' developers is that such constraints should be handled by the application, not the database.

Blodget

"The view of Rails' developers is that such constraints should be handled by the application, not the database"But why is that their view? Isn't this what the database does best? – Tirrell

I imagine a lot of it has to do with portability. Rails supports a number of DB backends, not all of which necessarily support the same feature set. Rails tries to abstract away the DB, though, and thus chooses in many cases to support only the lowest common denominator. I suppose there might be other reasons, too; the Rails core developers are avid bloggers, so I suppose you might find a clearer answer if you read through their blogs. – Blodget

Referential constraints can be defined across all major databases. Your explanation does not make sense. – Tirrell

I've grown to not use FK constraints. They cause far more pain than they alleviate in most cases (there are exceptions). Also, once you have to scale (i.e. shard or use something like big table) then FK constraints are out anyway. – Lylalyle

@eggdrop: SQLite does not support foreign key constraints. MySQL's default storage engine (MyISAM) does not support foreign key constraints. These two SQL databases are quite popular, especially in open-source deployments. – Hispaniola

@Bill Karwin - Apologies to mipadi. I stand corrected. In that light, mipadi's explanation makes sense. – Tirrell

No one uses SQLite for production web apps. And MyISAM is way underused compared to InnoDB, because MyISAM doesn't support transactions either. – Garbanzo

"No one uses SQLite for production web apps." -> justindriscoll.us/2008/03/… In your answer below you say "For a lot of web applications, it's probably ok if a small (probably tiny) percentage of records contain invalid data". By the same token many low traffic web apps (i.e. a large number) would face very few concurrent-write issues from using sqlite in production. – Tirrell

This is an old discussion, but for the record, SQLite added support for foreign keys in version 3.6.19 (2009-10-14), and MySQL changed their default storage engine to InnoDB in version 5.5.5 (2010-07-06). – Hispaniola