Post reply

Problems with creating a foreign key column

  • 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?

     

  • August 3, 2019 at 7:37 am

    #3668453

    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

    #3668505

    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

    #3668601

    Facing same problem 🙁

  • August 4, 2019 at 4:06 pm

    #3668636

    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