August 2, 2019 at 10:39 pm
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?
August 3, 2019 at 7:37 am
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
August 3, 2019 at 5:45 pm
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.
August 4, 2019 at 2:07 pm
Facing same problem 🙁
August 4, 2019 at 4:06 pm
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply