mysql 外键引发的删除失败

mysql 外键引发的删除失败

mysql> TRUNCATE TABLE role ;
ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (test.account, CONSTRAINT FK_account_role FOREIGN KEY (roleId) REFERENCES test.role (id))
mysql> TRUNCATE TABLE authority ;
ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (test.role_authority, CONSTRAINT FK_sccf4fx8omb6jlsy2ra75xxer FOREIGN KEY (authorityId) REFERENCES test.authority (id))
mysql>
mysql> TRUNCATE TABLE role ;
ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (test.account, CONSTRAINT FK_account_role FOREIGN KEY (roleId) REFERENCES test.role (id))
mysql>

方案1:

SET FOREIGN_KEY_CHECKS = 0;

TRUNCATE table1;
TRUNCATE table2;

SET FOREIGN_KEY_CHECKS = 1;

方案2：

Option 1: which does not risk damage to data integrity:

    Remove constraints
    Perform TRUNCATE
    Delete manually the rows that now have references to nowhere
    Create constraints

posted on 2017-05-17 00:08 CanntBelieve 阅读( ...) 评论( ...) 编辑 收藏

转载于:https://www.cnblogs.com/FlyAway2013/p/6864466.html