错误代码：1215无法添加外键约束

I'm trying to create a health management DB, I get this error. What is the problem?

我正在尝试创建一个健康管理数据库,我收到此错误。问题是什么?

-- table glocation

CREATE TABLE `MOH`.`glocation` (
`street` VARCHAR(20) NOT NULL,
`city` VARCHAR(20) NOT NULL,
`state` VARCHAR(20) NOT NULL,
`geolocation` INT(8),
PRIMARY KEY (`street`, `city`, `state`));

-- table patient

CREATE TABLE `MOH`.`patient` (
`PID` INT(6) zerofill UNSIGNED NOT NULL AUTO_INCREMENT,
`first_name` VARCHAR(45) NOT NULL,
`last_name` VARCHAR(45) NOT NULL,
`sex` VARCHAR(10),
`b_insurance` VARCHAR(45),
`s_insurance` VARCHAR(45),
`education` VARCHAR(20),
`job` VARCHAR(20),
`street` VARCHAR(20),
`city` VARCHAR(20),
`state` VARCHAR(20),
`date_of_birth` date,
`license` boolean,
PRIMARY KEY (`PID`),
CONSTRAINT `street`
  FOREIGN KEY (`street`)
  REFERENCES `MOH`.`glocation` (`street`),
CONSTRAINT `city`
  FOREIGN KEY (`city`)
  REFERENCES `MOH`.`glocation` (`city`),
CONSTRAINT `state`
  FOREIGN KEY (`state`)
  REFERENCES `MOH`.`glocation` (`state`));

The error is:

错误是:

CREATE TABLE MOH.patient (...) Error Code: 1215. Cannot add foreign key constraint

CREATE TABLE MOH.patient(...)错误代码:1215。无法添加外键约束

1 个解决方案

#1

I should think it's because you're trying to make patient.street, patient.city and patient.state foreign keys individually, but they aren't independently primary keys in glocation.

我应该认为这是因为你试图制作耐心的.street,patient.city和patient.state外键,但它们不是独立的主要键。

Try making the combination of columns a single foreign key, as in

尝试将列的组合作为单个外键,如

CONSTRAINT location
  FOREIGN KEY (street, city, state)
  REFERENCES glocation (street, city, state)

which I think makes more sense in the context of your project anyway. (The three independent constraints you tried to define would have allowed nonsense combinations like 'El Camino Real', 'New York City', 'Florida' as long as each value independently existed in glocation.)

无论如何,我认为在你的项目中更有意义。 (你试图定义的三个独立约束允许无意义组合,如'El Camino Real','New York City','Florida',只要每个值在glocation中独立存在。)

#1