Posted by Erland Sommarskog on 04/18/07 21:58

apax999@gmail.com (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@sommarskog.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

[Back to original message]