radut
asked on
Ms SQL REFERENCE constraint
Hi
I am try to delete som records and i get this, there is no field New_ordredetaljerBase.new_
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_ordreExtensionBas
The statement has been terminated.
What can i do ?
I am try to delete som records and i get this, there is no field New_ordredetaljerBase.new_
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_ordreExtensionBas
The statement has been terminated.
What can i do ?
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
Guy Hengel [angelIII / a3]
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_
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT'
GO
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_
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT'
GO
ASKER
there are 1100 results.. :(
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'
USE NRGi_MSCRM;
EXEC sp_fkeys @pktable_name = N'New_ordreExtensionBase'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.