What can I do about a SQL Server ghost FK constraint?

Question

I'm having some trouble with a SQL Server 2005 database that seems like it's keeping a ghost constraint around. I've got a script that drops the constraint in question, does some work, and then re-adds the same constraint. Normally, it works fine. Now, however, it can't re-add the constraint because the database says that it already exists, even though the drop worked fine!

Here are the queries I'm working with:

alter table individual drop constraint INDIVIDUAL_EMP_FK

ALTER TABLE INDIVIDUAL
   ADD CONSTRAINT INDIVIDUAL_EMP_FK
          FOREIGN KEY (EMPLOYEE_ID)
                         REFERENCES EMPLOYEE

After the constraint is dropped, I've made sure that the object really is gone by using the following queries:

select object_id('INDIVIDUAL_EMP_FK')
select * from sys.foreign_keys where name like 'individual%'

Both return no results (or null), but when I try to add the query again, I get:

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "INDIVIDUAL_EMP_FK". 

Trying to drop it gets me a message that it doesn't exist. Any ideas?

Answer 1

It means the data is wrong on creation of the FK

That is, you have "EMPLOYEE_ID" values in the INDIVIDUAL child table that don not exist in the parent EMPLOYEE table.

You could use ALTER TABLE ...WITH NOCHECK ADD CONSTRAINT... but then the FK is no use