文章/答案/技术大牛

发布

社区首页 >问答首页 >MySQL:错误代码1215不能添加外键约束。我没有重复的主键&所有数据类型都匹配

问MySQL:错误代码1215不能添加外键约束。我没有重复的主键&所有数据类型都匹配
EN

Stack Overflow用户

提问于 2018-01-22 01:33:29

回答 2查看 37关注 0票数 0

我是一名学生，我可能已经为这个问题挣扎了大约几个小时，检查MySQL语法在线帮助，其他堆叠溢出问题，以及我的教科书。我已经反复修改我的代码了，我完全被搞糊涂了。

我一直试图在MySQL上使用前向工程师特性，但是在标题为“LabT3”的表上，我一直收到1215错误消息:无法添加外键约束。我会把代码贴在下面。正如我在标题中所说的，我已经检查过，以确保我没有重复的主键，并且我所有属性上的数据类型在表到表之间都是匹配的。我不知道我做错了什么。

这是密码。如有任何投入，将不胜感激：

-- MySQL Workbench Forward Engineering

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';

-- -----------------------------------------------------
-- Schema Amber's_Clinic_ER_Diagram
-- -----------------------------------------------------
-- Lab 2
-- Amber Lewis
-- Entity-Relationship Diagram for sample Clinic

-- -----------------------------------------------------
-- Schema Amber's_Clinic_ER_Diagram
--
-- Lab 2
-- Amber Lewis
-- Entity-Relationship Diagram for sample Clinic
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `Amber's_Clinic_ER_Diagram` DEFAULT CHARACTER SET utf8 ;
USE `Amber's_Clinic_ER_Diagram` ;

-- -----------------------------------------------------
-- Table `Amber's_Clinic_ER_Diagram`.`DoctorT2`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Amber's_Clinic_ER_Diagram`.`DoctorT2` (
  `DoctorID` VARCHAR(20) NOT NULL,
  `DoctorName` VARCHAR(50) NOT NULL,
  `Dept` VARCHAR(20) NOT NULL,
  PRIMARY KEY (`DoctorID`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Amber's_Clinic_ER_Diagram`.`PatientT1`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Amber's_Clinic_ER_Diagram`.`PatientT1` (
  `PID` VARCHAR(5) NOT NULL,
  `Name` VARCHAR(20) NOT NULL,
  `Age` INT NULL,
  `Weight` VARCHAR(10) NULL,
  `Gender` VARCHAR(6) NOT NULL,
  `Address` VARCHAR(50) NULL,
  `PhoneNo` INT NULL,
  `Disease` VARCHAR(50) NOT NULL,
  `DoctorID` VARCHAR(20) NOT NULL,
  PRIMARY KEY (`PID`, `DoctorID`),
  INDEX `fk_Patient - T1_Doctor - T2_idx` (`DoctorID` ASC),
  CONSTRAINT `fk_Patient - T1_Doctor - T2`
    FOREIGN KEY (`DoctorID`)
    REFERENCES `Amber's_Clinic_ER_Diagram`.`DoctorT2` (`DoctorID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Amber's_Clinic_ER_Diagram`.`OutpatientT5`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Amber's_Clinic_ER_Diagram`.`OutpatientT5` (
  `PID` VARCHAR(5) NOT NULL,
  `Date` DATE NOT NULL,
  `LabNo` INT NOT NULL,
  PRIMARY KEY (`PID`, `Date`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Amber's_Clinic_ER_Diagram`.`RoomT6`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Amber's_Clinic_ER_Diagram`.`RoomT6` (
  `RoomNo` INT NOT NULL,
  `RoomType` VARCHAR(15) NOT NULL,
  `Status` VARCHAR(15) NOT NULL,
  PRIMARY KEY (`RoomNo`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Amber's_Clinic_ER_Diagram`.`InpatientT4`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Amber's_Clinic_ER_Diagram`.`InpatientT4` (
  `PID` VARCHAR(5) NOT NULL,
  `RoomNo` INT NOT NULL,
  `DateOfAdm` DATE NOT NULL,
  `DateOfDis` DATE NOT NULL,
  `Advance` INT NOT NULL,
  `LabNo` INT NOT NULL,
  PRIMARY KEY (`PID`, `RoomNo`, `DateOfAdm`),
  INDEX `fk_InpatientT4_RoomT61_idx` (`RoomNo` ASC),
  CONSTRAINT `fk_InpatientT4_RoomT61`
    FOREIGN KEY (`RoomNo`)
    REFERENCES `Amber's_Clinic_ER_Diagram`.`RoomT6` (`RoomNo`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Amber's_Clinic_ER_Diagram`.`LabT3`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Amber's_Clinic_ER_Diagram`.`LabT3` (
  `LabNo` INT NOT NULL,
  `PID` VARCHAR(5) NOT NULL,
  `Weight` VARCHAR(10) NULL,
  `DoctorID` VARCHAR(20) NOT NULL,
  `Date` DATE NOT NULL,
  `Category` VARCHAR(20) NOT NULL,
  `PatientType` VARCHAR(30) NOT NULL,
  `Amount` VARCHAR(15) NOT NULL,
  PRIMARY KEY (`LabNo`, `PID`),
  INDEX `fk_LabT3_DoctorT2_idx` (`DoctorID` ASC),
  INDEX `fk_LabT3_OutpatientT5_idx` (`PID` ASC),
  CONSTRAINT `fk_LabT3_DoctorID`
    FOREIGN KEY (`DoctorID`)
    REFERENCES `Amber's_Clinic_ER_Diagram`.`DoctorT2` (`DoctorID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_LabT3_PID`
    FOREIGN KEY (`PID`)
    REFERENCES `Amber's_Clinic_ER_Diagram`.`OutpatientT5` (`PID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_LabT3_InpatientT41`
    FOREIGN KEY (`LabNo`)
    REFERENCES `Amber's_Clinic_ER_Diagram`.`InpatientT4` (`LabNo`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Amber's_Clinic_ER_Diagram`.`BillT7`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Amber's_Clinic_ER_Diagram`.`BillT7` (
  `BillNo` INT NOT NULL,
  `PID` VARCHAR(5) NOT NULL,
  `PatientType` VARCHAR(30) NOT NULL,
  `DoctorCharge` INT NOT NULL,
  `MedicineCharge` INT NOT NULL,
  `RoomCharge` INT NOT NULL,
  `OperationCharge` INT NOT NULL,
  `NursingCharge` INT NOT NULL,
  `LOS` INT NOT NULL,
  `Advance` INT NOT NULL,
  `HealthCard` VARCHAR(30) NOT NULL,
  `Lab Charge` INT NOT NULL,
  `Bill` INT NOT NULL,
  PRIMARY KEY (`BillNo`, `PID`),
  INDEX `fk_BillT7_OutpatientT51_idx` (`PID` ASC),
  CONSTRAINT `fk_BillT7_OutpatientT51`
    FOREIGN KEY (`PID`)
    REFERENCES `Amber's_Clinic_ER_Diagram`.`OutpatientT5` (`PID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


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

mysql

sql

foreign-keys

constraints

mysql-workbench

回答 2

Stack Overflow用户

回答已采纳

发布于 2018-01-22 05:28:02

这里，我已经更改了表Lab3和InpatientT4。

Lab3是主键LabNo的主表，因此要在InpatientT4中添加外键关系，并删除Lab3中的外键引用。

-- MySQL Workbench Forward Engineering

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';

-- -----------------------------------------------------
-- Schema Amber's_Clinic_ER_Diagram
-- -----------------------------------------------------
-- Lab 2
-- Amber Lewis
-- Entity-Relationship Diagram for sample Clinic

-- -----------------------------------------------------
-- Schema Amber's_Clinic_ER_Diagram
--
-- Lab 2
-- Amber Lewis
-- Entity-Relationship Diagram for sample Clinic
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `Amber's_Clinic_ER_Diagram` DEFAULT CHARACTER SET utf8 ;
USE `Amber's_Clinic_ER_Diagram` ;

-- -----------------------------------------------------
-- Table `Amber's_Clinic_ER_Diagram`.`DoctorT2`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Amber's_Clinic_ER_Diagram`.`DoctorT2` (
  `DoctorID` VARCHAR(20) NOT NULL,
  `DoctorName` VARCHAR(50) NOT NULL,
  `Dept` VARCHAR(20) NOT NULL,
  PRIMARY KEY (`DoctorID`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Amber's_Clinic_ER_Diagram`.`PatientT1`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Amber's_Clinic_ER_Diagram`.`PatientT1` (
  `PID` VARCHAR(5) NOT NULL,
  `Name` VARCHAR(20) NOT NULL,
  `Age` INT NULL,
  `Weight` VARCHAR(10) NULL,
  `Gender` VARCHAR(6) NOT NULL,
  `Address` VARCHAR(50) NULL,
  `PhoneNo` INT NULL,
  `Disease` VARCHAR(50) NOT NULL,
  `DoctorID` VARCHAR(20) NOT NULL,
  PRIMARY KEY (`PID`, `DoctorID`),
  INDEX `fk_Patient - T1_Doctor - T2_idx` (`DoctorID` ASC),
  CONSTRAINT `fk_Patient - T1_Doctor - T2`
    FOREIGN KEY (`DoctorID`)
    REFERENCES `Amber's_Clinic_ER_Diagram`.`DoctorT2` (`DoctorID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Amber's_Clinic_ER_Diagram`.`OutpatientT5`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Amber's_Clinic_ER_Diagram`.`OutpatientT5` (
  `PID` VARCHAR(5) NOT NULL,
  `Date` DATE NOT NULL,
  `LabNo` INT NOT NULL,
  PRIMARY KEY (`PID`, `Date`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Amber's_Clinic_ER_Diagram`.`RoomT6`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Amber's_Clinic_ER_Diagram`.`RoomT6` (
  `RoomNo` INT NOT NULL,
  `RoomType` VARCHAR(15) NOT NULL,
  `Status` VARCHAR(15) NOT NULL,
  PRIMARY KEY (`RoomNo`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `Amber's_Clinic_ER_Diagram`.`LabT3`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Amber's_Clinic_ER_Diagram`.`LabT3` (
  `LabNo` INT NOT NULL,
  `PID` VARCHAR(5) NOT NULL,
  `Weight` VARCHAR(10) NULL,
  `DoctorID` VARCHAR(20) NOT NULL,
  `Date` DATE NOT NULL,
  `Category` VARCHAR(20) NOT NULL,
  `PatientType` VARCHAR(30) NOT NULL,
  `Amount` VARCHAR(15) NOT NULL,
  PRIMARY KEY (`LabNo`, `PID`),
  INDEX `fk_LabT3_DoctorT2_idx` (`DoctorID` ASC),
  INDEX `fk_LabT3_OutpatientT5_idx` (`PID` ASC),
  CONSTRAINT `fk_LabT3_DoctorID`
    FOREIGN KEY (`DoctorID`)
    REFERENCES `Amber's_Clinic_ER_Diagram`.`DoctorT2` (`DoctorID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_LabT3_PID`
    FOREIGN KEY (`PID`)
    REFERENCES `Amber's_Clinic_ER_Diagram`.`OutpatientT5` (`PID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION) 
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Amber's_Clinic_ER_Diagram`.`InpatientT4`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Amber's_Clinic_ER_Diagram`.`InpatientT4` (
  `PID` VARCHAR(5) NOT NULL,
  `RoomNo` INT NOT NULL,
  `DateOfAdm` DATE NOT NULL,
  `DateOfDis` DATE NOT NULL,
  `Advance` INT NOT NULL,
  `LabNo` INT NOT NULL,
  PRIMARY KEY (`PID`, `RoomNo`, `DateOfAdm`),
  INDEX `fk_InpatientT4_RoomT61_idx` (`RoomNo` ASC),
  CONSTRAINT `fk_InpatientT4_RoomT61`
    FOREIGN KEY (`RoomNo`)
    REFERENCES `Amber's_Clinic_ER_Diagram`.`RoomT6` (`RoomNo`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
 CONSTRAINT `fk_LabT3_InpatientT41`
    FOREIGN KEY (`LabNo`)
    REFERENCES `Amber's_Clinic_ER_Diagram`.`LabT3` (`LabNo`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `Amber's_Clinic_ER_Diagram`.`BillT7`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Amber's_Clinic_ER_Diagram`.`BillT7` (
  `BillNo` INT NOT NULL,
  `PID` VARCHAR(5) NOT NULL,
  `PatientType` VARCHAR(30) NOT NULL,
  `DoctorCharge` INT NOT NULL,
  `MedicineCharge` INT NOT NULL,
  `RoomCharge` INT NOT NULL,
  `OperationCharge` INT NOT NULL,
  `NursingCharge` INT NOT NULL,
  `LOS` INT NOT NULL,
  `Advance` INT NOT NULL,
  `HealthCard` VARCHAR(30) NOT NULL,
  `Lab Charge` INT NOT NULL,
  `Bill` INT NOT NULL,
  PRIMARY KEY (`BillNo`, `PID`),
  INDEX `fk_BillT7_OutpatientT51_idx` (`PID` ASC),
  CONSTRAINT `fk_BillT7_OutpatientT51`
    FOREIGN KEY (`PID`)
    REFERENCES `Amber's_Clinic_ER_Diagram`.`OutpatientT5` (`PID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


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

票数 0

Stack Overflow用户

发布于 2018-01-22 04:38:11

调试的一种方法是删除每个FK语句，直到脚本运行为止。这会告诉你到底是哪个FK是问题所在。这是调试DDL脚本的一种很好的技术；一直到它工作为止，这样您就可以找到确切的错误原因。

在这样做时，您可能会发现fk_LabT3_InpatientT41是不正确的。您正在为Amber's_Clinic_ER_Diagram.InpatientT4 (LabNo)中的非键字段创建外键.

我想你把FK的声明放在了错误的桌子上。您可能希望FK指向LabT3，而不是InpatientT4。

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/48373609

复制

相似问题

问MySQL:错误代码1215不能添加外键约束。我没有重复的主键&所有数据类型都匹配
EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL:错误代码1215不能添加外键约束。我没有重复的主键&所有数据类型都匹配EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL:错误代码1215不能添加外键约束。我没有重复的主键&所有数据类型都匹配
EN