SQL Server Error: "%" is not a constraint. Could not drop constraint. See previous errors

Question

I'm using Microsoft SQL Server 2005, and am relatively new to SQL in general.

There is a relationship between two tables, "Resources" and "Group_Resources", in the database "Information". Resources has a foreign key, "id", in Group_Resources, named "resource_id". There is a foreign key constraint, "fk_gr_res_resources", between the two.

I'm established as the database owner, and have full read/write/create/delete permissions.

I want to delete the foreign key constraint, so I executed the following query:

ALTER TABLE [Information].[group_resources] DROP CONSTRAINT fk_gr_res_resources 

and received the following error:

'fk_gr_res_resources' is not a constraint. Could not drop constraint. See previous errors.

I'm confused, because it is a constraint, and there are no spelling errors. Am I going about deleting this improperly? Am I deleting the constraint from the incorrect table? Any suggestions would be greatly appreciated, & please don't flame me: I am new to SQL after all.

Answer 1

You are getting this error:

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

Because the FK constraint does not exist!

Are you sure that Information is the right schema name and not dbo?

1. This SQL will prove that the FK does not exist:

SELECT * FROM sysobjects WHERE name = 'fk_gr_res_resources' 

2. Oops, I was wrong in my original answer, here is the correct syntax for SQL Server:

ALTER TABLE <table_name> DROP CONSTRAINT <foreignkey_name> 

3. Example for your database:

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'fk_gr_res_resources') BEGIN   ALTER TABLE Group_Resources   DROP CONSTRAINT fk_gr_res_resources END; 

4. Try running this:

IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'fk_gr_res_resources') BEGIN   ALTER TABLE Group_Resources   ADD CONSTRAINT fk_gr_res_resources   FOREIGN KEY (resource_id)   REFERENCES Resources(id) /* make sure Resources.id is a PRIMARY KEY */ END; 

5. Then try this and see if you still get that error:

ALTER TABLE Group_Resources DROP CONSTRAINT fk_gr_res_resources 

The other syntax was for MySQL, sorry:

ALTER TABLE <table_name> DROP FOREIGN KEY <foreignkey_name> 

Thanks for correcting me OMG Ponies!