T-SQL Tutorial

Msg 547 Level 16 - The DELETE statement conflicted with the REFERENCE constraint


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

idfist_namelast_namegendercitycountrydep_id
1TomWHITEMLos AngelesUS2
2MichaelJONESMNew YorkUS3
8DanielMILLERMNew YorkUS1

Table departments

idname
1Anthropology
2Biology
3Chemistry
4Computer 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: