Ошибка 1833 mysql

Foreword:

The error occurred when an interface of the tornado project operated the mysql data table, and it was reported at the time of insertion: pymysql.err.InternalError: (1364, «Field ‘up_user_id’ doesn’t have a default value»), it is obvious that a default value is missing during insertion , But in principle, this field does not need to be transmitted, so it needs to be modified as an auto-increment field; then the database field is modified when it is auto-incremented: 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’, which involves the referenced foreign key, and then the alter drop foreign prompt is ok, and then I modify it and still give me back 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 ‘, which is a bit awkward;

Detailed analysis:

ERROR: 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’;

Reason: The field up_user_id I changed was used as a foreign key by ‘ihome.ih_order_info’, but I mentioned alter drop foregin before. Why can’t I modify it after deleting the foreign key? It is necessary to modify the table referenced by the foreign key. The two tables are locked to modify the foreign key mechanism. The key point is that there is no lock table when the foreign key is deleted. The foreign key is not actually deleted, so the modification is not successful;

Ih_order_info table creation statement:

 ih_order_info | CREATE TABLE `ih_order_info` (
  `oi_order_id` bigint (20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'order id',
     `oi_user_id` bigint (20) unsigned NOT NULL COMMENT 'user id',
     `oi_house_id` bigint (20) unsigned NOT NULL COMMENT 'house id',
     `oi_begin_date` date NOT NULL COMMENT 'Check in time',
     `oi_end_data` date NOT NULL COMMENT 'Leave time',
     `oi_days` int (10) unsigned NOT NULL COMMENT 'number of nights',
     `oi_house_price` int (10) unsigned NOT NULL COMMENT 'House unit price, unit share',
     `oi_amount` int (10) unsigned NOT NULL COMMENT 'Order amount, unit points',
     `oi_status` tinyint (4) NOT NULL DEFAULT '0' COMMENT 'Order status, 0- pending order, 1- pending payment, 2- paid, 3- pending evaluation, 4- completed, 5- canceled, 6 -Rejected order ',
     `oi_comment` text COMMENT 'Order comment',
     `oi_utime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'update time',
     `oi_citme` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'creation time',
  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 = 'Order Form' |

Solution steps:

1. Lock two tables:

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

2. The result of the checklist being locked:

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. Delete the foreign key constraint:

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

Here you can also check whether the foreign key is cleared by checking the table creation process:

show create table ih_order_info;
 -I remember I checked in real time that the foreign key 1 is gone;
ih_order_info | CREATE TABLE `ih_order_info` (
     `oi_order_id` bigint (20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'order id',
     `oi_user_id` bigint (20) unsigned NOT NULL COMMENT 'user id',
     `oi_house_id` bigint (20) unsigned NOT NULL COMMENT 'house id',
     `oi_begin_date` date NOT NULL COMMENT 'Check in time',
     `oi_end_data` date NOT NULL COMMENT 'Leave time',
     `oi_days` int (10) unsigned NOT NULL COMMENT 'number of nights',
     `oi_house_price` int (10) unsigned NOT NULL COMMENT 'House unit price, unit share',
     `oi_amount` int (10) unsigned NOT NULL COMMENT 'Order amount, unit points',
     `oi_status` tinyint (4) NOT NULL DEFAULT '0' COMMENT 'Order status, 0- pending order, 1- pending payment, 2- paid, 3- pending evaluation, 4- completed, 5- canceled, 6 -Rejected order ',
     `oi_comment` text COMMENT 'Order comment',
     `oi_utime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'update time',
     `oi_citme` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'creation time',
  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 = 'Order Form' |

4. Modify the field that needs to be modified (that is, the field where the foreign key is referenced);

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

5. Add the foreign key back:

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

6. Unlock the two tables:

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

Then the operation interface in python is successful; the problem is solved;

Поскольку внешние ключи должны соответствовать типу данных столбца, который они ссылаются, имеет смысл, что изменение типа данных столбца может нарушить внешний ключ, который ссылается на него.

Кажется, что у MySQL есть защита от этого и отрицает попытку ИЗМЕНИТЬ столбец. Но он недостаточно умен, чтобы проверить, действительно ли конкретная модификация будет изменять тип данных. Он просто отрицает любую попытку изменить этот столбец.

Вы можете обойти это, временно отключив проверку внешнего ключа. Я воссоздал ваш стол и подтвердил его:

mysql> ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT;
ERROR 1833 (HY000): Cannot change column 'person_id': used in a foreign key constraint 'favorite_food_ibfk_1' of table 'test.favorite_food'

mysql> set foreign_key_checks=0;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> set foreign_key_checks=1;