mysql删除外键_mysql 删除外键约束

查看创建数据库语句

show create table table_name;

查询schema中所有外键名称然后拼接生成删除语句，再执行。

SELECT CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' DROP FOREIGN KEY ',CONSTRAINT_NAME,' ;')

FROM information_schema.TABLE_CONSTRAINTS c

WHERE c.TABLE_SCHEMA='db_name' AND c.CONSTRAINT_TYPE='FOREIGN KEY';

删除表约束

alter table table_name drop foreign key FKstne13dqw7l76ib6cr6ytwl7v;

commit;

参数配置

SET FOREIGN_KEY_CHECKS = 0;

SET GLOBAL FOREIGN_KEY_CHECKS = 0;

查看结果

SELECT @@FOREIGN_KEY_CHECKS;