Solved

Unable to drop Foreign Key in Table

Posted on 2014-10-22
10
124 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 45

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 45

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 45

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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 45

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 45

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

708 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

18 Experts available now in Live!

Get 1:1 Help Now