T-SQL Tutorial

Msg 547 Level 16 - The INSERT statement conflicted with the FOREIGN KEY constraint


Transact sql error message Msg 547 Level 16 - The INSERT statement conflicted with the FOREIGN KEY constraint - means that the insert value do not exist in the references table.

Msg 547 Level 16 Example:

Table students

idfist_namelast_namegendercitycountrydep_id
1TomWHITEMLos AngelesUS2
2MichaelJONESMNew YorkUS3

Table departments

idname
1Anthropology
2Biology
3Chemistry
4Computer Science




Invalid insert:

USE model;
GO
insert into students(id,first_name, last_name, gender, city, country, dep_id)
values(8,'Daniel','MILLER','M','New York','US',32);
GO

Message
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__students__dep_id__19DFD96B". The conflict occurred in database "model", table "dbo.departments", column 'ID'. The statement has been terminated.

Correct insert:

USE model;
GO
insert into students(id,first_name, last_name, gender, city, country, dep_id)
values(8,'Daniel','MILLER','M','New York','US',1);
GO

Message
(1 row(s) affected)

Other error messages: