ActiveRecord refererential integrity is broken. Let's fix it!

Posted by Jon
on Tuesday, August 18

ActiveRecord supports cascading deletes to preserve referential integrity:

1
2
3
class User
  has_many :posts, :dependent => :destroy
end

But you really only want cascading deletes about half the time. The other half, you want to actually restrict deletion of a record with dependencies. ActiveRecord doesn’t support this.

Think of an e-commerce system where a user has many orders. Once an order has gone through, you shouldn’t be able to delete the user who placed the order. You need a record of the order and the user who placed it.

Or even more obvious, think of a lookup table. An Order might have several of these dependencies; OrderStatus, Currency, DiscountLevel, etc. In all of these cases, you want ON DELETE restrict, not ON DELETE cascade. But Rails doesn’t support this. That’s dumb.

If you agree, head on over to the Rails UserVoice site and make your opinion known! There is a ticket for this already. Vote it up if you think Rails should implement this.

The solution to the problem is really pretty simple. ActiveRecord just needs something like this:

1
2
3
class User
  has_many :posts, :dependent => :restrict
end

In this case, if you try to destroy a user that has one or more posts, Rails should complain. You’ve told the app: “Don’t let me delete users who have posts!” The easiest way to do this is to have Rails throw an exception, and have your controller capture the exception and print a flash message. Other approaches could work too.

So why is this important?

1. It’s common. Every project should maintain referential integrity in some way, and :dependent => :destroy isn’t always appropriate. Who wants to do a cascading delete from roles to users, or manufacturers to products, or order_statuses to orders? I don’t think I’ve ever worked on a project where cascading deletes were always appropriate. Any lookup table, at minimum, needs this feature. (I personally prefer to maintain referential integrity with foreign keys, but even still, I’d love to have an application-level check first, which would be easier to rescue. And some projects don’t use foreign keys.)

2. It fits with the Rails philosophy. Rails says “Let your application handle referential integrity, not the database”. But without :dependent => :restrict, one of the most important pieces of referential integrity is missing.

3. It’s easy. 9 lines of code to add this to has_many. Check out this gist: http://gist.github.com/170059.

Someone wrote a plugin for this, but it has the distinct disadvantage of not working anymore. This should really be a core feature anyway, at least as long as :dependent => :destroy is a core feature.

The UserVoice suggestion for this is at http://rails.uservoice.com/pages/10012-rails/suggestions/103508-support-dependent-restrict-and-dependent-nullify.

Comments

Leave a response

  1. Pratik NaikAugust 18, 2009 @ 08:56 PM

    Stuff like this is a better match for the core mailing list. Uservoice for patches/small features is pretty much a waste of time. Mailing list + Patch will get things moving fast. I like the feature, I’d be happy to commit the patch if there’s no major objection.

    Thanks!

  2. Hongli LaiAugust 19, 2009 @ 04:52 AM

    I’ve been using the redhillonrails_core plugin for years now, and it works great:

    create_table :foo do |t|
      t.integer :user_id
      t.foreign_key :user_id, :users, :id, :on_delete => :restrict
    end
    

    And I agree, the feature should be in core.

  3. Mike GunderloyAugust 19, 2009 @ 08:27 AM

    I took the liberty of turning this into a proper patch with support for has_one, docs + tests: https://rails.lighthouseapp.com/projects/8994-ruby-on-rails/tickets/3075-add-dependent-restricted-to-has_one-and-has_many

  4. Jon DahlAugust 19, 2009 @ 09:38 AM

    Thanks everyone.

    Hongli, I use redhillonrails_core most of the time, and I’m a big believer in foreign key integrity. Sadly, I’m working on a small project now that doesn’t use them, so I’m stuck handling this at the app level.

    Mike, big thanks for turning this into a proper patch! I’ll leave a quick comment or two on the patch over at Lighthouse.

    Pratik, thanks for the tip. I’ll post to the core mailing list.

  5. Witold RugowskiAugust 20, 2009 @ 07:40 AM

    @Jon I want to ask question about Your older post – http://railspikes.com/2009/3/10/slow-tests-are-a-bug which has probably comments disabled, so I do it here.

    I’m looking for a hint how to disable loading fixtures and use real database data to run tests, could You share some details how You do use test database?

  6. NoahAugust 20, 2009 @ 10:12 AM

    Depending on the database being used, there are also the options ‘no action’ and ‘set default’.

    From the ANSI SQL standard:
    1. <referential> ::=
    2. CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION

    I believe rails already implements set null.

  7. Jon DahlAugust 21, 2009 @ 03:21 PM

    Noah: “no action” is kind of like “restrict”, but waits until the end of the transaction to check whether integrity is broken. It would be a valuable addition to Rails (:dependent => :restrict_at_end maybe?). But it would be a bit more difficult to implement. Ideas?

    Not sure whether “set default” would be useful.

    Witold: I’ve got a blog post brewing on the subject. I’ll give you an email.