PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK: SQL Server Constraints Explained

You’ve met each of these individually already — PK and FK in Chapter 5, NOT NULL and DEFAULT in Chapter 2. This lesson brings the full constraint family together in one place, adds UNIQUE and CHECK, and is explicit about exactly what each one guarantees and how it fails.

CREATE TABLE dbo.Department (
    department_id   INT IDENTITY(1,1) PRIMARY KEY,
    department_name NVARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE dbo.Staff (
    staff_id       INT IDENTITY(1,1) PRIMARY KEY,
    email          NVARCHAR(100) NOT NULL UNIQUE,
    department_id  INT NOT NULL REFERENCES dbo.Department(department_id),
    salary         DECIMAL(10,2) NOT NULL CHECK (salary > 0)
);

What Each One Guarantees

Watching Them Do Their Job

INSERT INTO dbo.Staff (email, department_id, salary) VALUES ('bad@co.com', 999, 50000);
-- Error: FOREIGN KEY constraint... department_id 999 doesn't exist

INSERT INTO dbo.Staff (email, department_id, salary) VALUES ('bad2@co.com', 1, -100);
-- Error: CHECK constraint "CK_Staff_salary" violated

INSERT INTO dbo.Staff (email, department_id, salary) VALUES ('taken@co.com', 1, 60000);
INSERT INTO dbo.Staff (email, department_id, salary) VALUES ('taken@co.com', 1, 65000);
-- Error: Violation of UNIQUE KEY constraint... duplicate email

Naming Your Constraints on Purpose

-- Unnamed — SQL Server auto-generates a name like CK__Staff__salary__1234ABCD
salary DECIMAL(10,2) NOT NULL CHECK (salary > 0)

-- Named explicitly — readable in error messages and easy to ALTER/DROP later
CONSTRAINT CK_Staff_PositiveSalary CHECK (salary > 0)

Adding a Constraint to an Existing Table

-- The table already exists; add the rule after the fact
ALTER TABLE dbo.Staff ADD CONSTRAINT CK_Staff_ValidEmail CHECK (email LIKE '%_@_%._%');

-- Temporarily allow existing bad rows to be re-checked separately (rare, use with care)
ALTER TABLE dbo.Staff WITH NOCHECK ADD CONSTRAINT CK_Staff_PositiveSalary CHECK (salary > 0);

What Happens When You Try to Delete a Constraint’s “Reason”

-- Trying to drop a department that staff still reference:
DELETE FROM dbo.Department WHERE department_id = 1;
-- Error: The DELETE statement conflicted with the REFERENCE constraint

-- The correct sequence: remove or reassign dependents first
UPDATE dbo.Staff SET department_id = 2 WHERE department_id = 1;
DELETE FROM dbo.Department WHERE department_id = 1;

Why Push Business Rules Into Your SQL Server Schema, Not Just the App

Constraints aren’t just data hygiene — they’re how you encode real business rules directly into the schema, so they can never be silently bypassed by a buggy application, a second app added later that talks to the same database, or a stray ad-hoc UPDATE run during an incident.

Encoding a Real Rule

-- Rule: an order's ship date can never be before its order date
CREATE TABLE dbo.CustomerOrder (
    order_id    INT IDENTITY(1,1) PRIMARY KEY,
    order_date  DATE NOT NULL,
    ship_date   DATE NULL
        CHECK (ship_date IS NULL OR ship_date >= order_date),
    total_usd   DECIMAL(10,2) NOT NULL CHECK (total_usd >= 0)
);

-- Proving the rule holds, not just reading about it:
INSERT INTO dbo.CustomerOrder (order_date, ship_date, total_usd) VALUES ('2026-01-10', '2026-01-05', 49.99);
-- Error: CHECK constraint violated — shipped 5 days BEFORE it was ordered, correctly rejected

A CHECK Constraint Spanning Multiple Columns

CHECK isn’t limited to validating one column against a literal — it can compare columns on the same row to each other, as shown above (ship_date against order_date). Another common shape:

CREATE TABLE dbo.Promotion (
    promotion_id  INT IDENTITY(1,1) PRIMARY KEY,
    starts_on     DATE NOT NULL,
    ends_on       DATE NOT NULL,
    CHECK (ends_on > starts_on)
);

This kind of cross-column rule is exactly the class of business logic that’s easy to forget to validate in one code path of an application (a bulk-import script, an admin panel, an API endpoint added six months later by someone unfamiliar with the original rule) but structurally impossible to skip once it lives in the schema.

The Last Line of Defense

Applications get replaced, have bugs, or get bypassed by a direct database script during an incident. A CHECK constraint at the database layer is enforced no matter what wrote the data — it’s the guarantee application code alone can never fully provide. This is sometimes summarized as “defense in depth”: validate in the application for a fast, friendly error message to the user, and validate in the database as the guarantee that actually holds under all circumstances.

Where This Doesn’t Reach — And What Does

CHECK constraints are limited to logic expressible within a single row’s own columns — they can’t reference other tables or aggregate across rows. “A department can’t have more than 20 staff” or “an order’s total must match the sum of its line items” needs a different tool: a trigger, or logic in a stored procedure. That’s a deliberate scope boundary you’ll meet by name (constraints vs. triggers vs. procedures) as a full decision framework in SQL Server for Developers & DBAs — for now, the key lesson is simply that single-row rules belong in CHECK constraints, full stop, because nothing enforces them more reliably.