Solved

Ms SQL REFERENCE constraint

Posted on 2010-09-09
6
659 Views
Last Modified: 2012-05-10
Hi

I am try to delete som records and i get this, there is no field New_ordredetaljerBase.new_kundeid

and this dos not work..

ALTER TABLE dbo.[New_ordredetaljerBase]
   DROP CONSTRAINT new_kundeid


Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint "Contact_New_ordres". The conflict occurred in database "NRGi_MSCRM", table "dbo.New_ordreExtensionBase", column 'new_kundeid'.
The statement has been terminated.


What can i do ?
0
Comment
Question by:radut
  • 3
  • 2
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33634846
you dropped the wrong constraint aka on the wrong table.now, if the foreign key has to exist, you should first delete OR update the related records, then delete the records you want to delete here.check the error message, it tells you the name of the constraint
0
 
LVL 4

Expert Comment

by:MichaelMH
ID: 33634893
Your issue in this case, is related to the fact that you are trying to remove a record from a parent table which is referenced by a child table. First of all you must delete the record from the child table and then you will be able to perform changes on the parent table (.New_ordreExtensionBase).

Check in which other tables from you data base the column "Contact_New_ordres" from table "New_ordreExtensionBase" is used.

Here is a script which is going to list all constrains from your DB:

USE NRGi_MSCRM;
GO
SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT'
GO
0
 

Author Comment

by:radut
ID: 33634938
there are 1100 results.. :(

0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33634977
you don't need that query. please just reread the error message you got. it has all the names you need...
0
 
LVL 4

Expert Comment

by:MichaelMH
ID: 33635122
Use the following query to see the tables to which your initial table is related with:

USE NRGi_MSCRM;
EXEC sp_fkeys @pktable_name = N'New_ordreExtensionBase'
0
 
LVL 4

Accepted Solution

by:
MichaelMH earned 500 total points
ID: 33635135
This query presents all the constraints of your table:

USE NRGi_MSCRM;
GO
SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE OBJECT_NAME(parent_object_id) = 'New_ordreExtensionBase'
AND type_desc LIKE '%CONSTRAINT'
GO

It might be useful in some cases.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

713 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