Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Ms SQL REFERENCE constraint

Posted on 2010-09-09
6
Medium Priority
?
664 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

730 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