Disabling or Getting Rid of Foreign Keys So I Can Drop My Table

**Erland Sommarskog** · Apr 18 '07, 10:05 PM

Re: Disabling or Getting Rid of Foreign Keys So I Can Drop My Table

apax999@gmail.c om (apax999@gmail. com) writes:

Kinda new to SQL, using SQL Server 2005.
>
I have some foreign keys in a couple of tables. I need to drop these
tables, but can't since I'll get the error:
>
Msg 3726,
>
Level 16, State 1, Line 1
Could not drop object 'Client' because it is referenced by a FOREIGN
KEY constraint.
>
I'm not sure how to disable or get rid of these foreign keys so that I
can drop my tables. I tried:
>
ALTER TABLE Client NOCHECK CONSTRAINT ALL
>
Alter Table Client Drop Column Foreign Key Boss_ID;
>
I went into the Object Explorer and deleted the FK lines from each
table, but still the same error.

You need to drop the foreign keys from the tables that references
Client, not the FKs of Client itself. Say there is an Orders table
with a column ClientID. Then you need to drop that FK constraint.
Then again, this would mean that the client ids in Orders would
be hanging in thin air. Of course, if you create the table, and
repopulate, it may be OK. But don't forget to reapply the foreign
keys you dropped.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Disabling or Getting Rid of Foreign Keys So I Can Drop My Table

Disabling or Getting Rid of Foreign Keys So I Can Drop My Table

Comment