Solved

Unable to drop and create a foreign key constraint

Posted on 2007-11-14
6
1,241 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 500 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

828 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