CODEWITHSUNDEEP
sql-server-2012
Konrad Viltersten
Konrad Viltersten

Reputation: 39318

Can't drop nor find a constraint on foreign key

I've declared me a table and put in a constraint on one of the columns. Being ignorant (or maybe unwilling) of altering the table, I did it in one move like so.

drop table Test
create table Test(
  Id bigint primary key identity(1000,1),
  TypeId bigint not null foreign key references TestTypes(Id),
  StringValue nvarchar(100) null)

Now, the stupid server won't allow me to remove the table...

I've tried clicking around the the GUI. No luck there. I tried adding and dropping a new constraint by ALTER. To no avail. I tried looking in the table with constraints. It's empty.

So, the question is twosome.

  1. How do I get rid of the stupid constraint?
  2. What is the actual name if the stupid constraint?

Upvotes: 1

Views: 31

Answers (1)

jpw
jpw

Reputation: 44931

You can query the system catalog views to find the name and use that to drop the constraint.

This code drops the foreign key constraint on the columnTypeIdin the tableTest

declare @n varchar(max) = 'alter table Test drop '

select @n = @n + fk.name 
from sys.foreign_keys fk
inner join sys.columns c on fk.parent_object_id = c.object_id 
inner join sys.tables t on t.object_id = c.object_id
where c.name = 'TypeId' and t.name = 'Test'

exec (@n)

Upvotes: 1

Related Questions