Database Foreign Keys and Relational Concepts

A foreign key is a column, or a composite combination of up to 32 columns, in a referencing table (the child table) that establishes and enforces a logical link to a primary key, unique constraint, or unique index in a referenced table (the parent table). Defining a foreign key causes SQL Server to enforce referential integrity automatically: every non-null foreign key value in the child table must correspond to an existing, valid row in the parent table, which keeps orphaned child records from ever being created in the first place. The image below shows an example of a foreign key in one table that relates to the primary key in another.

Look first at the emp_state_id column in the Employees table, which records the state an employee lives in. That column refers to a list of possible values held in the States table. The States table has a state_id column that uniquely identifies the name of the state stored in state_name; because state_id uniquely identifies a row in the States table, it's the primary key for that table.

You want to prevent emp_state_id values in the Employees table from ever containing anything that isn't also a valid state_id in the States table. Defining emp_state_id as a foreign key that references the States table's primary key is exactly how you enforce that: once the constraint is in place, SQL Server rejects any insert or update that would set emp_state_id to a value with no matching row in States. Null values are a separate concern that applies to every table, regardless of how its data integrity is otherwise enforced; the next lesson covers them in full.

Foreign keys are both a method of ensuring data integrity and a manifestation of the relationships between tables. When you add a foreign key to a table, you're creating a dependency between the table carrying the foreign key (the referencing table) and the table it points at (the referenced table). After adding a foreign key, any record inserted into the referencing table must have a matching record in the referenced column(s) of the referenced table, unless the foreign key column itself is left null, in which case the row is exempt from the check entirely.

A primary key is a critical part of almost every table, and a foreign key is added to a table in nearly the same way, except that you also have to say which table and column it references. In its simplest single-column form, the syntax looks like this:

<column name> <data type> <nullability>
    FOREIGN KEY REFERENCES <table name>(<column name>)
    [ON DELETE {CASCADE | NO ACTION | SET NULL | SET DEFAULT}]
    [ON UPDATE {CASCADE | NO ACTION | SET NULL | SET DEFAULT}]

It helps to see the enforcement itself in action. Suppose the States table only contains rows for state_id values 1 through 50, and an application tries to insert a new employee with emp_state_id set to 99:

INSERT INTO dbo.Employees (emp_id, emp_address, emp_city, emp_state_id, emp_zip)
VALUES (1042, '14 Birch Lane', 'Fairview', 99, '00000');

SQL Server rejects the statement outright, with an error naming the specific constraint that blocked it, something close to Msg 547: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Employees_States". The conflict occurred in database "HR", table "dbo.States", column 'state_id'. No row is written, and the table is left exactly as it was before the statement ran. That immediate, specific rejection, rather than a silently orphaned row discovered weeks later during a report, is the entire point of defining the constraint in the first place.

When you define a foreign key constraint in SQL Server 2025, the relational engine enforces several strict rules across both the parent and child tables:

• Target requirement. The referenced column or columns in the parent table must already be protected by a primary key constraint, a unique constraint, or a non-filtered unique index. A foreign key can't point at an arbitrary, unprotected column.
• Data type and collation parity. The foreign key column(s) in the child table must have the exact same data type, length, precision, scale, and collation as the referenced column(s) in the parent table. An INT can't reference a BIGINT, and a VARCHAR(50) with a case-sensitive collation can't reference one with a case-insensitive collation, even though both are technically character data.
• Handling of NULL values. Unlike a primary key, a foreign key column allows NULL unless it's explicitly declared NOT NULL. SQL Server only implements the ANSI MATCH SIMPLE rule for composite foreign keys, so if any single column in a composite foreign key is NULL, the engine skips the parent-table existence check for that entire row. That models an optional relationship, zero or one parent row to many children, rather than a mandatory one.
• Self-referencing foreign keys. A foreign key can reference the primary key of its own table, which is how you model hierarchical structures, an Employee.ManagerID column referencing Employee.EmployeeID, or nested product categories, without needing a second table at all.

A foreign key does more than validate INSERT and UPDATE statements against the child table. It also governs what happens when someone tries to delete a referenced parent row, or update a parent key value that child rows still depend on. SQL Server supports four referential actions, specified separately for ON DELETE and ON UPDATE:

Referential Action	Behavior on a Parent DELETE or UPDATE	Common Use Case
NO ACTION (default)	Raises a referential integrity error (Msg 547) and rolls back the statement if any matching child rows exist.	Financial records, invoices, and strict audit hierarchies, where a parent must never be deleted while children still exist.
CASCADE	Automatically deletes matching child rows (ON DELETE CASCADE) or updates their foreign key values to match (ON UPDATE CASCADE).	Order headers and line items (Sales.Orders to Sales.OrderDetails), where a child row has no independent meaning once its parent is gone.
SET NULL	Sets the foreign key column(s) in matching child rows to NULL. Requires the child column to be nullable.	Decommissioning a sales territory, or removing an optional SalesRepID, without losing the historical customer order itself.
SET DEFAULT	Sets the foreign key column(s) in matching child rows to their defined DEFAULT constraint value.	Reassigning open tickets or tasks to a "General Queue / Unassigned" default parent ID when the original owner is removed.

SQL Server blocks creating a foreign key with CASCADE, SET NULL, or SET DEFAULT whenever a child table could be reached by more than one cascading path from the same parent table, raising Error 1785. In that kind of diamond-shaped schema, one of the paths has to fall back to NO ACTION and be handled explicitly instead, through a stored procedure or an INSTEAD OF trigger.

One of the most consequential architectural details involving foreign keys is how they interact with indexes, and it catches a lot of people off guard:

1. SQL Server does not auto-index foreign keys. Creating a primary key or unique constraint automatically builds a B-tree index behind it; creating a foreign key constraint does not create any index on the child table at all. The constraint is purely a validation rule unless you add an index yourself.
2. Why you should almost always index a foreign key column anyway. Child-to-parent joins are the most common join pattern in relational workloads, and indexing the foreign key lets the optimizer choose an index seek or merge join instead of a full child-table scan. It matters on the parent side too: whenever a row is deleted from the parent table, SQL Server has to check the child table for any row that still references it, and without an index on that foreign key column, deleting a single parent row forces a full scan of the entire child table, every time.
3. Query optimizer join elimination. When a foreign key is trusted (is_not_trusted = 0 in sys.foreign_keys) and its child column is NOT NULL, the SQL Server 2025 query optimizer can drop the parent table out of the execution plan entirely, for a query that joins the two tables, inside a view, say, but only ever selects columns from the child side. A foreign key disabled during a bulk load and never re-validated loses this optimization until it's explicitly checked again.

Just as a primary key can span more than one column, so can a foreign key. A composite foreign key is required whenever the table it references uses a composite primary key or unique constraint; the child table has to carry a matching column for every column in that key, and all of them have to line up together. Consider an OrderLineItems table that references an OrderItems table keyed on the composite pair (PublicationID, IssueNumber), rather than a single surrogate column:

CONSTRAINT FK_OrderLineItems_OrderItems
    FOREIGN KEY (PublicationID, IssueNumber)
    REFERENCES Catalog.OrderItems(PublicationID, IssueNumber)

Under SQL Server's MATCH SIMPLE rule, a row in the child table only has to leave every one of those columns NULL to be exempt from the check in a strict sense, but in practice, if even one of the two columns is NULL, the whole row is skipped during validation. That's worth keeping in mind when a composite foreign key is meant to be mandatory: making every participating column NOT NULL is the only way to guarantee the check actually runs for every row.

Since join elimination depends on a foreign key being trusted, and a bulk load or an ALTER TABLE ... NOCHECK CONSTRAINT statement can silently leave one untrusted, it's worth knowing how to check. The sys.foreign_keys catalog view exposes exactly that:

SELECT
    fk.name AS ForeignKeyName,
    OBJECT_NAME(fk.parent_object_id) AS ChildTable,
    OBJECT_NAME(fk.referenced_object_id) AS ParentTable,
    fk.delete_referential_action_desc AS OnDelete,
    fk.update_referential_action_desc AS OnUpdate,
    fk.is_not_trusted AS IsNotTrusted
FROM sys.foreign_keys AS fk
WHERE fk.parent_object_id = OBJECT_ID('Production.Products');

A value of 1 in IsNotTrusted means the optimizer can't rely on that constraint for join elimination or other plan-shape assumptions, even though the constraint is still enforced going forward for new writes. Running the WITH CHECK CHECK CONSTRAINT statement shown earlier, against every row already in the table, is what clears that flag back to trusted.

The script below demonstrates a parent-child foreign key, a self-referencing foreign key, cascading and set-null-style actions, a supporting nonclustered index on the child's foreign key column, and how to re-validate a foreign key that was temporarily disabled:

-- 1. Parent table
CREATE TABLE Production.Categories (
    CategoryID INT IDENTITY(1,1) CONSTRAINT PK_Categories PRIMARY KEY,
    CategoryName NVARCHAR(100) NOT NULL CONSTRAINT UQ_Categories_Name UNIQUE,
    ParentCategoryID INT NULL,
    -- Self-referencing foreign key for category hierarchy
    CONSTRAINT FK_Categories_ParentCategory
        FOREIGN KEY (ParentCategoryID) REFERENCES Production.Categories(CategoryID)
        ON DELETE NO ACTION
);
GO

-- 2. Child table with cascading and self-referencing foreign keys
CREATE TABLE Production.Products (
    ProductID INT IDENTITY(1,1) CONSTRAINT PK_Products PRIMARY KEY,
    SKU VARCHAR(30) NOT NULL CONSTRAINT UQ_Products_SKU UNIQUE,
    CategoryID INT NOT NULL,
    DiscontinuedReplacementID INT NULL,
    Specs JSON NOT NULL,
    SearchVector VECTOR(1536) NULL,

    -- Mandatory relationship: block category deletion while products exist
    CONSTRAINT FK_Products_Categories
        FOREIGN KEY (CategoryID) REFERENCES Production.Categories(CategoryID)
        ON DELETE NO ACTION
        ON UPDATE CASCADE,

    -- Optional self-reference: clear the pointer if the replacement product is removed
    CONSTRAINT FK_Products_Replacement
        FOREIGN KEY (DiscontinuedReplacementID) REFERENCES Production.Products(ProductID)
        ON DELETE NO ACTION
);
GO

-- 3. Best practice: explicitly index the foreign key column on the child table
CREATE NONCLUSTERED INDEX IX_Products_CategoryID
ON Production.Products(CategoryID)
INCLUDE (SKU);
GO

-- 4. Re-validating a foreign key after a bulk load disabled it,
-- required before the optimizer will trust it again for join elimination
ALTER TABLE Production.Products
    WITH CHECK CHECK CONSTRAINT FK_Products_Categories;
GO

Notice that FK_Products_Replacement references the same table it's defined on, Production.Products, exactly the self-referencing pattern described above, used here to track which product replaced a discontinued one rather than a reporting hierarchy.

Foreign keys benefit directly from two SQL Server 2025 engine-level enhancements:

• Optimized Locking. Earlier SQL Server versions held shared locks on parent key rows and exclusive key locks on child rows for the full duration of a transaction while validating a foreign key, often escalating to a full table lock under heavy load. SQL Server 2025's Optimized Locking instead tracks a Transaction ID (TID) per row, releasing the page and row locks immediately after each row is verified or modified and holding only the TID lock, so foreign key validation no longer blocks concurrent readers and writers the way it used to.
• System-versioned temporal table restrictions. When both the parent and child table are configured as system-versioned temporal tables, ON DELETE CASCADE and ON UPDATE CASCADE are restricted on the current table, to preserve historical point-in-time referential accuracy. Deletions across a temporal parent/child pair have to be coordinated explicitly within a transaction instead of relying on a cascading action.

Before moving on to the next lesson, click the exercise link below for some hands-on practice with primary and foreign keys.
Defining Primary Foreign Keys - Exercise

Now that you've learned how to enforce referential integrity with foreign keys, the next lesson covers how null values are handled across every table, regardless of how its data integrity is otherwise enforced.