[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Unable to drop and create a foreign key constraint

Posted on 2007-11-14
6
Medium Priority
?
1,278 Views
Last Modified: 2010-08-05

I dropped the foreign key from my database table using..

 alter table MyTable drop constraint mytable_fkey

Then I attempted to create it again using this command..

 alter table MyTable add constraint mytable_fkey  foreign key ( fkeyColum ) references otherTable

I get the error...

Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "mytable_fkey". The conflict occurred in database "mydatabase", table "dbo.otherTable", column 'otherTable_id'.

So, I figure I didn't really drop it.. so I run the drop command again and get this...

Msg 3728, Level 16, State 1, Line 1
     'mytable_fkey' is not a constraint.

How can It not be a constraint (so i can't drop it) and already exist so I can't create it?

Is the error on create referring to something else that I need to remove like an index?
thanks!
0
Comment
Question by:gdemaria
  • 3
  • 2
6 Comments
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 20284857
Try:

alter table MyTable add constraint mytable_fkey  foreign key ( fkeyColum ) references otherTable (fkeyColumn)

The syntax you supplied doesn't define the column in otherTable to create the FK on.
0
 
LVL 17

Accepted Solution

by:
Chris Mangus earned 2000 total points
ID: 20284888
You may also need to add the WITH NOCHECK clause because you may have child data that has no matching data in the parent table.
0
 
LVL 39

Author Comment

by:gdemaria
ID: 20284969
thanks cmangus, but that didn't do it.  

The error is telling me that a contraint already exists for that column so I'm thinking something is still there.. but what?

regarding NOCHECK, I do want to ensure there is matching data, but I tried it anyway and the error doesn't change.

0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
LVL 17

Expert Comment

by:Chris Mangus
ID: 20285008
Have you checked sysconstraints to see if there is a row for a constraint with this name?
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20285514
are you using sql server 2005? if you upgraded from 2000, you can drop but not recreate same-named foreign keys in two different tables.
0
 
LVL 39

Author Closing Comment

by:gdemaria
ID: 31409243
Somehow it seems a parent record was delete without its children, I didn't see this at first because there was a deadlock on the table preventing processing of my index.  Thanks!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

590 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