PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK, DEFAULT, and Constraint Trust

Learning outcomes

ServiceHub receives a bulk import from an old system. The import tool disables constraints “for speed,” loads rows, and then re-enables the constraints. The application appears healthy, but a technician row referenced by several work orders does not exist. The team checks Object Explorer, sees the foreign key name, and concludes referential integrity is guaranteed. SQL Server can tell a more precise story: a constraint can exist, be enabled for future changes, yet still be untrusted because existing rows were never validated.

1. Each constraint type answers a different integrity question

A PRIMARY KEY identifies each row and is both unique and non-null; SQL Server backs it with a unique index unless you specify another valid implementation. A UNIQUE constraint enforces uniqueness for a candidate key and also creates a unique index. A FOREIGN KEY requires values in the referencing columns to correspond to a candidate key in another table (subject to NULL semantics and referential actions). A CHECK constraint validates a Boolean-like predicate. A DEFAULT supplies a value when an INSERT omits the column or explicitly uses DEFAULT; it does not validate arbitrary values. NOT NULL is column nullability, not a named check substitute.

USE ServiceHubLab;GOIF SCHEMA_ID(N'lab04') IS NULL EXEC(N'CREATE SCHEMA lab04 AUTHORIZATION dbo;');GODROP TABLE IF EXISTS lab04.ImportWorkOrder;DROP TABLE IF EXISTS lab04.ImportTechnician;GOCREATE TABLE lab04.ImportTechnician(    technician_id int NOT NULL,    technician_code varchar(12) NOT NULL,    is_active bit NOT NULL CONSTRAINT DF_ImportTechnician_Active DEFAULT (1),    CONSTRAINT PK_ImportTechnician PRIMARY KEY (technician_id),    CONSTRAINT UQ_ImportTechnician_Code UNIQUE (technician_code));GOCREATE TABLE lab04.ImportWorkOrder(    work_order_id int NOT NULL,    technician_id int NULL,    priority tinyint NOT NULL,    status varchar(16) NOT NULL,    CONSTRAINT PK_ImportWorkOrder PRIMARY KEY (work_order_id),    CONSTRAINT FK_ImportWorkOrder_Technician FOREIGN KEY (technician_id)        REFERENCES lab04.ImportTechnician(technician_id),    CONSTRAINT CK_ImportWorkOrder_Priority CHECK (priority BETWEEN 1 AND 5),    CONSTRAINT CK_ImportWorkOrder_Status CHECK (status IN ('new','assigned','closed')));GO

The PRIMARY KEY and UNIQUE declarations need unique access structures to enforce uniqueness. The foreign key does not automatically create an index on ImportWorkOrder.technician_id. An index there can be valuable for joins, parent-row deletes/updates, and validation, but it is a workload design decision—not an automatic side effect of declaring the foreign key.

2. Inspect enforcement metadata instead of trusting names

USE ServiceHubLab;GOSELECT kc.name AS constraint_name,       kc.type_desc,       i.name AS backing_index,       i.is_uniqueFROM sys.key_constraints AS kcJOIN sys.indexes AS i  ON i.object_id = kc.parent_object_id AND i.index_id = kc.unique_index_idWHERE kc.parent_object_id IN      (OBJECT_ID(N'lab04.ImportTechnician'), OBJECT_ID(N'lab04.ImportWorkOrder'));SELECT name, is_disabled, is_not_trustedFROM sys.foreign_keysWHERE parent_object_id = OBJECT_ID(N'lab04.ImportWorkOrder');SELECT name, is_disabled, is_not_trusted, definitionFROM sys.check_constraintsWHERE parent_object_id = OBJECT_ID(N'lab04.ImportWorkOrder');GO

is_disabled = 1 means SQL Server is not enforcing the constraint for incoming modifications. is_not_trusted = 1 means SQL Server has not verified that all relevant existing rows satisfy the constraint. Those states matter separately. A re-enabled but untrusted constraint can protect future DML while leaving historical bad data in place.

3. Deliberately create the classic untrusted-constraint incident

The following sequence simulates an unsafe bulk-load runbook. It disables one foreign key, inserts a row that violates referential integrity, then re-enables enforcement without validating existing rows. The object still exists and future inserts are checked, but the old bad row remains.

USE ServiceHubLab;GOINSERT lab04.ImportTechnician(technician_id, technician_code)VALUES (1,'T-IMPORT-1');INSERT lab04.ImportWorkOrder(work_order_id, technician_id, priority, status)VALUES (100,1,3,'assigned');GOALTER TABLE lab04.ImportWorkOrder NOCHECK CONSTRAINT FK_ImportWorkOrder_Technician;GOINSERT lab04.ImportWorkOrder(work_order_id, technician_id, priority, status)VALUES (101,999,2,'new');GOALTER TABLE lab04.ImportWorkOrder CHECK CONSTRAINT FK_ImportWorkOrder_Technician;GOSELECT name, is_disabled, is_not_trustedFROM sys.foreign_keysWHERE name = N'FK_ImportWorkOrder_Technician';GO

The expected state is is_disabled = 0 but is_not_trusted = 1. That distinction is the heart of the lesson. SQL Server is enforcing the foreign key for subsequent changes, yet it cannot claim that every existing row satisfies it.

4. Detect the actual violating rows before trying to restore trust

Do not blindly run WITH CHECK CHECK CONSTRAINT and hope. First identify violations and decide whether to correct the child row, restore the missing parent, quarantine the record, or reject the import. DBCC CHECKCONSTRAINTS can enumerate FOREIGN KEY and CHECK violations for a table.

USE ServiceHubLab;GODBCC CHECKCONSTRAINTS ('lab04.ImportWorkOrder') WITH ALL_CONSTRAINTS, ALL_ERRORMSGS;GOSELECT w.*FROM lab04.ImportWorkOrder AS wLEFT JOIN lab04.ImportTechnician AS t  ON t.technician_id = w.technician_idWHERE w.technician_id IS NOT NULL  AND t.technician_id IS NULL;GO-- Lab repair: the imported technician ID was wrong.UPDATE lab04.ImportWorkOrderSET technician_id = 1WHERE work_order_id = 101;GOALTER TABLE lab04.ImportWorkOrderWITH CHECK CHECK CONSTRAINT FK_ImportWorkOrder_Technician;GOSELECT name, is_disabled, is_not_trustedFROM sys.foreign_keysWHERE name = N'FK_ImportWorkOrder_Technician';GO

After validation succeeds, the expected trust flag is zero. That metadata is stronger evidence than “the constraint command ran.” It still does not replace business validation of whether technician ID 1 was semantically the correct repair.

5. CHECK and DEFAULT have their own traps

A CHECK constraint evaluates its predicate for inserted/updated rows, but SQL three-valued logic still applies: if a nullable expression evaluates UNKNOWN, the check is not FALSE. A DEFAULT is even less restrictive: it fills omitted values but does not prevent callers from supplying another legal SQL value. If the business rule says a value is mandatory and bounded, use nullability plus a CHECK, not a DEFAULT as a surrogate validator.

USE ServiceHubLab;GOINSERT lab04.ImportTechnician(technician_id, technician_code)VALUES (2,'T-IMPORT-2');SELECT technician_id, technician_code, is_activeFROM lab04.ImportTechnicianWHERE technician_id = 2;GO-- DEFAULT supplied 1 because is_active was omitted.-- It would not stop an explicit 0 because 0 is valid for bit.

Constraint design should map directly to the invariant: uniqueness, referential relationship, allowed domain, omission default, or nullability. Avoid “one constraint to rule them all” designs that hide intent.

6. Hands-on lab: integrity evidence card

USE ServiceHubLab;GOSELECT fk.name,       fk.is_disabled,       fk.is_not_trusted,       OBJECT_SCHEMA_NAME(fk.parent_object_id) AS child_schema,       OBJECT_NAME(fk.parent_object_id) AS child_table,       OBJECT_SCHEMA_NAME(fk.referenced_object_id) AS parent_schema,       OBJECT_NAME(fk.referenced_object_id) AS parent_tableFROM sys.foreign_keys AS fkWHERE fk.parent_object_id = OBJECT_ID(N'lab04.ImportWorkOrder');SELECT cc.name, cc.is_disabled, cc.is_not_trusted, cc.definitionFROM sys.check_constraints AS ccWHERE cc.parent_object_id = OBJECT_ID(N'lab04.ImportWorkOrder');SELECT i.name, i.is_unique, i.is_primary_key, i.is_unique_constraintFROM sys.indexes AS iWHERE i.object_id IN      (OBJECT_ID(N'lab04.ImportTechnician'), OBJECT_ID(N'lab04.ImportWorkOrder'))  AND i.index_id > 0ORDER BY i.object_id, i.index_id;GO

Verification checklist

• You observed an enabled-but-untrusted foreign key.
• You found the violating row before attempting to restore trust.
• You repaired the data and used WITH CHECK CHECK CONSTRAINT.
• You verified PRIMARY KEY/UNIQUE backing indexes and did not claim the foreign key created a child index automatically.
• You can explain why DEFAULT, CHECK and NOT NULL are different integrity mechanisms.

USE ServiceHubLab;GODROP TABLE IF EXISTS lab04.ImportWorkOrder;DROP TABLE IF EXISTS lab04.ImportTechnician;GO

7. Production judgment and next bridge

Disabling constraints is an operational change with correctness and optimizer consequences. If a load process must do it, record the exact constraints, validate incoming data, preserve rollback/reject evidence, and verify both is_disabled and is_not_trusted afterward. Do not automatically create an index for every foreign key, but evaluate referencing-column indexes from join/delete/update patterns and measured plans.

Lesson 3 turns to identifier generation. An identifier can be unique without being gap-free, sequential without being contention-free, globally unique without being small, and suitable as a surrogate key without being suitable as a business document number.

Authoritative references

• sys.foreign_keys — enabled/trusted metadata for foreign keys
• sys.check_constraints — enabled/trusted metadata and check definitions
• DBCC CHECKCONSTRAINTS — finding FOREIGN KEY and CHECK violations
• SQL Server 2025 build versions — current engine baseline