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 

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.

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. :)