Using Active Record Associations Without Foreign Keys

rubyonrails-talk
1

Hello,

Issue: A couple of models have a belongs_to association to another model with a huge table. We need to remove the foreign key constraints from MySQL, due a technical limitation of MySQL & schema migrations.

What’s the best way to remove those MySQL foreign keys, generated for the Active Record associations, without affecting existing code that depends on the associations?

Can I just drop the constraints from MySQL and keep the code as is?

MySQL limitations context: The reason is that our MySQL database uses asynchronous replication, and the table we want to migrate is large enough that performing schema migrations with ALTER on it stalls MySQL replication. So we’re using a tool called pt-online-schema-change to perform migrations without stalling replication. However, this tool can only perform “seamless” migrations if there are no foreign keys referring to the table being modified.

2

If you remove the foreign key in the database directly, there is nothing in the Rails code that will care per se. There may be validations, but those don’t rely on the foreign key being enforced by the database. For a long, long time, Rails didn’t require true foreign keys at all, yet it properly found child objects based on the convention-over-configuration key naming scheme.

Walter

1 Like
3

Thanks for the reply Walter,

Great, I’m actually hoping for rails to keep doing its validations. What if I want to create new Rails associations without foreign keys? That would be ideal. Because I don’t want the MySQL schema and the Rails schema to drift apart.

Regards, Mazen

4

Well, you’ll be missing on the absolute lock that true foreign keys give you (you can’t delete the parent if any children remain), so you’d have to recreate that in Ruby. Depending on the other aspects of that constraint, you might be missing a uniqueness guarantee, not sure about that. Rails is very happy even in 2021 to run entirely without database foreign keys. For a long time, MySQL didn’t have them as a feature, and Rails was born around that same time, so it worked around the lack. Rails originally only worked with MySQL. Basecamp still uses it exclusively.

Walter

1 Like
5

That’s great, now that I think about it, we never delete data.

Is there a way to instruct Active Record to not create foreign keys when I define a belongs_to association?

Thanks again!

6

Sure. Don’t use the belongs_to macro in your migration. Just do this:

def change
  add_column :table_name, :foo_id, :integer
  add_index :table_name, :foo_id
end

That will get you a proper foreign key shaped table without the database constraint. You’ll have an index, so it will be fast on reads, which is something else you get from that macro, but you won’t have the constraint to mess with you.

Walter