SQL Server : cannot drop table

Question

I try to drop all the foreign and primary keys for an assignment, but so far I get the following error:

Msg 3728, Level 16, State 1, Line 3
'fk_DEPENDENT_ESSN' is not a constraint.

Msg 3727, Level 16, State 0, Line 3
Could not drop constraint. See previous errors.

Here are my commands:

Use Company;
Go

--Dropping FKs from Dependent Table
ALTER TABLE Dependent
DROP fk_DEPENDENT_ESSN

--Dropping FKs from Dept_Locations Table
ALTER TABLE Dept_Locations
DROP fk__DEPT_LOCATIONS__Dnumber

--Dropping FKs from Employee Table
ALTER TABLE Employee
DROP FK__Employee__Dno

ALTER TABLE Employee
DROP FK__Employee__Super___ssn

--Dropping FKs from Project Table
ALTER TABLE Project
DROP FK__Project

--Dropping FKs from WORKS_ON Table
ALTER TABLE WORKS_ON
DROP FK__WORKS_ON__Essn

ALTER TABLE WORKS_ON
DROP FK__WORKS_ON__PNO

How should I fix this, sorry I can't post my database on here. if you have any questions, feel free to comment or we can email.

Thanks

If you don't show your schema, it may not be possible to get a precise answer. — Tim Biegeleisen, Nov 23 '15 at 04:10
You need to show to script on creating your table structures, so that we can evaluate those keys. — japzdivino, Nov 23 '15 at 04:12

score 0 · Answer 1 · edited May 23 '17 at 12:30

0

The error is because fk_DEPENDENT_ESSN does not exist. Are you missing and underscore? Should be fk__DEPENDENT_ESSN?

You can check what the names of the FK are using the sysobjects table.

SELECT * FROM sysobjects WHERE name LIKE '%DEPENDENT_ESSN'

See SQL Server 2008 cannot drop constraint

edited May 23 '17 at 12:30

Community

1
1

answered Nov 23 '15 at 04:12

Dave Anderson

11,836
3
58
79

Thanks can I email you? kind hard to put a lot of stuff on here. or can I? – Lin Wei Nov 23 '15 at 04:19
@LinWei Just run the query I posted, it will check the schema. If it finds a row it will show you the correct name of the key to use with `DROP`. If not then you need to use a better `WHERE` clause to find the key. – Dave Anderson Nov 23 '15 at 04:26
it just give me a bunch of long list – Lin Wei Nov 23 '15 at 04:30
on my database, the fk is named exactly the same as my commands, under the key – Lin Wei Nov 23 '15 at 04:31

SQL Server : cannot drop table

1 Answers1