The delete statement conflicted with the reference constraint ошибка

Remove From My Forums

Question
I am following the sample here:

http://csharp.net-informations.com/dataadapter/deletecommand-sqlserver.htm

I get a message that reads ‘The DELETE statement conflicted with the REFERENCE constraint “FK_Orders_Details_Products”’
In the Orders table, I can see that each order has an OrderID and in the Products table I see ProductID.

So, I have a couple questions:

#1)
How can I see the relationships between the tables in SQL Server?
I’ve seen it before and now I forget how to get back there.

#2)
How can I see the relationships on the tables themselves?
I thought I could see it in ‘Design’ view, but I’m not seeing it anywhere.

#3)
How can I ensure that all references to a Delete query are actually deleted?
Basically, how can I change the code to make this delete work?

Answers

HI !

You need to find on which table this FK constraint has been defined. Look at
sysconstraints table.

Please let me know if you still need assistance.

Thanks, Hasham
- Marked as answer by
  
  Monday, October 24, 2011 6:03 PM
Also try to look at View object Dependencies.

thanks, Hasham
- Marked as answer by
  ryguy72
  Monday, October 24, 2011 6:03 PM

Question: In the application error logs there is a message — The DELETE statement conflicted with the REFERENCE constraint FK_xxxxxxxxx

What is causing this error ? How can I troubleshoot?

Answer: The error is occuring because there is an attempt to delete a parent table data before the referenced child table data is deleted

When a database uses a FOREIGN KEY , its purpose is to create a link between two tables. The foreign key is one or more columns in the a table referencing another table’s PRIMARY KEY.

A child table is the table hosting the FOREIGN KEYS.A parent table is the table hosting the PRIMARY KEY.

The main idea underlying the FOREIGN KEY is to maintain referential integrity — via blocking deletion of data being referred elsewhere

There are 2 main patterns for managing PARENT — CHILD DATA

1)Delete the rows from child table first, then the rows from the parent table.
2)ON DELETE CASCADE. but is potentially a dangerous practise . Mainly because the application is disassociating the DELETE logic from other logic that may be encapsulated in queries , stored in ad-hoc queries or stored procedures. This point is up for debate

Read more on foreign key constraints

How to drop a SQL column and find foreign keys with sp_fkeys

List Foreign Key Constraints -MS SQL

How to list constraints of a table (SQL Server DBA)

SQL Server – Optimize delete from table

Author: Tom Collins (http://www.sqlserver-dba.com)

Share:

Источник

Home > SQL Server Error Messages > Msg 547 — DELETE statement conflicted with COLUMN REFERENCE constraint Constraint Name. The conflict occurred in database Database Name, table Table Name, column Column Name.

SQL Server Error Messages — Msg 547 — DELETE statement conflicted with COLUMN REFERENCE constraint Constraint Name. The conflict occurred in database Database Name, table Table Name, column Column Name.

SQL Server Error Messages — Msg 547

Error Message

Server: Msg 547, Level 16, State 1, Line 1
DELETE statement conflicted with COLUMN REFERENCE 
constraint Constraint Name.  The conflict occurred in
database Database Name, table Table Name, column
Column Name.
The statement has been terminated.

Causes:

This error occurs if you are trying to delete a record from a table that has a PRIMARY KEY and the record being deleted is being referenced as a FOREIGN KEY in another table.

To illustrate, assuming that in your Loans System, you have two tables, a table containing the different loan types accepted by the system ([dbo].[Loan Type]) and a table containing the loan applications ([dbo].[Loan Application]). The Loan Type ID in the Loan Application table references the Loan Type ID in the Loan Type table.

CREATE TABLE [dbo].[Loan Type] (
[Loan Type ID]	VARCHAR(20) NOT NULL PRIMARY KEY,
[Name]		VARCHAR(50) NOT NULL
)
GO

CREATE TABLE [dbo].[Loan Application] (
    [Loan ID]        INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    [Loan Type ID]   VARCHAR(20)  NOT NULL
                     REFERENCES [dbo].[Loan Type] ( [Loan Type ID] ),
    [Borrower]       VARCHAR(100) NOT NULL
)
GO

Here’s some sample records from the 2 tables:

[dbo].[Loan Type]
Loan Type ID  Name
------------- ------------------
CAR           Car Loan
HOME          Home Loan
HOME EQUITY   Home Equity Loan
PERSONAL      Personal Loan
STUDENT       Student Loan

[dbo].[Loan Application]
Loan ID  Loan Type ID  Borrower             
-------- ------------- -------------------- 
1        HOME          Old MacDonald
2        HOME          Three Little Pigs
3        CAR           Cinderella
4        STUDENT       Peter Pan

Due to changes in business requirements, you may be asked to delete the Student Loan from the available loan types accepted by the company.

DELETE FROM [dbo].[Loan Type]
WHERE [Loan Type ID] = 'STUDENT'
GO

But since there’s an existing record in the Loan Application table that references the Student Loan loan type, you get the following error:

Server: Msg 547, Level 16, State 1, Line 1
DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_Loan Appl_Loan'.
The conflict occurred in database 'TestDb', table 'Loan Application', column 'Loan Type ID'.
The statement has been terminated.

Solution / Work Around:

One way to avoid this error is to first delete all records from the other tables that reference the PRIMARY KEY.

DELETE FROM [dbo].[Loan Application]
WHERE [Loan Type ID] = ‘STUDENT’
GO

DELETE FROM [dbo].[Loan Type]
WHERE [Loan Type ID] = ‘STUDENT’
GO

But deleting records from other tables may not be acceptable because these records may still be needed. An alternative to the physical deletion of the record is the implementation of a logical deletion of the record. This can be done by adding a new column in the table that will determine if the record is still active or not. A bit column can serve as a status flag wherein a value of 1 means that the record is still active while a value of 0 means that the record is not used anymore.

ALTER TABLE [dbo].[Loan Type]
ADD [Status] BIT NOT NULL DEFAULT (1)
GO

UPDATE [dbo].[Loan Type]
SET [Status] = 0
WHERE [Loan Type ID] = ‘STUDENT’
GO

Loan Type ID  Name               Status
------------- ------------------ ------
CAR           Car Loan           1
HOME          Home Loan          1
HOME EQUITY   Home Equity Loan   1
PERSONAL      Personal Loan      1
STUDENT       Student Loan       0

Frequently Asked Questions — SQL Server Error Messages
Frequently Asked Questions — INSERT Statement
Frequently Asked Questions — SELECT Statement

Источник

Transact sql error message Msg 547 Level 16 — The DELETE statement conflicted with the REFERENCE constraint — means that the you try to delete rows with at least one column that have reference in other table.

Msg 547 Level 16 Example:

Table students

id	fist_name	last_name	gender	city	country	dep_id
1	Tom	WHITE	M	Los Angeles	US	2
2	Michael	JONES	M	New York	US	3
8	Daniel	MILLER	M	New York	US	1

Table departments

id	name
1	Anthropology
2	Biology
3	Chemistry
4	Computer Science

Invalid delete:

USE model; GO DELETE FROM departments WHERE id=1 ; GO

Message
Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint «FK__students__dep_id__19DFD96B». The conflict occurred in database «model», table «dbo.students», column ‘dep_id’. The statement has been terminated.

Correct delete:

USE model; GO DELETE FROM students WHERE dep_id=1; GO DELETE FROM departments WHERE id=1; GO

Message
(1 row(s) affected)
(1 row(s) affected)

Other error messages:

Conversion failed when converting date and/or time from character string
Is not a defined system type
Conversion failed when converting the varchar value
Unknown object type used in a CREATE, DROP, or ALTER statement
Cannot insert the value NULL into column
Cannot insert explicit value for identity column in table
The INSERT statement conflicted with the FOREIGN KEY constraint

Источник

The delete statement conflicted with the reference constraint ошибка

Question

Answers

Msg 547 Level 16 Example:

Invalid delete:

Correct delete:

Other error messages:

А вот еще интересные материалы: