MySQL Constraints - Active Record

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?

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:

http://soniahamilton.wordpress.com/2008/10/01/ruby-on-rails-foreign-key-constraints-in-mysql/

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,

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.

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,

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/
http://ruby-lambda.blogspot.com/