[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Unable to drop Foreign Key in Table

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

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 53

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
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 

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 53

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 53

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 53

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 Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
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…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

591 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