Disable Foreign Key Check Under MySQL

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)

OR

ActiveRecord::StatementInvalid: Mysql::Error: 
Cannot delete or update a parent row: a foreign key constraint fails: 
DELETE FROM line_items WHERE id = 1

If you've ever gotten the above error message from unit testing with Rails, this is what you gotta do.

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

Read more about it here.

Foreign Key in MySQL Error #1452 and #1216

This has caused me 3-4 hours of time wasting and it is so bloody simple! I can't believe after googling for so many hours that I could not find something that could solve this problem for me.

If you've came across an error #1452 when trying to add a foreign key and you have existing entries in the table, you need to make sure there are valid values in all entries in the foreign key column before creating a foreign key.

So what I did was do a zero fill on the foreign key column. Sweet I thought. This should fix it. After trying to create the foreign key again, I get an error #1216. Turns out that the values in the foreign key column must be valid (i.e. correspond to a valid id to referenced table) and the zero fill obviously didn't help because I dont have any id with 0.

Once you've entered valid values in, the foreign key creation should work! Make sure that the column in the referenced table is a primary key or an index and foreign key column is an index before proceeding.