CODEWITHSUNDEEP
mysqlforeign-keysmysql-workbench
01000110
01000110

Reputation: 282

Error Code: 1215 Cannot add foreign key constraint

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

Upvotes: 1

Views: 110

Answers (1)

Darwin von Corax
Darwin von Corax

Reputation: 5246

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.

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.)

Upvotes: 1

Related Questions