MySQL错误1215:不能添加外键约束 [英] MySQL Error 1215: Cannot add foreign key constraint
问题描述
我正在尝试将我的新架构转发到我的数据库服务器上,但我无法弄清楚为什么会出现此错误。我试图在这里搜索答案,但是我发现的一切都说要将数据库引擎设置为Innodb或确保我试图用作外键的键是他们自己的表中的主键。如果我没有弄错的话,我已经完成了这两件事情。你可以提供任何其他的帮助吗?
$ b $ pre $执行SQL脚本在服务器
错误:错误1215年:无法添加外键约束
- ----------------------------------- ------------------
- 表`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个失败
$ b $
pre $
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)
`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
我猜测 也许父表中的列是 两个表中的数据类型完全相同。 I am trying to forward engineer my new schema onto my db server, but I can't figure out why I am getting this error. I've tried to search for the answer here, but everything I've found has said to either set the db 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. Any other help you guys could offer? SQL script execution finished: statements: 7 succeeded, 1 failed Here is the SQL for the parent tables.
I'm guessing that Perhaps the columns in the parent tables are They need to be exactly the same data type in both tables. 这篇关于MySQL错误1215:不能添加外键约束的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋! Staff.Emp_ID
与 Clients_has_Staff.Clients_Case_Number
和 Clients_has_Staff.Staff_Emp_ID
。
不完全相同
INT UNSIGNED
?
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
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
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
?