Microsoft SQL Server Foreign Keys Foreign key relationship/constraint
Example
Foreign keys enables you to define relationship between two tables. One (parent) table need to have primary key that uniquely identifies rows in the table. Other (child) table can have value of the primary key from the parent in one of the columns. FOREIGN KEY REFERENCES constraint ensures that values in child table must exist as a primary key value in the parent table.
In this example we have parent Company table with CompanyId primary key, and child Employee table that has id of the company where this employee works.
create table Company (
CompanyId int primary key,
Name nvarchar(200)
)
create table Employee (
EmployeeId int,
Name nvarchar(200),
CompanyId int
foreign key references Company(companyId)
)
foreign key references ensures that values inserted in Employee.CompanyId column must also exist in Company.CompanyId column. Also, nobody can delete company in company table if there is ate least one employee with a matching companyId in child table.
FOREIGN KEY relationship ensures that rows in two tables cannot be "unlinked".
