SQLServer: How to drop corrupted Foreign Key Constraint

Question

I have a corrupted Foreign Key Constraint in one of our databases.

I can't drop it - because it does not exist. It does not exist in sys.sysobjects and sys.objects. SQLServer Management Console does not list it as well,

Yet, I can't recreate the Foreign Key Constraint because it already exists in the schema (error: an object with the same identifier does already exist).

How can I fix that? Which metadata tables are responsible for that?

If there's definitely no other object in the database with that name (I hope you've been searching sys.objects based on just name at this point), open a ticket with MS. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Mar 18, 2011 at 13:31
Yes, there's definitely not one single object with the name. — Falcon
– Falcon, Commented Mar 18, 2011 at 13:38

Ben Thul · Accepted Answer · 2011-03-18 14:24:53Z

2

The only thing I can think of is if it's somehow doing a case-sensitive match and you're passing in a name with different case than what the server has stored. Try this:

select fk.[name]
from sys.foreign_keys fk
inner join sys.objects o
   on fk.[referenced_object_id] = o.[object_id]
where lower(o.[name]) = lower('your table here')

answered Mar 18, 2011 at 14:24

Ben Thul

32.9k4 gold badges51 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQLServer: How to drop corrupted Foreign Key Constraint

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related