Can not delete or modify or see same table foreign

问题:

A same table foreign key constraint in my database is not accessible. I can not drop it, disable it, add it back, ... How do I remove it and re-add it?

Note: I have several versions of my database all created with the same script. Only in one I see this behavior. In others, this key is easily added and removed.

Many thanks. Here is some scripts I ran and the result:

At some point in the past i ran the following script:

ALTER TABLE Recipe
ADD CONSTRAINT FK_Recipe_DuplicateOfRecipeId_Recipe_Id FOREIGN KEY (DuplicateOfRecipeId)
    REFERENCES Recipe (Id) ;

now running

ALTER TABLE Recipe DROP CONSTRAINT  FK_Recipe_DuplicateOfRecipeId_Recipe_Id 
results in the following error:
'FK_Recipe_DuplicateOfRecipeId_Recipe_Id' is not a constraint.

and running

ALTER TABLE Recipe NOCHECK CONSTRAINT FK_Recipe_DuplicateOfRecipeId_Recipe_Id
results in: Constraint 'FK_Recipe_DuplicateOfRecipeId_Recipe_Id' does not exist.

so i run

alter table Recipe ADD CONSTRAINT FK_Recipe_DuplicateOfRecipeId_Recipe_Id FOREIGN KEY (DuplicateOfRecipeId) REFERENCES Recipe (Id);

and i get:

The ALTER TABLE statement conflicted with the FOREIGN KEY SAME TABLE constraint "FK_Recipe_DuplicateOfRecipeId_Recipe_Id". The conflict occurred in database "CrawlerDB", table "dbo.Recipe", column 'Id'.

so I run:

select COUNT(*) from sys.objects where name = 'FK_Recipe_DuplicateOfRecipeId_Recipe_Id'
select COUNT(*) from sys.all_objects where name = 'FK_Recipe_DuplicateOfRecipeId_Recipe_Id'
SELECT COUNT(*) FROM sys.foreign_keys where name = 'FK_Recipe_DuplicateOfRecipeId_Recipe_Id'

and all 3 return nothing.

Whats going on and how do I fix it? I need to access this object, remove it and add it back.
Many thanks!

回答1:

I'm guessing that your master database is corrupted. You'd probably be best suited by rebuilding it.

However, as a workaround, you could try this:

1. Duplicate your foreign key into a non-FK column

   ALTER TABLE Recipe ADD DuplicateOfFK INT

2. Copy all your FK data to the duplicate

   UPDATE Recipe SET DuplicateOfFK = DuplicateOfRecipeId

3. Drop the Foreign Key column

   ALTER TABLE Recipe DROP COLUMN DuplicateOfRecipeId

4. Go backwards.

   ALTER TABLE Recipe ADD DuplicateOfRecipeId INT

   UPDATE Recipe SET DuplicateOfRecipeId = DuplicateOfFK

   ALTER TABLE Recipe DROP COLUMN DuplicateOfFK

5. Add the constraint back.

回答2:

Your comment above should have given you the clue:

after setting all values in the foreign key field to null, then everything works again. But why?

Unfortunately the error message can be misleading. The statement adding the foreign key back failed not because you were creating a circular reference, but because DuplicateOfRecipeId contained at least one non-null value that was not already contained in Id.

By that I mean that the following code works:

create table dbo.Recipe
(
    Id int not null identity(1, 1)
        constraint PK_Recipe primary key nonclustered
    , DuplicateOfRecipeId int not null
);
insert dbo.Recipe values (1), (2), (3);
alter table Recipe add constraint FK_Recipe_DuplicateOfRecipeId_Recipe_Id foreign key (DuplicateOfRecipeId) references Recipe (Id);
drop table dbo.Recipe
go

But the following code fails (note the different DuplicateOfRecipeId values):

create table dbo.Recipe
(
    Id int not null identity(1, 1)
        constraint PK_Recipe primary key nonclustered
    , DuplicateOfRecipeId int not null
);
insert dbo.Recipe values (4), (5), (6);
alter table Recipe add constraint FK_Recipe_DuplicateOfRecipeId_Recipe_Id foreign key (DuplicateOfRecipeId) references Recipe (Id);
drop table dbo.Recipe
go