CODEWITHSUNDEEP
sql-serversql-server-2008
user1000197
user1000197

Reputation: 13

Unable to get a Foreign key

USE Kudler_FF
ALTER TABLE Employee_Tbl
ADD CONSTRAINT FK_Employee_Tbl
FOREIGN KEY (JobTitle) REFERENCES Job_Tbl (JobTitle);

Message I am getting:

Msg 547, Level 16, State 0, Line 2
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Employee_Tbl". The conflict occurred in database "Kudler_FF", table "dbo.Job_Tbl", column 'JobTitle'.

What did I add or not add

Upvotes: 0

Views: 100

Answers (1)

paxdiablo
paxdiablo

Reputation: 882696

You usually only get an error adding a foreign key constraint when the constraint would be violated by the current data.

In other words, you probably have a value in Employee_Tbl(JobTitle) that does not exist in Job_Tbl(JobTitle).

You would not be able to add such a constraint until your data is modified so that a violation would not occur.

Find the values for JobTitle in Employee_Tbl that don't exist in Job_Tbl and then add them to that latter table.

I'm not sure of the exact syntax for SQL Server but you could start with:

select distinct JobTitle from Employee_Tbl
where JobTitle not in (
    select distinct JobTitle from Job_Tbl
)

Upvotes: 1

Related Questions