[FIXED] Getting cannot truncate a table referenced in a foreign key constraint in Yii framework

Issue

I'm getting cannot truncate a table referenced in a foreign key constraint in Yii framework

I have tried the below which I could see in some of the answers but still not working

Yii::app()->db->createCommand('set foreign_key_checks=0')->execute();
$this->db->createCommand()->checkIntegrity(false)->execute();
$this->getFixtureManager()->checkIntegrity(false);

I get the error as below

Database Exception – yii\db\Exception

SQLSTATE[42000]: Syntax error or access violation: 1701 Cannot truncate a table referenced in a foreign key constraint (yiibasic.educations, CONSTRAINT FK_user FOREIGN KEY (user_id) REFERENCES yiibasic.users (id)) The SQL being executed was: TRUNCATE TABLE users

Error Info: Array (
[0] => 42000
[1] => 1701
[2] => Cannot truncate a table referenced in a foreign key constraint
(yiibasic.educations, CONSTRAINT FK_user FOREIGN KEY (user_id) REFERENCES yiibasic.users (id)) )

Can any one help me to correct it. Also can you give the best way of seeding data in Yii I coudn't get a good tutorial for the same

Solution

You must first disable the constraints : SET foreign_key_checks = 0;

then truncate your tables

Then reactivate the constraints : SET foreign_key_checks = 1;

Answered By - loustalet