Mysql——操作mysql外键错误（ERROR：1833）

最新推荐文章于 2024-07-28 01:06:51 发布

原创最新推荐文章于 2024-07-28 01:06:51 发布 · 4.2k 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#python #Mysql #外键

Mysql 专栏收录该内容

1 篇文章

订阅专栏

在Tornado项目中遇到插入MySQL数据时因缺少默认值引发的错误，尝试将字段改为自增，但因外键约束失败。分析发现删除外键时未锁定表，导致修改仍受约束。解决步骤包括锁定表、删除外键、修改字段、重新添加外键和解锁，最终成功解决问题。

前言：

错误发生在tornado项目一个接口操作mysql数据表时候，插入时候报了：pymysql.err.InternalError: (1364, "Field 'up_user_id' doesn't have a default value")，很明显插入时候缺少一个默认值，但是这个字段原则上是不需要传的，所以需要修改为自增字段；然后数据库中修改这个表字段为自增时候报了：ERROR 1833 (HY000): Cannot change column 'up_user_id': used in a foreign key constraint 'ih_order_info_ibfk_1' of table 'ihome.ih_order_info'，牵扯到被引用外键了，然后一通alter drop foreign提示ok了，再去修改依然给我回馈了ERROR 1833 (HY000): Cannot change column 'up_user_id': used in a foreign key constraint 'ih_order_info_ibfk_1' of table 'ihome.ih_order_info'，这就有点尬了；

详细分析：

错误：ERROR 1833 (HY000): Cannot change column 'up_user_id': used in a foreign key constraint 'ih_order_info_ibfk_1' of table 'ihome.ih_order_info'；

原因：我更改的字段up_user_id被'ihome.ih_order_info'用成了外键，但是我前边说过alter drop foregin了，为啥修改删除外键后还是不能修改，涉及修改外键引用的表，是需要两个表处于锁定状态修改保证外键机制的，关键点是删除外键时候没有锁表导致外键并不是真正的删除，所以修改还是不成功；

ih_order_info建表语句：

 ih_order_info | CREATE TABLE `ih_order_info` (
  `oi_order_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '订单id',
  `oi_user_id` bigint(20) unsigned NOT NULL COMMENT '用户id',
  `oi_house_id` bigint(20) unsigned NOT NULL COMMENT '房屋id',
  `oi_begin_date` date NOT NULL COMMENT '入住时间',
  `oi_end_data` date NOT NULL COMMENT '离开时间',
  `oi_days` int(10) unsigned NOT NULL COMMENT '入住天数',
  `oi_house_price` int(10) unsigned NOT NULL COMMENT '房屋单价，单位份分',
  `oi_amount` int(10) unsigned NOT NULL COMMENT '订单金额，单位分',
  `oi_status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '订单状态,0-待接单，1-待支付,2-已支付，3-待评价，4-已完成，5-已取消，6-已拒单',
  `oi_comment` text COMMENT '订单评论',
  `oi_utime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `oi_citme` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`oi_order_id`),
  KEY `oi_status` (`oi_status`),
  KEY `oi_house_id` (`oi_house_id`),
  KEY `ih_order_info_ibfk1` (`oi_user_id`),
  CONSTRAINT `ih_order_info_ibfk1` FOREIGN KEY (`oi_user_id`) REFERENCES `ih_user_profile` (`up_user_id`),
  CONSTRAINT `ih_order_info_ibfk_2` FOREIGN KEY (`oi_house_id`) REFERENCES `ih_house_info` (`hi_house_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单表' |

解决步骤：

1、锁定两个表：

mysql> lock tables ih_order_info write,ih_user_profile write;
Query OK, 0 rows affected (0.01 sec)

2、检查表被锁结果：

mysql> show open tables where In_use > 0;
+----------+-----------------+--------+-------------+
| Database | Table           | In_use | Name_locked |
+----------+-----------------+--------+-------------+
| ihome    | ih_order_info   |      1 |           0 |
| ihome    | ih_user_profile |      1 |           0 |
+----------+-----------------+--------+-------------+

3、删除外键约束：

mysql> alter table ih_order_info drop foreign key ih_order_info_ibfk_1;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

此处也可以通过检查表创建过程查看外键是否清除：

show create table ih_order_info;
-- 我记得我实时查看了下外键1是不在了的；
ih_order_info | CREATE TABLE `ih_order_info` (
  `oi_order_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '订单id',
  `oi_user_id` bigint(20) unsigned NOT NULL COMMENT '用户id',
  `oi_house_id` bigint(20) unsigned NOT NULL COMMENT '房屋id',
  `oi_begin_date` date NOT NULL COMMENT '入住时间',
  `oi_end_data` date NOT NULL COMMENT '离开时间',
  `oi_days` int(10) unsigned NOT NULL COMMENT '入住天数',
  `oi_house_price` int(10) unsigned NOT NULL COMMENT '房屋单价，单位份分',
  `oi_amount` int(10) unsigned NOT NULL COMMENT '订单金额，单位分',
  `oi_status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '订单状态,0-待接单，1-待支付,2-已支付，3-待评价，4-已完成，5-已取消，6-已拒单',
  `oi_comment` text COMMENT '订单评论',
  `oi_utime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `oi_citme` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`oi_order_id`),
  KEY `oi_status` (`oi_status`),
  KEY `oi_user_id` (`oi_user_id`),
  KEY `oi_house_id` (`oi_house_id`),
  CONSTRAINT `ih_order_info_ibfk_2` FOREIGN KEY (`oi_house_id`) REFERENCES `ih_house_info` (`hi_house_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单表' |

4、修改需要修改的字段（也就是被引用外键的字段）；

mysql> alter table ih_user_profile modify up_user_id bigint unsigned not null auto_increment comment "用户id";
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

5、将外键添加回去：

 alter table ih_order_info add constraint ih_order_info_ibfk1 foreign key (oi_user_id) references ih_user_profile(up_user_id);

6、解除两个表的锁定：

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

然后python中操作接口成功；问题解决；