A foreign key in SQL Server is a column or set of columns that is used to establish and enforce a link between the data in two tables. The foreign key in the child table references the primary key in the parent table.

Foreign keys are used to enforce referential integrity, which ensures that the relationships between tables are maintained and that data is consistent across the database. It prevents actions that would create orphaned records in a child table.

Foreign keys can also be used to create relationships between tables, such as one-to-one, one-to-many, and many-to-many relationships. They can also be used to restrict or cascade updates or deletes to related data.

Foreign keys are defined after the table is created, and they can be added or dropped at any time without affecting the data in the table.

Add Constraint Foreign Key Example

To Create a foreign key in an existing table, use the command alter table with add constraint.

Employees table

USE tempdb;
GO
CREATE TABLE dbo.EMPLOYEES(
ID INT NULL,
NAME VARCHAR (250) NULL,
JOB VARCHAR (30) NULL,
DEPT_ID INT NULL );
GO

Departments table

USE tempdb;
GO
CREATE TABLE dbo.departments(
id int NOT NULL,
name varchar(250) );
GO

Create Constraint Foreign Key

USE tempdb;
GO
ALTER TABLE dbo.EMPLOYEES
ADD CONSTRAINT FK_DEPT_ID FOREIGN KEY(DEPT_ID)
REFERENCES dbo.DEPARTMENTS(ID);
GO