Microsoft SQL Server Foreign Keys Maintaining relationship between parent/child rows
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".
