Skip to main content

Rails: Foreign Key Constraints

I like referential integrity. Rails doesn't provide support for database-level foreign key constraints. "Agile Web Development with Rails" uses code such as the following to add foreign key constraints:
t.integer :product_id, :null => false, 
:options => "CONSTRAINT fk_line_item_products REFERENCES products(id)"
That doesn't work for me. The migration ran just fine, but when I looked at the database, the constraint wasn't present. I hate it when things fail silently!

I tried a plugin to add foreign key constraints, but I got frustrated pretty quickly. I fell back to executing plain SQL:
execute %{
CREATE TABLE roles_users (
role_id INT NOT NULL,
user_id INT NOT NULL,

INDEX (role_id),
INDEX (user_id)
The nice thing about plain SQL is that I can understand exactly what's going on. There are no layers of abstraction in my way, and if my SQL is wrong, MySQL will complain.

To be fair, MySQL's errors aren't always the most helpful. I encountered a couple of syntax errors. One was because I misspelled a column name. Another was because I put the name of the foreign key constraint in the wrong place. In both cases, I ended up with the following:
ERROR 1025 (HY000): Error on rename of './myapp_development/mymodel' to './myapp_development/#sql2-87-40c' (errno: 152)
Lovely! Oh well, at least it's better than failing silently.

Since I'm trying out test driven development, I knew I had to write a test before I could go mucking around with my schema. It turned out to be fairly hard to test that the database itself enforces a foreign key constraint. After all, Rails loves to try to enforce those constraints itself. Here's the RSpec test I ended up with:
require File.expand_path(File.dirname(__FILE__) + '/../spec_helper')

describe Role do
context "the roles table" do
it "should enforce foreign key constraints in the database" do
verbose = ActiveRecord::Migration.verbose
ActiveRecord::Migration.verbose = false
result = ActiveRecord::Migration.execute("SHOW CREATE TABLE roles_users")
sql = result.fetch_row[1]
[/CONSTRAINT/, /FOREIGN KEY/, /REFERENCES/].each do |phrase|
sql.should =~ phrase
ActiveRecord::Migration.verbose = verbose
Ugh, painful. (Apparently, the silence method is only available on controllers. There's a different logger for migrations.)

Oh well. My tests pass, and I have referential integrity.


jjinux said…
If I wanted to use a plugin instead of straight SQL, this is the one that looked the most promising:
Matthew Higgins said…
Hey - I just restarted active development on foreigner. The current thing I'm working on is extracting the foreign keys from the database so that they can be dumped to schema.rb.
jjinux said…
That'd be nice ;) Currently, I have to ignore schema.rb by using rake db:migrate RAILS_ENV=test when migrating my test database.
Matthew Higgins said…
Foreigner now defines the foreign keys in schema.rb for both MySql and Postgresql. Seems to work well.
jjinux said…
Thanks. I'll give it a shot.
jjinux said…
Yeah, foreigner,, is pretty sweet. I still have a bunch of older migrations that are written in pure SQL, but foreigner automatically put the right stuff in my schema.rb file.

As far as I'm concerned, it's the right balance of useful and not too magical.

Thanks, again!

Popular posts from this blog

Ubuntu 20.04 on a 2015 15" MacBook Pro

I decided to give Ubuntu 20.04 a try on my 2015 15" MacBook Pro. I didn't actually install it; I just live booted from a USB thumb drive which was enough to try out everything I wanted. In summary, it's not perfect, and issues with my camera would prevent me from switching, but given the right hardware, I think it's a really viable option. The first thing I wanted to try was what would happen if I plugged in a non-HiDPI screen given that my laptop has a HiDPI screen. Without sub-pixel scaling, whatever scale rate I picked for one screen would apply to the other. However, once I turned on sub-pixel scaling, I was able to pick different scale rates for the internal and external displays. That looked ok. I tried plugging in and unplugging multiple times, and it didn't crash. I doubt it'd work with my Thunderbolt display at work, but it worked fine for my HDMI displays at home. I even plugged it into my TV, and it stuck to the 100% scaling I picked for the othe

ERNOS: Erlang Networked Operating System

I've been reading Dreaming in Code lately, and I really like it. If you're not a dreamer, you may safely skip the rest of this post ;) In Chapter 10, "Engineers and Artists", Alan Kay, John Backus, and Jaron Lanier really got me thinking. I've also been thinking a lot about Minix 3 , Erlang , and the original Lisp machine . The ideas are beginning to synthesize into something cohesive--more than just the sum of their parts. Now, I'm sure that many of these ideas have already been envisioned within , LLVM , Microsoft's Singularity project, or in some other place that I haven't managed to discover or fully read, but I'm going to blog them anyway. Rather than wax philosophical, let me just dump out some ideas: Start with Minix 3. It's a new microkernel, and it's meant for real use, unlike the original Minix. "This new OS is extremely small, with the part that runs in kernel mode under 4000 lines of executable code.&quo

Haskell or Erlang?

I've coded in both Erlang and Haskell. Erlang is practical, efficient, and useful. It's got a wonderful niche in the distributed world, and it has some real success stories such as CouchDB and Haskell is elegant and beautiful. It's been successful in various programming language competitions. I have some experience in both, but I'm thinking it's time to really commit to learning one of them on a professional level. They both have good books out now, and it's probably time I read one of those books cover to cover. My question is which? Back in 2000, Perl had established a real niche for systems administration, CGI, and text processing. The syntax wasn't exactly beautiful (unless you're into that sort of thing), but it was popular and mature. Python hadn't really become popular, nor did it really have a strong niche (at least as far as I could see). I went with Python because of its elegance, but since then, I've coded both p