Can’t add Foreign Key Constraint

Posted on

Question :

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

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.

Job.PersonID does not have a unique constraint
I think this is what you mean to have
But is is still messed up in only one person can have a job
If that is the case then just combine Job and Employee

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

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),
    CONSTRAINT FK_Job_2_Employee FOREIGN KEY(PersonID) REFERENCES Employee(PersonID)
);

And this messed up:

CONSTRAINT FK_Job_2_Game FOREIGN KEY(Title) REFERENCES Job(Title)

Job.Title is not unique
Why would you even have a relationship between two char(30)
This is just messed up
You give contradicting requirements in comments
You need to step back and do a proper data design

Thanks to the suggestions above I was able to solve it, I combined both solutions. (One is I was doing it backwards, the other that using a Title is a Bad Id.

I simply substituted the title for a Game_ID. (Different Game_ID’s might share a similar title and they are numbers.)

CREATE TABLE Employee
(
    Employee_ID INT NOT NULL,
    First_Name CHAR(30) NOT NULL,
    Last_Name CHAR(30) NOT NULL,
    CONSTRAINT PK_Employee_ID PRIMARY KEY (Employee_ID)
);
CREATE TABLE Game
(
    Game_ID INT NOT NULL,
    Title CHAR(30) NOT NULL,
    System CHAR(30) NOT NULL,
    CONSTRAINT PK_Game_ID PRIMARY KEY (Game_ID)
);
CREATE TABLE Job
(
    Job_ID INT NOT NULL,
    Job_Type CHAR(30) NOT NULL,
    Job_Description CHAR(60) NOT NULL,
    Game_ID INT NOT NULL,
    Employee_ID INT NOT NULL,
    CONSTRAINT PK_Job_ID PRIMARY KEY (Job_ID),
    CONSTRAINT FK_Employee_2_Job FOREIGN KEY (Employee_ID) REFERENCES Employee (Employee_ID),
    CONSTRAINT FK_Game_2_Job FOREIGN KEY (Game_ID) REFERENCES Game (Game_ID)
);

Leave a Reply

Your email address will not be published. Required fields are marked *