Solved

Unable to drop Foreign Key in Table

Posted on 2014-10-22
10
127 Views
Last Modified: 2014-10-22
I have been Unable to drop Foreign Keys in Tables.
I am using sql server 2008 r2.
I have tried many scripts without success.
Below is a list of table names & Foreign Keys:
I can list create table & FK script if needed.
Thanks in advance for any help.
USE CorpWear265_Restore_TestAlt
Table: pcdb_category
FOREIGN KEY (catMajorID) REFERENCES pcdb_cat_major (mcatID)
Table:pcdb_colourways
FOREIGN KEY (cwColID1) REFERENCES pcdb_colour_name (cID)
FOREIGN KEY (cwColID2) REFERENCES pcdb_colour_name (cID)
FOREIGN KEY (cwColID3) REFERENCES pcdb_colour_name (cID)
FOREIGN KEY (cwColID4) REFERENCES pcdb_colour_name (cID)
FOREIGN KEY (cwColID5) REFERENCES pcdb_colour_name (cID)
Table:pcdb_line
FOREIGN KEY (lnCategory) REFERENCES pcdb_category (catID)
Table:pcdb_image
FOREIGN KEY (imLineID) REFERENCES pcdb_line (lnID)
Table:pcdb_sku
FOREIGN KEY (skuColourwayID) REFERENCES pcdb_colourways (cwID)
FOREIGN KEY (skuLineID) REFERENCES pcdb_line (lnID)

Open in new window

0
Comment
Question by:homeshopper
  • 5
  • 5
10 Comments
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40396630
How you are trying to drop the FK's and what's the error message?
0
 

Author Comment

by:homeshopper
ID: 40396649
USE CorpWear265_Restore_TestAlt
ALTER TABLE pcdb_category DROP FOREIGN KEY catMajorID
GO

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'FOREIGN'.
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40396654
You need to use the CONSTRAINT keyword:
ALTER TABLE pcdb_category DROP CONSTRAINT catMajorID

Open in new window

0
 

Author Comment

by:homeshopper
ID: 40396660
Thanks for the suggestion, but get new error:
Msg 3728, Level 16, State 1, Line 2
'catMajorID' is not a constraint.
Msg 3727, Level 16, State 0, Line 2
Could not drop constraint. See previous errors.
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40396665
Please run the following query and confirm the FK names:
SELECT t.name, f.name
FROM sys.Tables t
INNER JOIN sys.foreign_keys f ON f.parent_object_id = t.object_id
ORDER BY t.name, f.name

Open in new window

0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:homeshopper
ID: 40396673
Thanks, it lists the tables with FK below:
pcdb_category      FK__pcdb_cate__catMa__15DA3E5D
pcdb_colourways      FK__pcdb_colo__cwCol__1C873BEC
pcdb_colourways      FK__pcdb_colo__cwCol__1D7B6025
pcdb_colourways      FK__pcdb_colo__cwCol__1E6F845E
pcdb_colourways      FK__pcdb_colo__cwCol__1F63A897
pcdb_colourways      FK__pcdb_colo__cwCol__2057CCD0
pcdb_image      FK__pcdb_imag__imLin__2CBDA3B5
pcdb_line      FK__pcdb_line__lnCat__27F8EE98
pcdb_line      FK__pcdb_line__lnFam__28ED12D1
pcdb_sku      FK__pcdb_sku__skuCol__32767D0B
pcdb_sku      FK__pcdb_sku__skuLin__336AA144
0
 
LVL 46

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40396676
So, there's no catMajorID FK.
Here's the command you need to run:
ALTER TABLE pcdb_category DROP CONSTRAINT  FK__pcdb_cate__catMa__15DA3E5D

Open in new window

0
 

Author Comment

by:homeshopper
ID: 40396717
Ok, I ran your last suggestion successfully,
then tried deleting table pcdb_category,but it gave same error.
Next I listed remaining Fkeys as before, then deleted those tables.
Then finally, was able to delete pcdb_category.
Thanks for your help.
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40396764
Means that you had a chain of references and you needed to drop the others ones first than that one.
0
 

Author Closing Comment

by:homeshopper
ID: 40396789
Yes, Thank you for the help
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now