Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1275
  • 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

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