MySQL无法添加外键约束-Java 学习之路

228

所以我试图将外键约束添加到我的数据库作为项目需求，它在不同的表上第一次或第二次工作，但我有两个表，在尝试添加外键约束时我得到一个错误 . 我得到的错误消息是：

ERROR 1215（HY000）：无法添加外键约束

这是我用来创建表的SQL，两个有问题的表是 Patient 和 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;

20 回答

0

我遇到了这个问题，并且能够通过确保数据类型完全匹配来解决它 .

我正在使用SequelPro添加约束，默认情况下它将主键设为无符号 .

回复于 2024-04-20T03:15:04+08:00
0

我有同样的问题，解决方案很简单 . 解决方案：表中声明的外键不应设置为null .

reference：如果指定SET NULL操作，请确保未将子表中的列声明为NOT NULL . （ref）

回复于 2024-04-20T03:15:04+08:00
0

请确保两个表都是InnoDB格式 . 即使一个是MyISAM格式，那么外键约束也不会起作用 .

另外，另外一点是，两个字段应该是相同的类型 . 如果一个是INT，那么另一个也应该是INT . 如果一个是VARCHAR，另一个也应该是VARCHAR等 .

回复于 2024-04-20T03:15:04+08:00
597
检查以下规则：
- 首先检查是否为表名指定了名称
- 第二种正确的数据类型给外键？
回复于 2024-04-20T03:15:04+08:00
0
要查找特定错误，请执行以下操作：
```
SHOW ENGINE INNODB STATUS;
```
并查看 LATEST FOREIGN KEY ERROR 部分 .

子列的数据类型必须与父列完全匹配 . 例如，由于 medicalhistory.MedicalHistoryID 是 INT ， Patient.MedicalHistory 也需要是 INT ，而不是 SMALLINT .

此外，您应该在运行DDL之前运行查询 set foreign_key_checks=0 ，以便您可以按任意顺序创建表，而不需要在相关子表之前创建所有父表 .
回复于 2024-04-20T03:15:04+08:00
3

我将一个字段设置为“未签名”而另一个字段不设置 . 一旦我将两列都设置为无符号就可以了 .

回复于 2024-04-20T03:15:04+08:00
6
- Engine 应该相同，例如InnoDB的
- Datatype 应该相同，长度相同 . 例如VARCHAR（20）
- Collation 列charset应该是相同的 . 例如UTF8
  注意：即使您的表具有相同的排序规则，列仍然可以具有不同的排序 .
- Unique - 外键应引用引用表中唯一的字段（通常是主键） .
回复于 2024-04-20T03:15:04+08:00
0

尝试在外键上使用相同类型的主键 - int(11) - smallint(5) .

希望能帮助到你！

回复于 2024-04-20T03:15:04+08:00
15
确认两个表的字符编码和排序规则相同 .

在我自己的情况下，其中一个表使用 utf8 而另一个表使用 latin1 .

我有另一种情况，编码是相同的但排序规则不同 . 一个 utf8_general_ci 另一个 utf8_unicode_ci

您可以运行此命令来设置表的编码和排序规则 .
```
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
```
我希望这可以帮助别人 .
回复于 2024-04-20T03:15:04+08:00
10
要在表B中设置FOREIGN KEY，必须在表A中设置KEY .

在表A中：INDEX id （ id ）

然后在表B中，
```
CONSTRAINT `FK_id` FOREIGN KEY (`id`) REFERENCES `table-A` (`id`)
```
回复于 2024-04-20T03:15:04+08:00

111

我在“多对多”表中创建外键时遇到类似错误，其中主键由2个外键和另一个普通列组成 . 我通过更正引用的表名称即公司来解决问题，如下面的更正代码所示：

create table company_life_cycle__history -- (M-M)
(
company_life_cycle_id tinyint unsigned not null,
Foreign Key (company_life_cycle_id) references company_life_cycle(id) ON DELETE    CASCADE ON UPDATE CASCADE,
company_id MEDIUMINT unsigned not null,
Foreign Key (company_id) references company(id) ON DELETE CASCADE ON UPDATE CASCADE,
activity_on date NOT NULL,
PRIMARY KEY pk_company_life_cycle_history (company_life_cycle_id, company_id,activity_on),
created_on datetime DEFAULT NULL,
updated_on datetime DEFAULT NULL,
created_by varchar(50) DEFAULT NULL,
updated_by varchar(50) DEFAULT NULL
);

回复于 2024-04-20T03:15:04+08:00

0

我有两个外键用于不同的表但具有相同的键名的类似错误！我已重命名密钥，错误已经消失）

回复于 2024-04-20T03:15:04+08:00
0

有一个类似的错误，但在我的情况下，我错过了将pk声明为auto_increment .

以防它对任何人都有帮助

回复于 2024-04-20T03:15:04+08:00
5
我得到了同样的错误 . 我的原因是：
- 我通过复制整个数据库，通过phpmyadmin创建了数据库备份 .
- 我创建了一个新的数据库，其名称与旧数据库已选择它相同 .
- 我启动了一个SQL脚本来创建更新的表和数据 .
- 我收到了错误 . 当我禁用foreign_key_checks时 . 尽管数据库完全是空的 .
原因是：因为我使用phpmyadmin在重命名的数据库中创建了一些外键 - 使用数据库名称前缀创建的外键但未更新数据库名称前缀 . 所以在backup-db中仍有一些引用指向新创建的db .
回复于 2024-04-20T03:15:04+08:00
57

我的解决方案可能有点令人尴尬，并讲述为什么你有时应该看看你面前的东西而不是这些帖子的故事:)

之前我曾经遇到过一名前锋工程师，但失败了，所以这意味着我的数据库已经有了几张 table ，然后我一直试图修复外键关键失败，试图确保一切都很完美，但是它遇到了以前创建的表，所以它没有占上风 .

回复于 2024-04-20T03:15:04+08:00
4

检查两个表列上的签名 . 如果引用表列是SIGNED，则引用的表列也应该是SIGNED .

回复于 2024-04-20T03:15:04+08:00
0

此错误的另一个原因是当您的表或列包含保留keywords时：

有时人们会忘记这些 .

回复于 2024-04-20T03:15:04+08:00
-1
在我的例子中，有一个语法错误，MySQL控制台在运行查询时没有明确通知 . 但是，报告了 SHOW ENGINE INNODB STATUS 命令的 LATEST FOREIGN KEY ERROR 部分，
```
Syntax error close to:

  REFERENCES`role`(`id`) ON DELETE CASCADE) ENGINE = InnoDB DEFAULT CHARSET = utf8
```
我不得不在 REFERENCES 和 role 之间留下一个空格来使它工作 .
回复于 2024-04-20T03:15:04+08:00

注意：当我在数据库上进行一些研发时，下表是从某个站点获取的 . 因此命名约定不合适 .

For me, the problem was, my parent table had the different character set than that of the one which I was creating.

Parent Table (PRODUCTS)

products | CREATE TABLE `products` (
  `productCode` varchar(15) NOT NULL,
  `productName` varchar(70) NOT NULL,
  `productLine` varchar(50) NOT NULL,
  `productScale` varchar(10) NOT NULL,
  `productVendor` varchar(50) NOT NULL,
  `productDescription` text NOT NULL,
  `quantityInStock` smallint(6) NOT NULL,
  `buyPrice` decimal(10,2) NOT NULL,
  `msrp` decimal(10,2) NOT NULL,
  PRIMARY KEY (`productCode`),
  KEY `productLine` (`productLine`),
  CONSTRAINT `products_ibfk_1` FOREIGN KEY (`productLine`) REFERENCES `productlines` (`productLine`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Child Table which had a problem (PRICE_LOGS)

price_logs | CREATE TABLE `price_logs` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `productCode` varchar(15) DEFAULT NULL,
  `old_price` decimal(20,2) NOT NULL,
  `new_price` decimal(20,2) NOT NULL,
  `added_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `productCode` (`productCode`),
  CONSTRAINT `price_logs_ibfk_1` FOREIGN KEY (`productCode`) REFERENCES `products` (`productCode`) ON DELETE CASCADE ON UPDATE CASCADE
);

MODIFIED TO

price_logs | CREATE TABLE `price_logs` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `productCode` varchar(15) DEFAULT NULL,
  `old_price` decimal(20,2) NOT NULL,
  `new_price` decimal(20,2) NOT NULL,
  `added_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `productCode` (`productCode`),
  CONSTRAINT `price_logs_ibfk_1` FOREIGN KEY (`productCode`) REFERENCES `products` (`productCode`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

回复于 2024-04-20T03:15:04+08:00

0

我有同样的问题然后我在父表和子表中更正了引擎名称为Innodb并更正了引用字段名称FOREIGN KEY（ c_id ）REFERENCES x9o_parent_table （ c_id ）
然后它工作正常，表格安装正确 . 这将完全用于某人 .

回复于 2024-04-20T03:15:04+08:00

MySQL无法添加外键约束

20 回答

相关问题