Solved

Unable to drop Foreign Key in Table

Posted on 2014-10-22
10
131 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 49

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 49

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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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 49

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 49

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 49

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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Suggested Solutions

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
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 …

762 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