Geek asks and answers

How to check tables related to foreign keys?

I work with mysql and codeigniter using ORM redbean. After implementing the foreign key for many, many associations, I received the following error at startup:

drop TABLE IF EXISTS `temp`

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

Then I introduced SHOW ENGINE INNODB STATUS to 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 refers to FK. For testing purposes, I believe that it is best to drop all related tables and recreate them using the controller that I am testing. Is this the best way to go? I tried:

drop TABLE IF EXISTS `temp` `temp_workers`

but I still get 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
+5
source share
1 answer

, FK , -, .

:

User
  id: 1
  name: Mike

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

1:1 ( ), , , , , , .

Address, , , User FK .

, , , .

:

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

, . , - , , . , STRICTLY ; , , , :

# 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;

, . , , , , . - , magento, wordpress, vbulletin . workbench MySQL Entity- (ERD), .

+2

More articles:


All Articles