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