MYSQL -> FOREIGN KEY -> Errorno 150

Question

I have the following problem. These are my tables:

CREATE TABLE Author
(
  author_fName VARCHAR(256) NOT NULL,
  author_lName VARCHAR(256) NOT NULL,
  author_dob DATE,
  PRIMARY KEY (author_fName, author_lName)
  # author_age INT
);

CREATE TABLE Director
(
  director_fName VARCHAR(256) NOT NULL,
  director_lName VARCHAR(256) NOT NULL,
  director_dob DATE,
  PRIMARY KEY (director_fName, director_lName)
);

CREATE TABLE Award
(
  award_Name VARCHAR(256) NOT NULL PRIMARY KEY,
  a_fName VARCHAR(256),
  a_lName VARCHAR(256),
  d_fName VARCHAR(256),
  d_lName VARCHAR(256),
  FOREIGN KEY (a_fName) REFERENCES Author(author_fName),
  FOREIGN KEY (a_lName) REFERENCES Author(author_lName),
  FOREIGN KEY (d_fName) REFERENCES Director(director_fName),
  FOREIGN KEY (d_lName) REFERENCES Director(director_lName)
);

Sadly when I try to create the last table I receive a lovely ERROR NO 150. I investigated a little bit and found out that the error comes when I try to set the FOREIGN KEY for the a_lName and d_lName.

I'm not sure why this is happening.

MySQL errors come with a textual.description., You have it on your screen right in front of you, so there's no reason for you to fail to provide it in your post. We shouldn't have to Google the error message to get that description. You're asking us to help you, and it's in your best interest to make it as easy as possible for us to do so by providing the information you already have in your post. — Ken White, Dec 27 '18 at 17:53

Uueerdo · Answer 1 · 2018-12-27T18:05:25.953

Referenced fields must be indexed in the order referenced, since you are referencing Director.director_lname by itself, it must be the first field of an index in the Director table. You should be having similar issues with referencing Author.author_lName.

Side notes:

It is more typical to have surrogate auto-increment primary keys for these kinds of tables, and for references to reference those primary key.
Prefixing every field with the table name is going to make future queries unnecessarily long; if field from different tables need distinguish, such can be done simply by specifying the name of the table (or a shorter alias used in the query). Example: SELECT d.fname FROM Director AS d
If you keep your current design, your fk references will allow some odd behavior. It permits an Award to reference the fname of one director, and lname of another one even if the combination does not exist. Example: "Hayao Spielberg"

MYSQL -> FOREIGN KEY -> Errorno 150

1 Answers1