Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Unable to drop Foreign Key in Table

Posted on 2014-10-22
10
Medium Priority
?
137 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 52

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 52

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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 

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 52

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
 

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 52

Accepted Solution

by:
Vitor Montalvão earned 2000 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 52

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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Integration Management Part 2
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

927 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