Unable to drop Foreign Key in Table
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.
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)
Vitor Montalvão
How you are trying to drop the FK's and what's the error message?
ASKER
USE CorpWear265_Restore_TestAl
ALTER TABLE pcdb_category DROP FOREIGN KEY catMajorID
GO
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'FOREIGN'.
ALTER TABLE pcdb_category DROP FOREIGN KEY catMajorID
GO
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'FOREIGN'.
You need to use the CONSTRAINT keyword:
ALTER TABLE pcdb_category DROP CONSTRAINT catMajorIDASKER
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.
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.
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.nameASKER
Thanks, it lists the tables with FK below:
pcdb_category FK__pcdb_cate__catMa__15DA
pcdb_colourways FK__pcdb_colo__cwCol__1C87
pcdb_colourways FK__pcdb_colo__cwCol__1D7B
pcdb_colourways FK__pcdb_colo__cwCol__1E6F
pcdb_colourways FK__pcdb_colo__cwCol__1F63
pcdb_colourways FK__pcdb_colo__cwCol__2057
pcdb_image FK__pcdb_imag__imLin__2CBD
pcdb_line FK__pcdb_line__lnCat__27F8
pcdb_line FK__pcdb_line__lnFam__28ED
pcdb_sku FK__pcdb_sku__skuCol__3276
pcdb_sku FK__pcdb_sku__skuLin__336A
pcdb_category FK__pcdb_cate__catMa__15DA
pcdb_colourways FK__pcdb_colo__cwCol__1C87
pcdb_colourways FK__pcdb_colo__cwCol__1D7B
pcdb_colourways FK__pcdb_colo__cwCol__1E6F
pcdb_colourways FK__pcdb_colo__cwCol__1F63
pcdb_colourways FK__pcdb_colo__cwCol__2057
pcdb_image FK__pcdb_imag__imLin__2CBD
pcdb_line FK__pcdb_line__lnCat__27F8
pcdb_line FK__pcdb_line__lnFam__28ED
pcdb_sku FK__pcdb_sku__skuCol__3276
pcdb_sku FK__pcdb_sku__skuLin__336A
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
Means that you had a chain of references and you needed to drop the others ones first than that one.
ASKER
Yes, Thank you for the help