Problems with creating a foreign key column

Question

Post reply

Problems with creating a foreign key column

michael.leach2015

SSCommitted

Points: 1597
More actions
August 2, 2019 at 10:39 pm

#3668419
Here is the code I am using:
```
ALTER TABLE Costpoint.ProjectCodes
    ADD CONSTRAINT FK_ProjectCodes_Employee FOREIGN KEY (projectMgrID) REFERENCES Employee(employeeID);
```
I am getting the following error:
```
Msg 547, Level 16, State 0, Line 33
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_ProjectCodes_Employee". The conflict occurred in database "Amargosa Hotel", table "dbo.Employee", column 'employeeID'.
```
I had a previous error which I was able to figure out. After REFERENCES I have Employee.employeeID then I remembered you are supposed to use ( ) around the column name. So I figured that one out.
Both tables do exist. The "many" table is in one schema (Costpoint) and the other table is in another schema, the 'dbo' schema. I thought this could be the problem. So I figured out how to transfer a table from one schema into another (basically a cut & paste). So now with both tables in the 'Costpoint' schema I still get an error. This is the error I got:
```
Msg 1767, Level 16, State 0, Line 33
Foreign key 'FK_ProjectCodes_Employee' references invalid table 'Employee'.
Msg 1750, Level 16, State 1, Line 33
Could not create constraint or index. See previous errors.
```
So I don't know what else to try. Any suggestions?

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Andrew P SSCarpal Tunnel Points: 4649 More actions · Answer 1

Hi michael.leach2015,

I believe only tables in a user's default schema can be referenced without stating the schema name.

Try including the schema of the Employee table reference, as below.

ALTER TABLE Costpoint.ProjectCodes
ADD CONSTRAINT FK_ProjectCodes_Employee FOREIGN KEY (projectMgrID) REFERENCES Costpoint.Employee(employeeID);

If you're still getting the below error, this would indicate that you have values in the ProjectCodes.projectMgrID column that aren't in the Employee.employeeID column. You would need to correct the invalid/missing values before the foreign key can be created.

Msg 547, Level 16, State 0, Line 33
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_ProjectCodes_Employee". The conflict occurred in database "Amargosa Hotel", table "dbo.Employee", column 'employeeID'.

Best,

Andrew

Joe Torre SSChampion Points: 10248 More actions · Answer 2

That error indicates there are rows in the referenced table that aren't in the referencing table so SQL Server can't create the constraint.

Sachin Khanna Newbie Points: 6 More actions · Answer 3

Sachin Khanna

Newbie

Points: 6

August 4, 2019 at 2:07 pm

#3668601

Facing same problem

michael.leach2015 SSCommitted Points: 1597 More actions · Answer 4

Andrew and Joe,

You guys were right. In the foreign key table, I had some blanks in the projectMgrID column so there was not a corresponding match in the primary key table. I added this error and how to resolve it to my notes.

Thank you to both of you.

Problems with creating a foreign key column

Cookies on SQLServerCentral