Ruby on Rails
HowToDisableForeignKeyConstraintsInMySQL

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)

——

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 reloading fixtures using the same rake task because the 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

An earlier posting, DisableForeignKeyChecksUnderMySql(Version #4)? included the following code to add to test_helper.rb to disable constraints while fixtures are loaded when unit testing. Add it to the bottom of the file (below and outside the Test:Unit:TestCase definition):

class Fixtures
      alias :original_delete_existing_fixtures :delete_existing_fixtures
      alias :original_insert_fixtures :insert_fixtures

      def delete_existing_fixtures
          @connection.update "SET FOREIGN_KEY_CHECKS = 0" 
          original_delete_existing_fixtures
          @connection.update "SET FOREIGN_KEY_CHECKS = 1" 
      end

      def insert_fixtures
          @connection.update "SET FOREIGN_KEY_CHECKS = 0" 
          original_insert_fixtures
          @connection.update "SET FOREIGN_KEY_CHECKS = 1" 
      end
end