What can I do about a SQL Server ghost FK constraint?
- by rcook8601
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?
