Microsoft SQL Server Foreign Keys Maintaining relationship between parent/child rows

Help us to keep this website almost Ad Free! It takes only 10 seconds of your time:
> Step 1: Go view our video on YouTube: EF Core Bulk Extensions
> Step 2: And Like the video. BONUS: You can also share it!

Example

Let's assume that we have one row in Company table with companyId 1. We can insert row in employee table that has companyId 1:

insert into Employee values (17, 'John', 1)

However, we cannot insert employee that has non-existing CompanyId:

insert into Employee values (17, 'John', 111111)

Msg 547, Level 16, State 0, Line 12 The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Employee__Compan__1EE485AA". The conflict occurred in database "MyDb", table "dbo.Company", column 'CompanyId'. The statement has been terminated.

Also, we cannot delete parent row in company table as long as there is at least one child row in employee table that references it.

delete from company where CompanyId = 1

Msg 547, Level 16, State 0, Line 14 The DELETE statement conflicted with the REFERENCE constraint "FK__Employee__Compan__1EE485AA". The conflict occurred in database "MyDb", table "dbo.Employee", column 'CompanyId'. The statement has been terminated.

Foreign key relationship ensures that Company and employee rows will not be "unlinked".



Got any Microsoft SQL Server Question?