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

rxztt3cl  于 2023-05-28  发布在  SQL Server
关注(0)|答案(1)|浏览(151)

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?

yx2lnoni

yx2lnoni1#

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 thatInformationis the right schema name and notdbo ?

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>

相关问题