Mysql – Can’t add Foreign Key Constraint

constrainterrorsforeign keyMySQL

As far as I can tell it should work, I went through other similar questions but it seemed to be a different thing causing the same error.

CREATE TABLE Job
(
    JobNumber INT NOT NULL,
    JobName CHAR(30) NOT NULL,
    JobDescription CHAR(60) NOT NULL,
    Title Char(30) NOT NULL,
    PersonID INT NOT NULL,
   CONSTRAINT PK_JobNum PRIMARY KEY (JobNumber)
);

CREATE TABLE Employee
(
    PersonID INT NOT NULL,
    FirstName CHAR(30) NOT NULL,
    LastName CHAR(30) NOT NULL,
    CONSTRAINT Pk_PersonID PRIMARY KEY(PersonID),
    CONSTRAINT FK_Job_2_Employee FOREIGN KEY(PersonID) REFERENCES Job(PersonID)
);

CREATE TABLE Game
(
    GameNumber INT NOT NULL,
    Title CHAR(30) NOT NULL,
    System CHAR(30) NOT NULL,
    CONSTRAINT PK_GameNumber PRIMARY KEY(GameNumber),
    CONSTRAINT FK_Job_2_Game FOREIGN KEY(Title) REFERENCES Job(Title)
);

Error comes up when I try to initialize the second table. (And it'll probably come up on the third.)

  • Multiple people can have the same job, and a person can have multiple jobs.
  • The JobNumber and the JobName aren't the same thing. Each JobNumber only has one employee, And A single JobNumber has only one JobName.
  • The JobNumber and what job a person has is not the same thing.
  • The job number and the kind of job isn't the same thing. For example Both Job Number 1235 and 1435 could both have the job "level designer."

Best Answer

It appears you are putting the foreign key on the wrong table. The column PersonID on the Jobs table should reference the PersonID on the Employee table.

If you are assigning persons to jobs, then your original foreign key should be on JobId. This would require adding the JobId to the Employees table and removing the PersonId from the Jobs table.

If Employees can have many Jobs and Jobs can have more than one Employee, then you will want a join table. It will have foreign keys to both the Jobs and Employees table. The primary key of the join table will include both the PersonId and JobId.

Related Question