Ruby on Rails
DisableForeignKeyChecksUnderMySql

When you’ve built a database that includes foreign key constraints between parent and child tables, loading fixtures using rake load_fixtures and unit testing can fail with errors such as:

Mysql::Error: #23000Cannot add or update a child row: a foreign key constraint fails (`depot_development/line_items`, CONSTRAINT `fk_items_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE): INSERT INTO line_items (`quantity`, `product_id`, `id`, `unit_price`) VALUES (1, 1, 1, 29.95)
</code>
*OR*
<code>
ActiveRecord::StatementInvalid: Mysql::Error: Cannot delete or update a parent row: a foreign key constraint fails:             DELETE FROM line_items WHERE id = 1

… because the child record is being inserted into the database before the parent record. A similar error can occur when updating fixtures using the same rake task because the old parent record is being deleted before the child record.

In order to load or update fixtures which include parent-child records you need to disable foreign key constraint checking temporarily, but re-enable it after the fixtures have loaded so that your database behaves as it should.

Instead of using rake load_fixtures, add the following task to the lib/tasks directory and use rake load_fixtures_without_constraints instead.

desc "Loads fixtures whilst turning foreign key constraints checking off"
task :load_fixtures_without_constraints => :environment do
	require 'active_record/fixtures'
	ActiveRecord::Base.establish_connection(RAILS_ENV.to_sym)
	ActiveRecord::Base.connection.update "SET FOREIGN_KEY_CHECKS = 0"
	Dir.glob(File.join(RAILS_ROOT, 'test', 'fixtures', '*.{yml,csv}')).each do |fixture_file|
		Fixtures.create_fixtures('test/fixtures', File.basename(fixture_file, '.*'))
	end
	ActiveRecord::Base.connection.update "SET FOREIGN_KEY_CHECKS = 1"
end

You can also turn off constraints when fixtures are loaded to run unit tests by adding the following to the end of test_helper.rb:

class Fixtures
  def delete_existing_fixtures_with_fk_checks_disabled
    @connection.update "SET FOREIGN_KEY_CHECKS = 0" 
    delete_existing_fixtures_without_fk_checks_disabled
    @connection.update "SET FOREIGN_KEY_CHECKS = 1" 
  end
  
  def insert_fixtures_with_fk_checks_disabled
    @connection.update "SET FOREIGN_KEY_CHECKS = 0" 
    insert_fixtures_without_fk_checks_disabled
    @connection.update "SET FOREIGN_KEY_CHECKS = 1" 
  end
  
  alias_method_chain :delete_existing_fixtures, :fk_checks_disabled unless method_defined?(:delete_existing_fixtures_without_fk_checks_disabled)
  alias_method_chain :insert_fixtures, :fk_checks_disabled unless method_defined?(:insert_fixtures_without_fk_checks_disabled)
end

The above code is a newer version, which should fix the stack error problem that some people were having.

—-

The alternative way:

I wrote a rake task based on weenie’s one that let you assign a sequence number to your .yml files in order to mantain foreign keys relations.