• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1284
  • Last Modified:

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!
0
gdemaria
Asked:
gdemaria
  • 3
  • 2
1 Solution
 
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
 
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
 
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now