How do I add an UPDATE CASCADE to a Rails-Postgres migration?

Question

I need to write a Rails migration that will update the uuid of a specific object and then CASCADE through all the rows storing that id as a foreign key, like so:

alter table projects add constraint fk_league
foreign key (user_id) references users(id) on update cascade

Unfortunately Rails appears to auto-generate the constraint:

fk_rails_e4348431a9

How would I write the above sql to handle this?

Answer 1

Presumably you have a t.references or t.belongs_to somewhere in your migrations:

t.references :user, :foreign_key => true

The t.references is just an add_reference call in disguise. The add_reference documentation doesn't say anything useful about the value for the :foreign_key option but the code does:

foreign_key_options = options.delete(:foreign_key)
#...
if foreign_key_options
  to_table = Base.pluralize_table_names ? ref_name.to_s.pluralize : ref_name
  add_foreign_key(table_name, to_table, foreign_key_options.is_a?(Hash) ? foreign_key_options : {})
end

So when you specify the :foreign_key option, you can hand it a Hash of options for the underlying add_foreign_key call and that has an :on_update option:

:on_update
Action that happens ON UPDATE. Valid values are :nullify, :cascade: [sic] and :restrict

You'd want to replace your original t.references call with something more like this:

t.references :user, :foreign_key => { :on_update => :cascade }

---

If you already have everything set up on production and you need to alter the FK constraint, then I think you'd need to fix things up by hand:

1. Add a migration to drop the original constraint and add the updated one:

   def up
     connection.execute(%q{
       alter table projects
       drop constraint fk_rails_e4348431a9
     })
     connection.execute(%q{
       alter table projects
       add constraint fk_rails_e4348431a9
       foreign key (user_id)
       references users(id)
       on update cascade
     })
   end
   def down
     # The opposite of the above...
   end
   

   You probably don't need to keep the constraint name that Rails chose but you might as well.

2. Manually edit your db/schema.rb to add the above :foreign_key => { :on_update => :cascade } to the appropriate t.references call.