SQL Server: drop a FK constraint, which is not for

Question

I am using SQL Server 2008 and its Management Studio. I am doing a web project, which has a tool to automate the tables/relationships creation.

My web project reveals this error:

Unsuccessful: alter table Tester add constraint FK_c6c4bf4s2rvp56a32nnruww2b foreign key (game) references Game

Column 'Game.id' is not the same data type as referencing column 'Tester.game' in foreign key 'FK_c6c4bf4s2rvp56a32nnruww2b'

However, when I ran the following in the management studio:

ALTER TABLE dbo.Tester DROP CONSTRAINT FK_c6c4bf4s2rvp56a32nnruww2b

I get the following:

Msg 3728, Level 16, State 1, Line 1
'FK_c6c4bf4s2rvp56a32nnruww2b' is not a constraint.
Msg 3727, Level 16, State 0, Line 1
Could not drop constraint. See previous errors.

I am confused. What type of constraint is FK_c6c4bf4s2rvp56a32nnruww2b?

How can I remove it?

Thanks and regards.

Answer 1

There is no such constraint!

The first error clearly says that it could NOT create that constraint since the datatypes of those two columns involved (Game.Id and Tester.Game) do not match.

What you should do is check how you're creating your FK constraint that leads to that first error - and explicitly give that FK constraint a meaningful name!

Answer 2

You can try

 sp_help [table_name]

to get all the foreign key constraints.

When you get the foreign key constraints on the table. Drop them by writing something like this:-

ALTER TABLE [dbo].[table_name] DROP CONSTRAINT [Foreign_FK]