SQL Server foreign keys: indexing and restoring trust

A data load finishes successfully after foreign keys were temporarily disabled. The application resumes, and new inserts appear to be checked. Months later, a parent deletion becomes slow and a reporting query finds orders without customers. Two separate concerns have been mixed together: whether a constraint validates existing data, and whether an index makes relationship checks efficient.

A foreign key describes an enforced relationship. It does not automatically create an index on the referencing child columns. A primary key or unique constraint on the parent provides a valid referenced key, but SQL Server may still need to search the child table when checking a parent update or deletion. That distinction matters most when a small parent operation touches a very large child table.

Inspect enforcement and trust separately

Start in the affected database with the catalog query below. Metadata visibility follows your permissions, so an incomplete list under a restricted login is not proof that the database has no problematic constraints.

SELECT
    OBJECT_SCHEMA_NAME(fk.parent_object_id) AS ChildSchema,
    OBJECT_NAME(fk.parent_object_id) AS ChildTable,
    fk.name,
    fk.is_disabled,
    fk.is_not_trusted,
    fk.delete_referential_action_desc
FROM sys.foreign_keys AS fk
WHERE fk.is_disabled = 1 OR fk.is_not_trusted = 1;

An enabled constraint can be untrusted. Enabling it without checking old rows may protect future changes while leaving historical violations in place. SQL Server cannot safely use an untrusted relationship for the same optimizer assumptions as a validated one. Do not infer trust from a successful new insert or from the constraint name appearing in a management tool.

A disabled constraint is a different situation: future writes are not protected by that foreign-key enforcement. Record why it was disabled, which process owns the exception, and what happened to data during that interval. If the workload has continued, validating only the rows from the original bulk load is insufficient.

Fix the data before declaring the relationship valid

The next statements are an adaptation pattern for existing practice tables dbo.Orders and dbo.Customers, not a universal cleanup script. Replace names after inspecting the actual relationship. The anti-join identifies non-null customer IDs that have no parent; it does not decide whether those orders should be deleted, corrected, or matched to a missing customer.

SELECT o.CustomerId, COUNT_BIG(*) AS OrphanRows
FROM dbo.Orders AS o
LEFT JOIN dbo.Customers AS c ON c.CustomerId=o.CustomerId
WHERE o.CustomerId IS NOT NULL AND c.CustomerId IS NULL
GROUP BY o.CustomerId;

CREATE INDEX IX_Orders_CustomerId
ON dbo.Orders(CustomerId);

ALTER TABLE dbo.Orders
WITH CHECK CHECK CONSTRAINT FK_Orders_Customers;

The two occurrences of CHECK have different roles. WITH CHECK asks SQL Server to validate existing data; CHECK CONSTRAINT enables the constraint. Validation can read substantial data and require locks, so estimate the work and schedule appropriately. Recheck is_disabled and is_not_trusted after execution. A successful command and the catalog state provide stronger evidence than an operator's assumption.

For a nullable child key, NULL can represent no relationship. Do not label every NULL an orphan. Composite foreign keys require matching all participating columns and careful treatment of nullability. Likewise, a repair query based on a display name instead of the declared key may create incorrect associations.

Index the actual relationship workload

Before adding the illustrated child index, inspect existing indexes. A composite index beginning with CustomerId may already support the check and joins. An index with CustomerId only after an unrelated leading key is not generally an equivalent access path. Use plans from the parent deletion and important child queries to judge the choice.

Indexing every foreign key without considering workload adds storage and write amplification. Conversely, omitting an index because the application rarely joins the tables can overlook expensive parent deletions and cascading actions. Measure both directions. Large cascades can generate extensive logging and blocking even with suitable indexes, because the actual child modifications still have to occur.

A practical completion test inserts a valid child, attempts an invalid child in a rollback-controlled test, and exercises a representative parent change in a safe environment. Confirm that the intended application identity receives the expected error and handles it. Constraint trust, referential correctness, and access-path performance are related, but each needs its own evidence.

Technical references: Microsoft Learn: Foreign keys · Microsoft Learn: Constraint trust.