MySQL无法添加外键约束。
MySQL无法添加外键约束。
所以我正在尝试根据项目要求向我的数据库添加外键约束,前几次在不同的表上都成功了,但是在尝试添加外键约束时,有两个表出现了错误。\n我得到的错误消息是:\n
\n错误 1215 (HY000): 无法添加外键约束\n
\n这是我用于创建表的SQL语句,出错的两个表是Patient
和Appointment
。\n
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;
MySQL无法添加外键约束的原因可能有以下几点:
1. 引擎(Engine)应该相同,例如都是InnoDB引擎。
2. 数据类型(Datatype)应该相同,并且长度也应该相同,例如都是VARCHAR(20)。
3. 排序规则(Collation)的列字符集应该相同,即都是utf8。需要注意的是,即使表的排序规则相同,列的排序规则仍然可能不同。
4. 外键应该引用参考表中唯一的字段(通常是主键)。
解决这个问题的方法可能有以下几种:
1. 将引用表的列设置为唯一(unique),即使它已经是主键(Primary Key)。
2. 检查和更改排序规则的方法,可以参考这个链接:stackoverflow.com/questions/1294117/…。
3. 检查和更改数据类型的方法,例如将BigInt(20)改为Int(10)。
对于遇到这个问题的用户来说,他们可能会尝试上述的解决方法,但由于数据库结构的特殊性或者其他原因,可能仍然无法添加外键约束。
MySQL无法添加外键约束的问题通常是由以下原因引起的:
1. 子列的数据类型必须与父列的数据类型完全匹配。例如,如果`medicalhistory.MedicalHistoryID`是一个`INT`类型,那么`Patient.MedicalHistory`也必须是一个`INT`类型,而不是`SMALLINT`类型。
2. 在运行DDL之前,应该运行查询`set foreign_key_checks=0`,这样可以按任意顺序创建表,而不需要先创建所有父表再创建相关的子表。
除了上述原因,还有一些其他的解决方法:
- 检查表之间的字符集和校对规则是否匹配。如果两个表的字符集和校对规则不一致,也会导致无法添加外键约束。
- 确保在创建表时指定正确的数据类型和长度。例如,如果子列是无符号的`INT`类型,那么在创建表时必须将其设置为无符号的。
- 确保父表的列已经创建了索引。如果父表的列没有索引,也会导致无法添加外键约束。
- 运行`SHOW WARNINGS`命令可以提供更详细的错误信息。
当无法添加MySQL外键约束时,可以通过检查数据类型匹配、设置`foreign_key_checks=0`、检查字符集和校对规则、指定正确的数据类型和长度、创建索引等方法来解决问题。