How to test tables linked with foreign keys?

Question

I am working with mysql and codeigniter using the redbean ORM. After implementing a foreign key for many to many assosciation I got the following error when I run:

drop TABLE IF EXISTS `temp`

Integrity constraint violation: 1217 Cannot delete or update a parent row: a foreign key constraint fails thrown

I then entered SHOW ENGINE INNODB STATUS into phpmyadmin. The output includes:

LATEST FOREIGN KEY ERROR------------------------:  Cannot drop table `db1`.`temp`because it is referenced by `db1`.`temp_workers`.

In other words another table references the FK. For testing purposes I think the best thing to do is to drop all the associated tables and recreate them using the controller I'm testing. Is this the best way to go? I've tried:

drop TABLE IF EXISTS `temp` `temp_workers`

, but I'm still getting the above error, and the drop command does not work. Also:

truncate TABLE `temp`, `temp_workers`

gives:

You have an error in your SQL syntax

Answer 1

As mentioned in the comments you have to drop any tables with FK contraints to other tables, first, then you can drop the tables being linked to.

Example:

User
  id: 1
  name: Mike

Address 
  id: 1
  user_id: 1 (FK constraint to User.id table.column)
  address_1: 555 Main Street

This setup is a 1:1 relationship (more on data normalization), where one user row can reference one address row, and because the address row is dependent upon the existence of the user row, if you attempt to remove the user row, you will see the errors you mentioned.

But if you drop the Address table first, everything works as expected because the User table is not FK to any other table.

Ensuring referential integrity within your schema ensures you do not end up with orphaned rows, which will permeate throughout your data driven application.

You could also issue the following commands:

SET foreign_key_checks = 0;
# Do Stuff
SET foreign_key_checks = 1;

But I would strongly advise against this, as you could break the referential integrity of your data, and end up in a real mess. I've seen someone do this in an enterprise environment and it took them weeks to clean it up. However, if you are doing this STRICTLY for testing purposes; like writing unit tests, or just learning, and you didn't want to drop the tables every time, you could do this:

# Because we remove the foreign key check, we can truncate in any order
SET foreign_key_checks = 0;
TRUNCATE TABLE user;
TRUNCATE TABLE address;
SET foreign_key_checks = 1;

Proper schema design using foreign key constraints goes along way to building a good foundation for any data driven application. It will take time to get your head around when to use, and how to construct foreign key constraints, but over time you will begin to understand. A good way to get started is to download an open source project like magento, wordpress, or vbulletin and take a look at their schemas. You can even introspect these schemas using MySQL workbench and view their Entity-Relationship Diagrams (ERDs), which will visually demonstrate links between tables.