无法删除或修改或查看同一个表外键约束 [英] Can not delete or modify or see same table foreign key constraint

问题描述

我的数据库中有一个相同的表外键约束是不可访问的。我不能删除它，禁用它，加回来，...我如何删除它，并重新添加它？

注意：我有我的几个版本数据库全部使用相同的脚本创建。只有在我看到这种行为。在其他情况下，这个键很容易被添加和删除。

非常感谢。这里是我跑的一些脚本和结果：

在过去的某个时候，我运行了下面的脚本：

  ALTER TABLE配方
添加约束FK_Recipe_DuplicateOfRecipeId_Recipe_Id FOREIGN KEY（DuplicateOfRecipeId）
 REFERENCES Recipe（Id）; 
  

正在运行

  ALTER TABLE配方DROP CONSTRAINT FK_Recipe_DuplicateOfRecipeId_Recipe_Id 
导致以下错误：
'FK_Recipe_DuplicateOfRecipeId_Recipe_Id'不是一个约束。 
  

正在运行

  ALTER TABLE配方NOCHECK CONSTRAINT FK_Recipe_DuplicateOfRecipeId_Recipe_Id 
导致：约束'FK_Recipe_DuplicateOfRecipeId_Recipe_Id'不存在。 
  

所以我运行

  alter table Recipe ADD CONSTRAINT FK_Recipe_DuplicateOfRecipeId_Recipe_Id FOREIGN KEY（DuplicateOfRecipeId）REFERENCES Recipe（Id）; 
  

我得到：

  ALTER TABLE语句与FOREIGN KEY SAME TABLE约束FK_Recipe_DuplicateOfRecipeId_Recipe_Id冲突。数据库CrawlerDB，表dbo.Recipe，列Id发生冲突。 
  

所以我运行：

从sys.all_objects中选择COUNT（*），其中name ='FK_Recipe_DuplicateOfRecipeId_Recipe_Id'
 SELECT COUNT（*） FROM sys.foreign_keys其中name ='FK_Recipe_DuplicateOfRecipeId_Recipe_Id'
  

并且全部3不返回任何内容。

怎么回事，我该如何解决？我需要访问这个对象，删除它，并将其添加回来。

非常感谢！

解决方案

猜测你的主数据库已损坏。您可能最适合重建它

然而，作为一个解决方法，你可以试试这个：

$ ol

将您的外键复制到非FK列中

$ b

ALTER TABLE配方ADD DuplicateOfFK INT

将您所有的FK数据复制到复本中

UPDATE Recipe SET DuplicateOfFK = DuplicateOfRecipeId

删除外键列

<$ c














$ b p> ALTER TABLE Recipe ADD DuplicateOfRecipeId INT

$ b

UPDATE Recipe SET DuplicateOfRecipeId = DuplicateOfFK code $
$ b

ALTER TABLE配方DROP COLUMN DuplicateOfFK

添加限制。

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!

解决方案

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.

这篇关于无法删除或修改或查看同一个表外键约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！