Error code 1833 Cannot change column used in a foreign

Shulou Source: shulou.com Published: 2022-06-01 08:08:13 09月30日 Update

Recently modified the field length in the mysql database table. Execute the changed sql statement:

ALTER TABLE server_list MODIFY COLUMN server_lip CHAR (25)

Error message:

1 queries executed, 0 success, 1 errors, 0 warnings

Enquiries: alter table server_list modify column server_lip char (25)

Error code: 1833

Cannot change column 'server_lip': used in a foreign key constraint' server_history_ibfk_1' of table 'OMServer.server_history'

Reason: the server_lip field in the server_ list table is the foreign key of the server_history table, and the server_history table statement:

CREATE TABLE server_history (

ID int (11) NOT NULL AUTO_INCREMENT COMMENT 'primary key ID'

History_id int (11) NOT NULL COMMENT 'event ID'

History_ip char (15) NOT NULL COMMENT 'event IP address'

History_user char (15) NOT NULL COMMENT 'event username'

History_datetime datetime NOT NULL COMMENT 'event time'

Db_datetime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'restocking time'

History_command char (255) NOT NULL COMMENT 'event Command'

PRIMARY KEY (ID)

KEY history_ip (history_ip)

CONSTRAINT server_history_ibfk_1 FOREIGN KEY (history_ip) REFERENCES server_list (server_lip)

) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8 COMMENT=' operation event table'

Resolution steps:

1. Lock table

LOCK TABLES server_history WRITE,server_list WRITE

two。 Check if it is locked

3. Release the server_history foreign key constraint

ALTER TABLE server_history DROP FOREIGN KEY server_history_ibfk_1

4. Change the server_ _ lip field length of the list table

ALTER TABLE server_list MODIFY COLUMN server_lip CHAR (25)

Check the table structure and find that the field length update has been completed.

5. New server_ foreign key constraint

ALTER TABLE server_history ADD CONSTRAINT server_history_ibfk_1 FOREIGN KEY (history_ip) REFERENCES server_list (server_lip)

6. Unlock

UNLOCK TABLES