Have Geek Brain

What can I do about FK constraint in SQL Server?

I am having problems with a SQL Server 2005 database that seems to support ghost constraint. I have a script that resets a specified constraint, does some work, and then re-adds the same constraint. It usually works fine. Now, however, it cannot re-add the constraint because the database says it already exists, even though the blobs 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 limitation was turned off, I verified that the object actually disappeared 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 throw this gives me a message that it doesn't exist. Any ideas?

+2


source to share


1 answer


This means the data is incorrect when creating the FK

That is, you have EMPLOYEE_ID values ​​in the INDIVIDUAL child table that are not in the EMPLOYEE parent table.



You can use ALTER TABLE ...WITH NOCHECK ADD CONSTRAINT...

but then FK is not used

+4


source






More articles:


All Articles