https://sqlmaster.mssqlplanner.com

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

Written by

admin

in

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.

Four Constraints, One Job: Reject Bad Data(one schema, four different guarantees)PRIMARY KEYunique + never nullFOREIGN KEYmust exist elsewhereUNIQUEno dupes, 1 NULL okCHECKmust pass a ruleStaffstaff_id (PK)email (UNIQUE)department_id (FK)salary (CHECK > 0)one row per stafferGotcha: UNIQUE allows oneNULL row (NULLs aren’t equalto each other) — PRIMARY KEYnever allows any NULL.INSERT salary = 50000INSERT salary = -100CHECK constraint blocks it

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

Constraint Guarantees
PRIMARY KEY Uniquely identifies every row; implies NOT NULL + UNIQUE; a table can have only one
FOREIGN KEY Value must exist in the referenced table’s PK (or be NULL, if the FK column allows it)
UNIQUE No two rows share this value — but unlike PK, allows one NULL (NULL isn’t considered equal to another NULL, even here), and a table can have several UNIQUE constraints
CHECK Value must satisfy a boolean expression, evaluated on every INSERT/UPDATE

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)
Practice tip: Always name your own constraints in real schemas. “Violation of CHECK constraint CK_Staff_PositiveSalary” tells you and your teammates exactly what rule broke; an auto-generated name with a random suffix tells you nothing without looking it up.

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);
Common mistake: Adding a CHECK constraint with WITH NOCHECK to skip validating existing rows, then assuming the constraint is fully trustworthy going forward. It isn’t — existing violating rows stay in the table untouched, and the constraint is marked “not trusted,” which means the query optimizer can’t safely use it to simplify certain queries either. Only use WITH NOCHECK when you deliberately intend to clean up existing violations separately, and re-validate with WITH CHECK CHECK CONSTRAINT ALL once you have.

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;
Practice tip: Design the full Staff/Department schema above yourself from a blank query window, including at least one intentional constraint violation for each of PK, FK, UNIQUE, and CHECK, and read each actual error message SQL Server gives you. Recognizing these four error shapes on sight is a genuinely useful, permanent skill.

Enjoyed this?

Subscribe to get every new SQL Server lesson as soon as it’s published, and share it with a developer who’d find it useful.

📡 Subscribe via RSS  | 
Share on X  | 
Share on LinkedIn  | 
Share on Facebook

Want the full structured course with quizzes, projects, and 10+ exercises per chapter? Check out SQL Server Fundamentals, coming soon on this site.

More posts