asked on

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!

Chris Mangus

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.

ASKER CERTIFIED SOLUTION

Chris Mangus

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gdemaria

ASKER

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.

Chris Mangus

Have you checked sysconstraints to see if there is a row for a constraint with this name?

imitchie

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.

gdemaria

ASKER

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!