We help IT Professionals succeed at work.

Ms SQL REFERENCE constraint

radut
radut asked
on
744 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 ?
Comment
Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
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
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

Author

Commented:
there are 1100 results.. :(

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
you don't need that query. please just reread the error message you got. it has all the names you need...
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'
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.