I’m not yet so enlightened that all of my Rails unit and functional tests run without accessing the database. Indeed, I’m still using YAML fixtures to populate the database for testing.

I also insist on having foreign key constraints in the database, a thing that’s not exactly encouraged by Rails, but which is quite possible nonetheless. The various plugins from RedHill Consulting are a big help.

But then, when you feel all warm and cosy due to the additional safety at the database-level, you’re suddenly trapped by a snag: Sooner or later you find that your fixtures contain dependencies among objects that preclude any attempt at clever ordering by violating one foreign key constraint or another. Fixture files are loaded one after another in their entirety and when an object in an earlier fixture refers to an object in a later fixture, the database aptly notices as an inconsistency.

Well, you may think, it is an inconsistency, but only a temporal one. After all the fixture files are loaded, everything is consistent again. That’s the clue. We need to tell the database that, yes, indeed, things may be inconsistent for a time, but we’ll be cleaning up, promise. The good thing is that there is even an SQL standard-compliant way to express this promise.

  START TRANSACTION
  SET CONSTRAINTS ALL DEFERRED
  COMMIT

If you use transactional fixtures, the transaction bracket is already provided by Rails, but there’s no pretty way to sneak in the "SET CONSTRAINTS ..." line. There are two ways of slightly different brutality. First, you can edit activerecord/lib/fixtures.rb and just insert the required line.

  def self.create_fixtures(fixtures_directory, table_names, class_names = {})
    ...
    connection.transaction(Thread.current['open_transactions'].to_i == 0) do
      # insert the following line
      connection.execute("SET CONSTRAINTS ALL DEFERRED")
      ...
    end
    ...
  end

Alternatively, you can overwrite the entire method in, say, <railsapp>/lib/transactional_fixture_loading_hack.rb like this

require 'active_record/fixtures'

Fixtures.class_eval do
  def self.create_fixtures(fixtures_directory, table_names, class_names = {})
    ...
    connection.transaction(Thread.current['open_transactions'].to_i == 0) do
      # inserted line
      connection.execute("SET CONSTRAINTS ALL DEFERRED")
      ...
    end
    ...
  end

Whatever you do, you’ll have to inspect your code whenever you update your Rails version.

We’re still not done, unfortunately. The database defers only those constraints that are deferrable. Have a look at the Foreign Key Migrations Plugin for how to achieve this.

As a matter of convenience, I suggest that in your test_helper.rb you add a method that loads all your fixtures

class Test::Unit::TestCase
  self.use_transactional_fixtures = true

  def self.load_all_fixtures
    fixtures :users, :thingamajigs, :gadgets, :widgets
  end
end

Then, in a testcase class you can use it like this

require File.dirname(__FILE__) + '/../test_helper'

class ThingamajigTest < Test::Unit::TestCase
  load_all_fixtures

  ...
end

Note that with transactional fixtures this results in each fixture file loaded only once for all the tests.

So, there we are a last. Or those with a reasonable DBMS, I might say. For, of course, this technique is no use, if your database does not support deferrable constraints. PostgreSQL for one does support them.