MySQL Constraints - Active Record

rubyonrails-talk
1

What is the "proper" way to add constraints using MySQL as the database? I have it set up like this:

  t.integer :product_id, :null => false, :options=> "CONSTRAINT fk_line_item_products REFERENCES products(id)"

This does nothing even if I fill the options with obviously bad syntax or just garbage.

Suggestions?

2

dev wrote:

What is the "proper" way to add constraints using MySQL as the database? I have it set up like this:

  t.integer :product_id, :null => false, :options=> "CONSTRAINT fk_line_item_products REFERENCES products(id)"

This does nothing even if I fill the options with obviously bad syntax or just garbage.

Suggestions?

Rails won't let you do this - it strips the constraint out of the generated sql which i agree is a bit crap. You need to do it directly with a ActiveRecord::Base.connection.execute, or use a plugin/write your own method. There's a blog post here about the latter:

3

dev wrote:

What is the "proper" way to add constraints using MySQL as the database? I have it set up like this:

  t.integer :product_id, :null => false, :options=> "CONSTRAINT fk_line_item_products REFERENCES products(id)"

This does nothing even if I fill the options with obviously bad syntax or just garbage.

Suggestions?

Use a plugin such as matthuhiggins-foreigner or foreign_key_constraints.

Best,

4

Hi Max,

The snippet you were trying might work for SQLite but not for mysql. I doubt Rails directly supports the declaration of foreign key constraints through ruby code.

So you have take the db specific sql way of adding and executing the foreign key constraints.

You can try adding the following to the migration script, to be executed on mysql db.

execute “alter table line_items add constraint fk_line_item_products foreign key (product_id) references products(id)”

Thanks.

5

Marnen Laibow-Koser wrote:

dev wrote:

What is the "proper" way to add constraints using MySQL as the database? I have it set up like this:

  t.integer :product_id, :null => false, :options=> "CONSTRAINT fk_line_item_products REFERENCES products(id)"

This does nothing even if I fill the options with obviously bad syntax or just garbage.

Suggestions?

Use a plugin such as matthuhiggins-foreigner or foreign_key_constraints.

Sorry, I meant foreign_key_migrations.

Best,

6

Try my fork, sparkfly-foreigner. I had added a much more comprehensive rspec suite to make sure it does what it says it does. http://github.com/sparkfly/foreigner

Ho-Sheng Hsiao http://hosheng.blogspot.com/