MySQL 外键跳过检查失败-[错误代码 : 1025]

Question

我有下表定义:

CREATE TABLE IF NOT EXISTS `category` (
  `id` INT UNSIGNED NULL AUTO_INCREMENT,
  `parent_category_id` INT UNSIGNED NULL DEFAULT NULL COMMENT 'Parent category ID store in the same table',
  PRIMARY KEY (`id`),
  INDEX `fk_category_category_id_idx` (`parent_category_id` ASC),
  CONSTRAINT `fk_category_category_id`
    FOREIGN KEY (`parent_category_id`)
    REFERENCES `touriable`.`category` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB

我将运行以下 SQL:

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

ALTER TABLE category CHANGE parent_category_id parent_category_id INT;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

但是我收到了错误信息:

Error Code: 1025. Error on rename of './#sql-1260_d9b775' to './category' (errno: 150)  0.312 sec

我真的不知道为什么我不能运行 SQL。我试图跳过外键检查但仍然失败。为什么以及如何解决？

Answer 1

列类型必须匹配:INT 列不能是无符号 INT 列的外键。尝试:

ALTER TABLE category CHANGE parent_category_id parent_category_i INT UNSIGNED;