# MySQL Constraints - Active Record

**URL:** <https://www.ruby-forum.com/t/mysql-constraints-active-record/188067>\
**Category:** Rails\
**Created:** [May 5, 2010, 10:02am UTC](https://www.ruby-forum.com/t/mysql-constraints-active-record/188067 "2010-05-05T10:02:36Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\
**Post date:** [May 5, 2010, 10:02am UTC](https://www.ruby-forum.com/t/mysql-constraints-active-record/188067/1 "2010-05-05T10:02:36Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\
**Post date:** [May 5, 2010, 12:21pm UTC](https://www.ruby-forum.com/t/mysql-constraints-active-record/188067/2 "2010-05-05T12:21:46Z")

</div>

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:

> **[Ruby on Rails – Foreign Key Constraints in MySQL](https://soniahamilton.wordpress.com/2008/10/01/ruby-on-rails-foreign-key-constraints-in-mysql/)**
>
> As an ex-DBA, one of the things that annoys me about Rails is that migrations don’t have a way of setting up referential integrity (I’m still using Rails 1.2.3, so this may have changed…

---

<div class="post-metadata">

**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\
**Post date:** [May 5, 2010, 2:01pm UTC](https://www.ruby-forum.com/t/mysql-constraints-active-record/188067/3 "2010-05-05T14:01:23Z")

</div>

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,

Marnen Laibow-Koser  
[http://www.marnen.org](http://www.marnen.org)  
removed\_email\_address@domain.invalid

---

<div class="post-metadata">

**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\
**Post date:** [May 5, 2010, 7:16pm UTC](https://www.ruby-forum.com/t/mysql-constraints-active-record/188067/4 "2010-05-05T19:16:33Z")

</div>

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,

Marnen Laibow-Koser  
[http://www.marnen.org](http://www.marnen.org)  
removed\_email\_address@domain.invalid

---

<div class="post-metadata">

**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\
**Post date:** [May 5, 2010, 5:46pm UTC](https://www.ruby-forum.com/t/mysql-constraints-active-record/188067/5 "2010-05-05T17:46:53Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\
**Post date:** [May 6, 2010, 12:46pm UTC](https://www.ruby-forum.com/t/mysql-constraints-active-record/188067/6 "2010-05-06T12:46:01Z")

</div>

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

> **[GitHub - sparkfly/foreigner: Foreigner -- Foreign Key Constraints for...](https://github.com/sparkfly/foreigner)**
>
> Foreigner -- Foreign Key Constraints for PostgreSQL and MySQL. \*Thoroughly\* tested via rspec. - GitHub - sparkfly/foreigner: Foreigner -- Foreign Key Constraints for PostgreSQL and MySQL. \*Thorough...

Ho-Sheng H.  
[http://hosheng.blogspot.com/](http://hosheng.blogspot.com/)

> **[lambda { }](http://ruby-lambda.blogspot.com/)**
>
> lambda { put(self).in(:pipe).and.call() }