MySQL不能添加外键约束 [英] MySQL Cannot Add Foreign Key Constraint
本文介绍了MySQL不能添加外键约束的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我得到的错误信息是：
lockquote
错误1215（HY000）：无法添加外键约束$ / $>
这是我用来创建表的SQL，两个违规表是 Patient 和约会。
SET @OLD_UNIQUE_CHECKS = @@ UNIQUE_CHECKS，UNIQUE_CHECKS = 0;
SET @OLD_FOREIGN_KEY_CHECKS = @@ FOREIGN_KEY_CHECKS，FOREIGN_KEY_CHECKS = 1;
SET @OLD_SQL_MODE = @@ SQL_MODE，SQL_MODE ='TRADITIONAL，ALLOW_INVALID_DATES';
如果不存在，则创建模式`doctorsoffice`默认字符集utf8;
USE`doctorsoffice`;
- ---------------------------------------- -------------
- 表`doctorsoffice`.`doctor`
- ----------------- ------------------------------------
DROP TABLE如果出现`doctorsoffice`.`doctor `;
CREATE TABLE IF NOT EXISTS`doctoroffice`.`doctor`（
`DoctorID` INT（11）NOT NULL AUTO_INCREMENT，
`FName` VARCHAR（20）NULL DEFAULT NULL ，
`LName` VARCHAR（20）NULL DEFAULT NULL，$ b $``Gender` VARCHAR（1）NULL DEFAULT NULL，$ b $`Special`VARCHAR（40）NOT NULL DEFAULT'General Practitioner'，
UNIQUE INDEX`DoctorID`（`DoctorID` ASC），
PRIMARY KEY（`DoctorID`））
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
- ------------------------------------ -----------------
- 表`doctorsoffice`.`medicalhistory`
- ------------- ----------------------------------------
DROP TABLE如果存在`doctorsoffice `.`medicalhistory`;
CREATE TABLE IF NOT EXISTS`doctorsoffice`.`medicalhistory`（
`MedicalHistoryID` INT（11）NOT NULL AUTO_INCREMENT，
`Allergies` TEXT NULL DEFAULT NULL，
`Medications` TEXT NULL DEFAULT NULL，
`ExistingConditions` TEXT NULL DEFAULT NULL，
`Misc` TEXT NULL DEFAULT NULL，
UNIQUE INDEX`MedicalHistoryID`（`MedicalHistoryID` ASC），
PRIMARY KEY（`MedicalHistoryID`））
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
- ------------------------------------ -----------------
- 表doctorsoffice`.`Patient`
- ------------- ----------------------------------------
DROP TABLE如果存在`doctorsoffice ``病人`;
CREATE TABLE IF NOT EXISTS`doctorsoffice`.`Patient`（
`PatientID` INT unsigned NOT NULL AUTO_INCREMENT，
`FName` VARCHAR（30）NULL，
`LName` VARCHAR（45）NULL，
`Gender` CHAR NULL，
`DOB` DATE NULL，
`SSN` DOUBLE NULL，
`MedicalHistory` smallint（5 ）unsigned NOT NULL，
`PrimaryPhysician` smallint（5）unsigned NOT NULL，
PRIMARY KEY（`PatientID`），
UNIQUE INDEX`PatientID_UNIQUE`（`PatientID` ASC），
CONSTRAINT`FK_MedicalHistory`
FOREIGN KEY（`MEdicalHistory`）
REFERENCES`doctorsoffice`.`medicalhistory`（`MedicalHistoryID`）
ON DELETE CASCADE
ON UPDATE CASCADE，
CONSTRAINT`FK_PrimaryPhysician`
FOREIGN KEY（`PrimaryPhysician`）
REFERENCES`doctorsoffice`.`doctor`（`DoctorID`）
ON DELETE CASCADE
ON UPDATE CASCADE）
ENGINE = InnoDB;
- ------------------------------------ -----------------
- 表`doctorsoffice`.`Appointment`
- ------------- ----------------------------------------
DROP TABLE如果存在`doctorsoffice ```预约';
CREATE TABLE if NOT EXISTS`doctorsoffice`.`Appointment`（
`AppointmentID` smallint（5）unsigned NOT NULL AUTO_INCREMENT，
`Date` DATE NULL，
'Time'TIME NULL，
`Patient` smallint（5）unsigned NOT NULL，
`Doctor` smallint（5）unsigned NOT NULL，
PRIMARY KEY（`AppointmentID`），
UNIQUE INDEX`AppointmentID_UNIQUE`（`AppointmentID` ASC），
CONSTRAINT`FK_Patient`
FOREIGN KEY（`Patient`）
REFERENCES`doctorsoffice`.`Patient`（`PatientID` ）
ON DELETE CASCADE
ON UPDATE CASCADE，
CONSTRAINT`FK_Doctor`
FOREIGN KEY（`Doctor`）
REFERENCES`doctorsoffice`.`doctor`（'DoctorID `）
ON DELETE CASCADE
ON UPDATE CASCADE）
ENGINE = InnoDB;
- ------------------------------------ -----------------
- 表`doctorsoffice`.InsuranceCompany`
- ------------- ----------------------------------------
DROP TABLE如果存在`doctorsoffice ```保险公司`;
CREATE TABLE NOT NOT EXISTS`doctorsoffice`.`InsuranceCompany`（
`InsuranceID` smallint（5）NOT NULL AUTO_INCREMENT，
`Name` VARCHAR（50）NULL，
`Phone` DOUBLE NULL，
PRIMARY KEY（`InsuranceID`），
UNIQUE INDEX`InsuranceID_UNIQUE`（`InsuranceID` ASC））
ENGINE = InnoDB;
- ------------------------------------ -----------------
- 表`doctorsoffice`.`PatientInsurance`
- ------------- ----------------------------------------
DROP TABLE如果存在`doctorsoffice ``患者保险';
CREATE TABLE if NOT EXISTS`doctorsoffice`.`PatientInsurance`（
`PolicyHolder` smallint（5）NOT NULL，
`InsuranceCompany` smallint（5）NOT NULL，
`CoPay` INT NOT NULL DEFAULT 5，
`PolicyNumber` smallint（5）NOT NULL AUTO_INCREMENT，
PRIMARY KEY（`PolicyNumber`），
UNIQUE INDEX`PolicyNumber_UNIQUE`（` PolicyNumber` ASC），
CONSTRAINT`FK_PolicyHolder`
FOREIGN KEY（`PolicyHolder`）
REFERENCES`doctorsoffice`.`Patient`（`PatientID`）
ON DELETE CASCADE
ON UPDATE CASCADE，
CONSTRAINT`FK_InsuranceCompany`
FOREIGN KEY（`InsuranceCompany`）
REFERENCES`doctorsoffice`.InsuranceCompany`（`InsuranceID`）
ON DELETE CASCADE
ON UPDATE CASCADE）
ENGINE = InnoDB;
USE`doctorsoffice`;
SET SQL_MODE = @ OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS = @ OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS = @ OLD_UNIQUE_CHECKS;
解决方案
>
SHOW ENGINE INNODB STATUS;
然后查看最新的FOREIGN KEY ERROR 部分。
子列的数据类型必须完全匹配父列。例如，由于 medicalhistory.MedicalHistoryID 是一个 INT ， Patient.MedicalHistory 也需要是 INT ，而不是 SMALLINT 。
另外，在运行DDL之前，您应该运行查询 set foreign_key_checks = 0 ，这样您就可以以任意顺序创建表，而不需要创建所有父有关子表之前的表。
So I'm trying to add Foreign Key constraints to my database as a project requirement and it worked the first time or two on different tables, but I have two tables on which I get an error when trying to add the Foreign Key Constraints. The error message that I get is:
ERROR 1215 (HY000): Cannot add foreign key constraint
This is the SQL I'm using to create the tables, the two offending tables are Patient and Appointment.
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=1;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
CREATE SCHEMA IF NOT EXISTS `doctorsoffice` DEFAULT CHARACTER SET utf8 ;
USE `doctorsoffice` ;
-- -----------------------------------------------------
-- Table `doctorsoffice`.`doctor`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `doctorsoffice`.`doctor` ;
CREATE TABLE IF NOT EXISTS `doctorsoffice`.`doctor` (
`DoctorID` INT(11) NOT NULL AUTO_INCREMENT ,
`FName` VARCHAR(20) NULL DEFAULT NULL ,
`LName` VARCHAR(20) NULL DEFAULT NULL ,
`Gender` VARCHAR(1) NULL DEFAULT NULL ,
`Specialty` VARCHAR(40) NOT NULL DEFAULT 'General Practitioner' ,
UNIQUE INDEX `DoctorID` (`DoctorID` ASC) ,
PRIMARY KEY (`DoctorID`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `doctorsoffice`.`medicalhistory`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `doctorsoffice`.`medicalhistory` ;
CREATE TABLE IF NOT EXISTS `doctorsoffice`.`medicalhistory` (
`MedicalHistoryID` INT(11) NOT NULL AUTO_INCREMENT ,
`Allergies` TEXT NULL DEFAULT NULL ,
`Medications` TEXT NULL DEFAULT NULL ,
`ExistingConditions` TEXT NULL DEFAULT NULL ,
`Misc` TEXT NULL DEFAULT NULL ,
UNIQUE INDEX `MedicalHistoryID` (`MedicalHistoryID` ASC) ,
PRIMARY KEY (`MedicalHistoryID`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `doctorsoffice`.`Patient`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `doctorsoffice`.`Patient` ;
CREATE TABLE IF NOT EXISTS `doctorsoffice`.`Patient` (
`PatientID` INT unsigned NOT NULL AUTO_INCREMENT ,
`FName` VARCHAR(30) NULL ,
`LName` VARCHAR(45) NULL ,
`Gender` CHAR NULL ,
`DOB` DATE NULL ,
`SSN` DOUBLE NULL ,
`MedicalHistory` smallint(5) unsigned NOT NULL,
`PrimaryPhysician` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`PatientID`) ,
UNIQUE INDEX `PatientID_UNIQUE` (`PatientID` ASC) ,
CONSTRAINT `FK_MedicalHistory`
FOREIGN KEY (`MEdicalHistory` )
REFERENCES `doctorsoffice`.`medicalhistory` (`MedicalHistoryID` )
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `FK_PrimaryPhysician`
FOREIGN KEY (`PrimaryPhysician` )
REFERENCES `doctorsoffice`.`doctor` (`DoctorID` )
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `doctorsoffice`.`Appointment`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `doctorsoffice`.`Appointment` ;
CREATE TABLE IF NOT EXISTS `doctorsoffice`.`Appointment` (
`AppointmentID` smallint(5) unsigned NOT NULL AUTO_INCREMENT ,
`Date` DATE NULL ,
`Time` TIME NULL ,
`Patient` smallint(5) unsigned NOT NULL,
`Doctor` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`AppointmentID`) ,
UNIQUE INDEX `AppointmentID_UNIQUE` (`AppointmentID` ASC) ,
CONSTRAINT `FK_Patient`
FOREIGN KEY (`Patient` )
REFERENCES `doctorsoffice`.`Patient` (`PatientID` )
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `FK_Doctor`
FOREIGN KEY (`Doctor` )
REFERENCES `doctorsoffice`.`doctor` (`DoctorID` )
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `doctorsoffice`.`InsuranceCompany`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `doctorsoffice`.`InsuranceCompany` ;
CREATE TABLE IF NOT EXISTS `doctorsoffice`.`InsuranceCompany` (
`InsuranceID` smallint(5) NOT NULL AUTO_INCREMENT ,
`Name` VARCHAR(50) NULL ,
`Phone` DOUBLE NULL ,
PRIMARY KEY (`InsuranceID`) ,
UNIQUE INDEX `InsuranceID_UNIQUE` (`InsuranceID` ASC) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `doctorsoffice`.`PatientInsurance`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `doctorsoffice`.`PatientInsurance` ;
CREATE TABLE IF NOT EXISTS `doctorsoffice`.`PatientInsurance` (
`PolicyHolder` smallint(5) NOT NULL ,
`InsuranceCompany` smallint(5) NOT NULL ,
`CoPay` INT NOT NULL DEFAULT 5 ,
`PolicyNumber` smallint(5) NOT NULL AUTO_INCREMENT ,
PRIMARY KEY (`PolicyNumber`) ,
UNIQUE INDEX `PolicyNumber_UNIQUE` (`PolicyNumber` ASC) ,
CONSTRAINT `FK_PolicyHolder`
FOREIGN KEY (`PolicyHolder` )
REFERENCES `doctorsoffice`.`Patient` (`PatientID` )
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `FK_InsuranceCompany`
FOREIGN KEY (`InsuranceCompany` )
REFERENCES `doctorsoffice`.`InsuranceCompany` (`InsuranceID` )
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
USE `doctorsoffice` ;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
解决方案
To find the specific error run this:
SHOW ENGINE INNODB STATUS;
And look in the LATEST FOREIGN KEY ERROR section.
The data type for the child column must match the parent column exactly. For example, since medicalhistory.MedicalHistoryID is an INT, Patient.MedicalHistory also needs to be an INT, not a SMALLINT.
Also, you should run the query set foreign_key_checks=0 before running the DDL so you can create the tables in an arbitrary order rather than needing to create all parent tables before the relevant child tables.
这篇关于MySQL不能添加外键约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
