Solved

Ms SQL REFERENCE constraint

Posted on 2010-09-09
6
657 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 142

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
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 142

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

776 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