By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,264 Members | 1,100 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,264 IT Pros & Developers. It's quick & easy.

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

P: n/a
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.

What am I doing wrong?

Thanks for your help.

Apr 18 '07 #1
Share this Question
Share on Google+
1 Reply


P: n/a
ap*****@gmail.com (ap*****@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Apr 18 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.