In SQL Server, a FOREIGN KEY constraint is used to enforce referential integrity between two tables by establishing a relationship between a column (or a set of columns) in one table (child table) and the PRIMARY KEY or a UNIQUE key column(s) in another table (parent table). This ensures that values in the foreign key column(s) must match existing values in the referenced primary or unique key column(s) or be NULL.

Key Features of FOREIGN KEY

Referential Integrity:

Prevents invalid data from being entered into the child table.
Ensures that every value in the foreign key column matches a valid value in the referenced table.

Cascading Actions:

SQL Server supports cascading updates and deletes to maintain consistency when changes are made in the parent table. These include:

ON DELETE CASCADE: Automatically deletes rows in the child table when the corresponding row in the parent table is deleted.
ON UPDATE CASCADE: Automatically updates foreign key values in the child table when the corresponding value in the parent table is updated.

Multiple FOREIGN KEY Constraints:

A table can have multiple foreign key constraints referencing different parent tables.

Nullability:

Foreign key columns can accept NULL values unless the column is explicitly defined as NOT NULL.

Syntax

Here is the general syntax for creating a foreign key constraint:

-- Creating a table with a FOREIGN KEY constraint
CREATE TABLE ChildTable (
    ChildID INT PRIMARY KEY,
    ParentID INT,
    FOREIGN KEY (ParentID) REFERENCES ParentTable(ParentID)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

Example

Let’s illustrate with an example involving two tables: Orders and Customers.

Parent Table (Customers):

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName NVARCHAR(100)
);

Child Table (Orders):

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

In this setup:

The CustomerID in the Orders table is a foreign key referencing the CustomerID in the Customers table.
If a customer is deleted from the Customers table, their associated orders in the Orders table are also deleted (ON DELETE CASCADE).

Managing Foreign Keys

Adding a FOREIGN KEY to an Existing Table:

ALTER TABLE Orders
ADD CONSTRAINT FK_CustomerOrders
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);

Dropping a FOREIGN KEY:

ALTER TABLE Orders
DROP CONSTRAINT FK_CustomerOrders;

Benefits

Enforces data consistency across related tables.
Reduces redundancy by linking tables.

Limitations

Foreign key constraints may impact performance during large inserts or updates.
Cascading operations should be used cautiously to avoid unintended data deletions or updates.

By using FOREIGN KEY constraints, SQL Server helps maintain the integrity and reliability of relational data.