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

前言：

      错误发生在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中操作接口成功；问题解决；