Cannot add foreign key constraint mysql

Question

Does anyone know why I keep getting this error with this query. I'm stumped:

Error Code: 1215. Cannot add foreign key constraint

My Code:

CREATE TABLE countries (
  id INT NOT NULL AUTO_INCREMENT,
  cname VARCHAR(45) NOT NULL,
  PRIMARY KEY (id)
) COMMENT='Country List';

CREATE TABLE members (
  id INT NOT NULL AUTO_INCREMENT,
  FirstName VARCHAR(50) NOT NULL,
  LastName VARCHAR(50) NOT NULL,
  Salt VARCHAR(45),
  PRIMARY KEY (id),
  FOREIGN KEY (Salt) REFERENCES countries(cname)
) COMMENT='stuff';

Mureinik · Accepted Answer · 2015-06-23 21:14:55Z

3

A foreign key must reference a primary or unique key. For example, you could make countries.cname unique:

CREATE TABLE countries (
  id INT NOT NULL AUTO_INCREMENT,
  cname VARCHAR(45) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE (cname) -- Here
) COMMENT='Country List';

Alternatively, you can drop the salt column and make the reference via the id:

CREATE TABLE members (
  id INT NOT NULL AUTO_INCREMENT,
  FirstName VARCHAR(50) NOT NULL,
  LastName VARCHAR(50) NOT NULL,
  Salt_id INT, -- Here
  PRIMARY KEY (id),
  FOREIGN KEY (Salt_Id) REFERENCES countries(id) -- And here
) COMMENT='stuff';

answered Jun 23, 2015 at 21:14

Mureinik

316k54 gold badges402 silver badges406 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Cannot add foreign key constraint mysql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related