使用下面的修改会提示错误:1005 - Can't create table 'csgps.#sql-388_176' (errno: 121)[SQL] ALTER TABLE `t_terminal`
DROP FOREIGN KEY `FK704405E7F06A14EF`,
ADD CONSTRAINT `FK704405E7F06A14EF` FOREIGN KEY (`userId`) REFERENCES `t_user` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT;
[Err] 1005 - Can't create table 'csgps.#sql-388_176' (errno: 121)
而下面这样写才可以ALTER TABLE `t_terminal`
DROP FOREIGN KEY `FK704405E7F06A14EF`;
ALTER TABLE `t_terminal`
ADD CONSTRAINT `FK704405E7F06A14EF` FOREIGN KEY (`userId`) REFERENCES `t_user` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT;好奇怪啊
一个表有很多个外键呢,每次都要来一个ALTER ? 能不能修改为只在前面alter就可以的啊
DROP FOREIGN KEY `FK704405E7F06A14EF`,
ADD CONSTRAINT `FK704405E7F06A14EF` FOREIGN KEY (`userId`) REFERENCES `t_user` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT;
[Err] 1005 - Can't create table 'csgps.#sql-388_176' (errno: 121)
而下面这样写才可以ALTER TABLE `t_terminal`
DROP FOREIGN KEY `FK704405E7F06A14EF`;
ALTER TABLE `t_terminal`
ADD CONSTRAINT `FK704405E7F06A14EF` FOREIGN KEY (`userId`) REFERENCES `t_user` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT;好奇怪啊
一个表有很多个外键呢,每次都要来一个ALTER ? 能不能修改为只在前面alter就可以的啊
SET FOREIGN_KEY_CHECKS = 0
再修改试试
我用的是Navicat 8工具,用它修改外键关系的时候就是生成错误的那种,是编写工具的人不懂得这个问题,还是通过什么修改可以实现?
ALTER TABLE ADD FOREIGN KEY
ALTER TABLE DROP FOREIGN KEY 没有 alter / change /modify FOREIGN KEY 的操作。
SET FOREIGN_KEY_CHECKS = 1打开
SET FOREIGN_KEY_CHECKS = 0关闭
SET FOREIGN_KEY_CHECKS = 1打开
只是在删除外键的时候起作用,那个应该说语法问题,MYSQL没有遇到分号是不提交事务的,造成外键冲突了
看来是没办法