postgresql - Rails 3 + rspec + postgres: How to run test suite against a database with foreign key constraints? -

i have rails 3 application use postgresql 9.1 database. use rspec , factorygirl testing.

the data model has lots of models in 1-to-many or many-to-many relationships, , constraints encoded in rails models using has_many, belongs_to, has_many :through, et cetera. have code looks like:

class user < activerecord::base    attr_accessible :name    has_many :phones end  class phone < activerecord::base    attr_accessible :number, user_id    belongs_to :user end 

the schema in postgresql looks thing this

create table users (id integer, name varchar(20)); create table phones (id integer, number varchar(20), user_id integer); 

however, prefer encode data constraints in database using foreign key constraints instead of in model. this, created rails migration , added foreign key constraints doing like:

sql = "alter table phones add constraint phones_user_id foreign key (user_id) references users (id) on delete restrict;" activerecord::base.connection.execute(sql) 

when added foreign key constraints, , started application in development mode, database enforced constraints, , wasn't able delete user if had dependent phones. , held true whether in "psql" postgresql console or irb rails console.

however, when tried writing rspec test see if foreign key enforcing delete restrictions, test failed , able delete user dependent phones. apparently, "rake db:test:prepare" not prepare databases foreign keys.

is there way can run rspec test suite against database enforces foreign key constraints?

in rails 4, it's easy:

in config/application.rb, set

module yourapp   class application < rails::application     config.active_record.schema_format = :sql   end end 

rake rails_env=test db:migrate dump schema in sql db/structure.sql, , rake rails_env=test test:load load it. tasks use pg_dump postgres, they'll preserve every aspect of schema.

rails 3.2's test-database-related rake tasks didn't respect config.active_record.schema_format, didn't bother setting it. instead, overrode tasks use db:structure:load rather db:schema:load. in lib/tasks/db.rb:

namespace :db   namespace :migrate     # override original of task consistency. original, doesn't seed.     rake::task['db:migrate:reset'].clear     task reset: [ 'db:drop', 'db:create', 'db:structure:load', 'db:migrate' ]   end    # overrides original, db:schema:load. original doesn't migrate; version does,   # since, unlike schema.rb, *_structure.sql not include migrations.   rake::task['db:setup'].clear   task setup: [ 'test:ensure_environment_is_test', 'db:create', 'db:structure:load', 'db:migrate', 'db:seed' ]    rake::task['db:reset'].clear   task reset: [ 'test:ensure_environment_is_test', 'db:drop', 'db:setup' ]    namespace :test     desc "rspec tasks depend on task, override set database in way want."     rake::task['db:test:prepare'].clear     task prepare: [ 'db:reset' ]   end  end  namespace :test   task :ensure_environment_is_test     raise "don't know how db:setup rails_env=#{rails.env}" unless rails.env.test?   end end 

run rake db:structure:dump manually create db/structure.sql, , again roll migrations. modify above dump every time migrate, rails 4's tasks do, might or might not find inconsequential changes in dump make annoying.

even more hacking necessary in earlier versions of rails; hope don't have go there.

regardless of rails version, it's worth reading activerecord gem's lib/active_record/railties/database.rb.