MySQL 错误 1215:无法添加外键约束

2021-11-20 00:00:00 foreign-keys mysql

我正在尝试将我的新架构转发到我的数据库服务器上，但我不知道为什么我会收到这个错误.

I am trying to forward engineer my new schema onto my database server, but I can't figure out why I am getting this error.

我试图在这里搜索答案，但我发现的所有内容都说要么将数据库引擎设置为 InnoDB，要么确保我尝试用作外键的键是主键他们自己的桌子.如果我没记错的话，这两件事我都做过.我还能做什么?

I've tried to search for the answer here, but everything I've found has said to either set the database engine to InnoDB or to make sure the keys I'm trying to use as a foreign key are primary keys in their own tables. I have done both of these things, if I'm not mistaken. What else can I do?

Executing SQL script in server

ERROR: Error 1215: Cannot add foreign key constraint

-- -----------------------------------------------------
-- Table `Alternative_Pathways`.`Clients_has_Staff`
-- -----------------------------------------------------


CREATE  TABLE IF NOT EXISTS `Alternative_Pathways`.`Clients_has_Staff` (
  `Clients_Case_Number` INT NOT NULL ,
  `Staff_Emp_ID` INT NOT NULL ,
  PRIMARY KEY (`Clients_Case_Number`, `Staff_Emp_ID`) ,
  INDEX `fk_Clients_has_Staff_Staff1_idx` (`Staff_Emp_ID` ASC) ,
  INDEX `fk_Clients_has_Staff_Clients_idx` (`Clients_Case_Number` ASC) ,
  CONSTRAINT `fk_Clients_has_Staff_Clients`
    FOREIGN KEY (`Clients_Case_Number` )
    REFERENCES `Alternative_Pathways`.`Clients` (`Case_Number` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Clients_has_Staff_Staff1`
    FOREIGN KEY (`Staff_Emp_ID` )
    REFERENCES `Alternative_Pathways`.`Staff` (`Emp_ID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB

SQL 脚本执行完成:语句:7 条成功，1 条失败

SQL script execution finished: statements: 7 succeeded, 1 failed

这是父表的 SQL.

CREATE  TABLE IF NOT EXISTS `Alternative_Pathways`.`Clients` (
  `Case_Number` INT NOT NULL ,
  `First_Name` CHAR(10) NULL ,
  `Middle_Name` CHAR(10) NULL ,
  `Last_Name` CHAR(10) NULL ,
  `Address` CHAR(50) NULL ,
  `Phone_Number` INT(10) NULL ,
  PRIMARY KEY (`Case_Number`) )
ENGINE = InnoDB

CREATE  TABLE IF NOT EXISTS `Alternative_Pathways`.`Staff` (
  `Emp_ID` INT NOT NULL ,
  `First_Name` CHAR(10) NULL ,
  `Middle_Name` CHAR(10) NULL ,
  `Last_Name` CHAR(10) NULL ,
  PRIMARY KEY (`Emp_ID`) )
ENGINE = InnoDB

推荐答案

我猜 Clients.Case_Number 和/或 Staff.Emp_ID 并不完全相同数据类型为 Clients_has_Staff.Clients_Case_Number 和 Clients_has_Staff.Staff_Emp_ID.

I'm guessing that Clients.Case_Number and/or Staff.Emp_ID are not exactly the same data type as Clients_has_Staff.Clients_Case_Number and Clients_has_Staff.Staff_Emp_ID.

也许父表中的列是INT UNSIGNED?

它们在两个表中的数据类型必须完全相同.

They need to be exactly the same data type in both tables.

相关文章