Tests failing due to foreign key constraints? How to control order of creation of database tables...

I am having some problems with tests failing, and it seems to be related to
the foreign key constraints I have in the database.

  Listing A shows what appears in development.log, when I run "rake test",
before any output appears in test.log (which I find strange because I would
have thought that all output would go to test.log when running in test mode).
Lising B shows the corresponding output of the "rake test --trace" command.

  IIf I remove the foreign key constraints from the database, the test runs
fine, and Listing C appears in the development log instead. The
corresponding "rake test --trace" output is shown in Listing D.

  So it seems that as part of the testing process, rails is attempting to drop
and recreate all of the tables in the database, but it is doing it in the
wrong order so that the foreign key constraints get in they way. In fact the
ordering is based on the order that the tables are created in schema.rb
(LISTING E) (which is different to the order that I programmatically create
the tables in the migration file (LISTING F)). So has anyone else had this
problem? Is the solution a matter of controlling which order schema.rb
creates the table? Or is there something more fundamental wrong with my
setup?

Thanks.
  
============= LISTING A ========================

SQL (0.000450) SELECT * FROM schema_info
  SQL (0.000502) SHOW TABLES
  SQL (0.001142) SHOW FIELDS FROM functional_rights
  SQL (0.001021) SHOW KEYS FROM functional_rights
  SQL (0.001017) SHOW FIELDS FROM functional_rights_functional_roles
  SQL (0.000922) SHOW KEYS FROM functional_rights_functional_roles
  SQL (0.001036) SHOW FIELDS FROM functional_roles
  SQL (0.000856) SHOW KEYS FROM functional_roles
  SQL (0.000990) SHOW FIELDS FROM functional_roles_users
  SQL (0.000903) SHOW KEYS FROM functional_roles_users
  SQL (0.001211) SHOW FIELDS FROM users
  SQL (0.001118) SHOW KEYS FROM users
  SQL (0.000000) Mysql::Error: Cannot delete or update a parent row: a
foreign key constraint fails: DROP TABLE functional_rights
  SQL (0.000000) Mysql::Error: Table 'functional_rights' already exists:
CREATE TABLEfunctional_rights (`id` int(11) DEFAULT NULL auto_increment
PRIMARY KEY, `name` varchar(255) DEFAULT '' NOT NULL, `controller`
varchar(255) DEFAULT '' NOT NULL, `action` varchar(255) DEFAULT '' NOT NULL)
ENGINE=InnoDB

============= LISTING B ========================

(in /home/michael/code/rails/work/railsproject)
** Invoke test (first_time)
** Execute test
** Invoke test:units (first_time)
** Invoke db:test:prepare (first_time)
** Invoke environment (first_time)
** Execute environment
** Execute db:test:prepare
** Invoke db:test:clone (first_time)
** Invoke db:schema:dump (first_time)
** Invoke environment
** Execute db:schema:dump
** Execute db:test:clone
** Invoke db:schema:load (first_time)
** Invoke environment
** Execute db:schema:load
** Invoke test:functionals (first_time)
** Invoke db:test:prepare
** Execute test:functionals
/usr/bin/ruby18 -Ilib:test
"/usr/lib/ruby/gems/1.8/gems/rake-0.7.1/lib/rake/rake_test_loader.rb"
"test/functional/functional_role_controller_test.rb"
"test/functional/user_controller_test.rb"
"test/functional/functional_right_controller_test.rb"
"test/functional/session_controller_test.rb"
Loaded suite /usr/lib/ruby/gems/1.8/gems/rake-0.7.1/lib/rake/rake_test_loader
Started
..............
Finished in 0.181921 seconds.

14 tests, 80 assertions, 0 failures, 0 errors
** Invoke test:integration (first_time)
** Invoke db:test:prepare
** Execute test:integration
/usr/bin/ruby18 -Ilib:test
"/usr/lib/ruby/gems/1.8/gems/rake-0.7.1/lib/rake/rake_test_loader.rb"
rake aborted!
Test failures
/usr/lib/ruby/gems/1.8/gems/rails-1.1.6/lib/tasks/testing.rake:35
/usr/lib/ruby/gems/1.8/gems/rake-0.7.1/lib/rake.rb:387:in `execute'
/usr/lib/ruby/gems/1.8/gems/rake-0.7.1/lib/rake.rb:387:in `execute'
/usr/lib/ruby/gems/1.8/gems/rake-0.7.1/lib/rake.rb:357:in `invoke'
/usr/lib/ruby/1.8/thread.rb:135:in `synchronize'
/usr/lib/ruby/gems/1.8/gems/rake-0.7.1/lib/rake.rb:350:in `invoke'
/usr/lib/ruby/gems/1.8/gems/rake-0.7.1/lib/rake.rb:1906:in `run'
/usr/lib/ruby/gems/1.8/gems/rake-0.7.1/lib/rake.rb:1906:in `run'
/usr/lib/ruby/gems/1.8/gems/rake-0.7.1/bin/rake:7
/usr/bin/rake:18

============= LISTING C ========================

SQL (0.000450) SELECT * FROM schema_info
  SQL (0.000485) SHOW TABLES
  SQL (0.001146) SHOW FIELDS FROM functional_rights
  SQL (0.001023) SHOW KEYS FROM functional_rights
  SQL (0.001011) SHOW FIELDS FROM functional_rights_functional_roles
  SQL (0.000823) SHOW KEYS FROM functional_rights_functional_roles
  SQL (0.001001) SHOW FIELDS FROM functional_roles
  SQL (0.000853) SHOW KEYS FROM functional_roles
  SQL (0.000991) SHOW FIELDS FROM functional_roles_users
  SQL (0.000838) SHOW KEYS FROM functional_roles_users
  SQL (0.001170) SHOW FIELDS FROM users
  SQL (0.001298) SHOW KEYS FROM users
  SQL (0.001402) DROP TABLE functional_rights
  SQL (0.006160) CREATE TABLE functional_rights (`id` int(11) DEFAULT NULL
auto_increment PRIMARY KEY, `name` varchar(255) DEFAULT '' NOT NULL,
`controller` varchar(255) DEFAULT '' NOT NULL, `action` varchar(255) DEFAULT
'' NOT NULL) ENGINE=InnoDB
  SQL (0.001514) DROP TABLE functional_rights_functional_roles
  SQL (0.010817) CREATE TABLE functional_rights_functional_roles
(`functional_right_id` int(11), `functional_role_id` int(11)) ENGINE=InnoDB
  SQL (0.001557) DROP TABLE functional_roles
  SQL (0.004841) CREATE TABLE functional_roles (`id` int(11) DEFAULT NULL
auto_increment PRIMARY KEY, `name` varchar(255) DEFAULT '' NOT NULL)
ENGINE=InnoDB
  SQL (0.001718) DROP TABLE functional_roles_users
  SQL (0.005080) CREATE TABLE functional_roles_users (`functional_role_id`
int(11), `user_id` int(11)) ENGINE=InnoDB
  SQL (0.001777) DROP TABLE users
  SQL (0.005176) CREATE TABLE users (`id` int(11) DEFAULT NULL
auto_increment PRIMARY KEY, `username` varchar(255) DEFAULT '' NOT NULL,
`password_salt` varchar(255) DEFAULT '' NOT NULL, `password_hash`
varchar(255) DEFAULT '' NOT NULL, `email_address` varchar(255) DEFAULT '' NOT
NULL, `created_at` datetime NOT NULL) ENGINE=InnoDB
  SQL (0.000000) Mysql::Error: Table 'schema_info' already exists: CREATE
TABLE schema_info (version int(11))
  SQL (0.000974) SHOW FIELDS FROM schema_info
  SQL (0.000680) UPDATE schema_info SET version = 2

============= LISTING D ========================

(in /home/michael/code/rails/work/railsproject)
** Invoke test (first_time)
** Execute test
** Invoke test:units (first_time)
** Invoke db:test:prepare (first_time)
** Invoke environment (first_time)
** Execute environment
** Execute db:test:prepare
** Invoke db:test:clone (first_time)
** Invoke db:schema:dump (first_time)
** Invoke environment
** Execute db:schema:dump
** Execute db:test:clone
** Invoke db:schema:load (first_time)
** Invoke environment
** Execute db:schema:load
** Execute test:units
/usr/bin/ruby18 -Ilib:test
"/usr/lib/ruby/gems/1.8/gems/rake-0.7.1/lib/rake/rake_test_loader.rb"
"test/unit/functional_role_test.rb" "test/unit/overlay_test.rb"
"test/unit/password_test.rb" "test/unit/user_test.rb"
"test/unit/functional_right_test.rb"
Loaded suite /usr/lib/ruby/gems/1.8/gems/rake-0.7.1/lib/rake/rake_test_loader
Started
...............................................
Finished in 1.309497 seconds.

47 tests, 262 assertions, 0 failures, 0 errors
** Invoke test:functionals (first_time)
** Invoke db:test:prepare
** Execute test:functionals
/usr/bin/ruby18 -Ilib:test
"/usr/lib/ruby/gems/1.8/gems/rake-0.7.1/lib/rake/rake_test_loader.rb"
"test/functional/functional_role_controller_test.rb"
"test/functional/user_controller_test.rb"
"test/functional/functional_right_controller_test.rb"
"test/functional/session_controller_test.rb"
Loaded suite /usr/lib/ruby/gems/1.8/gems/rake-0.7.1/lib/rake/rake_test_loader
Started
..............
Finished in 0.15657 seconds.

14 tests, 80 assertions, 0 failures, 0 errors
** Invoke test:integration (first_time)
** Invoke db:test:prepare
** Execute test:integration
/usr/bin/ruby18 -Ilib:test
"/usr/lib/ruby/gems/1.8/gems/rake-0.7.1/lib/rake/rake_test_loader.rb"

===========================LISTING E ==========================

ActiveRecord::Schema.define(:version => 2) do

  create_table "functional_rights", :force => true do |t|
    t.column "name", :string, :default => "", :null => false
    t.column "controller", :string, :default => "", :null => false
    t.column "action", :string, :default => "", :null => false
  end

  create_table "functional_rights_functional_roles", :id => false, :force =>
true do |t|
    t.column "functional_right_id", :integer
    t.column "functional_role_id", :integer
  end

  add_index "functional_rights_functional_roles",
["functional_right_id"], :name => "fk_frfr_functional_right"
  add_index "functional_rights_functional_roles",
["functional_role_id"], :name => "fk_frfr_functional_role"

  create_table "functional_roles", :force => true do |t|
    t.column "name", :string, :default => "", :null => false
  end

  create_table "functional_roles_users", :id => false, :force => true do |t|
    t.column "functional_role_id", :integer
    t.column "user_id", :integer
  end

  add_index "functional_roles_users", ["functional_role_id"], :name =>
"fk_fru_functional_role"
  add_index "functional_roles_users", ["user_id"], :name => "fk_fru_user"

  create_table "users", :force => true do |t|
    t.column "username", :string, :default => "", :null => false
    t.column "password_salt", :string, :default => "", :null => false
    t.column "password_hash", :string, :default => "", :null => false
    t.column "email_address", :string, :default => "", :null => false
    t.column "created_at", :datetime, :null => false
  end

end

========================== LISTING F ===========================

class AddFunctionalRightsAndFunctionalRolesTables < ActiveRecord::Migration
  def self.up
    create_table(:functional_rights, :force => true) { |t| # A table storing
the actions on a given controller that a given right allows a user to
perform.
    t.column :name, :string, :null => false
    t.column :controller, :string, :null => false
    t.column :action, :string, :null => false
  }
  
  create_table(:functional_roles, :force => true) { |t| # *functional* roles,
referring to the fact that we are talking about actions that a user may
perform, rather than data they can access.
    t.column :name, :string, :null => false
  }
  
  # Join
  create_table(:functional_rights_functional_roles, :id => false, :force =>
true) { |t|
    t.column :functional_right_id, :integer
    t.column :functional_role_id, :integer
  }
  
  # Join
  create_table(:functional_roles_users, :id => false, :force => true) { |t|
    t.column :functional_role_id, :integer
    t.column :user_id, :integer
  }
  
  # Add foreign key constraints.
  execute 'ALTER TABLE functional_rights_functional_roles ADD CONSTRAINT
fk_frfr_functional_right FOREIGN KEY ( functional_right_id ) REFERENCES
functional_rights( id ) '
  
  execute 'ALTER TABLE functional_rights_functional_roles ADD CONSTRAINT
fk_frfr_functional_role FOREIGN KEY ( functional_role_id ) REFERENCES
functional_roles( id ) '
  
  execute 'ALTER TABLE functional_roles_users ADD CONSTRAINT
fk_fru_functional_role FOREIGN KEY ( functional_role_id ) REFERENCES
functional_roles( id ) '
  
  execute 'ALTER TABLE functional_roles_users ADD CONSTRAINT fk_fru_user
FOREIGN KEY ( user_id ) REFERENCES users( id ) '
  
  end

  def self.down
    drop_table :functional_rights_functional_roles
    drop_table :functional_roles_users
    drop_table :functional_rights
    drop_table :functional_roles
  end
end

Have you had a look at the plug-ins from Red Hill Consulting, notably
schema_defining and foreign_key_migrations?
(http://www.redhillconsulting.com.au/rails_plugins.html). They
automatically add foreign keys to the schema based on key and table
name matches (can be overridden).

I use these and my tests run fine - I think the schema_defining plugin
(on which the foreign_key_migrations plug-in relies) means that in a
test environment the foreign keys are ignored allowing the database to
quickly create and drop content...

It's worth a look.

Have you had a look at the plug-ins from Red Hill Consulting, notably
schema_defining and foreign_key_migrations?

I use these and my tests run fine - I think the schema_defining plugin
(on which the foreign_key_migrations plug-in relies) means that in a
test environment the foreign keys are ignored allowing the database to
quickly create and drop content...

Thanks for the info Ian. I think for the time being I'll just drop the
foreign key constraints, since according to chris@odegy.com 's email
(rubyonrails-talk 21.Aug.06 12:28), the "rails way" is to implement all
constraints in the ruby model. I've since found a blog post by DHH stating
the same... it does make me feel a bit uneasy, but I'm going to see where
"going with the rails flow" takes me...