Unable to drop and create a foreign key constraint


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!
LVL 39
gdemariaAsked:
Who is Participating?
 
Chris MangusDatabase AdministratorCommented:
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
 
Chris MangusDatabase AdministratorCommented:
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
 
gdemariaAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Chris MangusDatabase AdministratorCommented:
Have you checked sysconstraints to see if there is a row for a constraint with this name?
0
 
imitchieCommented:
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
 
gdemariaAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.