Constraints

Learn PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, and DEFAULT constraints in SQL Server, with practical examples of each.

Introduction

A table with no rules is only as reliable as the code writing to it — nothing stops a bug from inserting duplicate IDs, orphaned references, or nonsense values. Constraints let SQL Server enforce those rules itself, at the database level, so bad data simply cannot get in, no matter which application or script is writing to the table. This lesson covers the five constraint types you'll use constantly: PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, and DEFAULT.

PRIMARY KEY

A PRIMARY KEY uniquely identifies every row in a table. It combines two rules automatically: the value must be unique across all rows, and it can never be NULL. Every table should have one.

USE SchoolDB;
GO

CREATE TABLE Courses (
    CourseID INT NOT NULL PRIMARY KEY,
    CourseName NVARCHAR(100) NOT NULL
);

Messages

Click Run to see what this code prints.

FOREIGN KEY

A FOREIGN KEY links a column in one table to the PRIMARY KEY of another, enforcing that any value stored there must already exist in the referenced table. This is how SQL Server prevents orphaned data — for example, an enrollment record pointing at a course that doesn't exist.

CREATE TABLE Enrollments (
    EnrollmentID INT NOT NULL PRIMARY KEY,
    StudentID INT NOT NULL,
    CourseID INT NOT NULL,
    EnrolledOn DATE NOT NULL,
    CONSTRAINT FK_Enrollments_Courses
        FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

Messages

Click Run to see what this code prints.

-- This fails because CourseID 999 doesn't exist in Courses:
INSERT INTO Enrollments (EnrollmentID, StudentID, CourseID, EnrolledOn)
VALUES (1, 101, 999, '2026-08-05');

Messages

Click Run to see what this code prints.

UNIQUE Constraint

UNIQUE enforces that no two rows share the same value in a column, but unlike PRIMARY KEY, it allows NULL (typically one NULL, since NULL is never considered equal to another NULL). Use it for columns like email addresses, where uniqueness matters but the column isn't the table's main identifier.

ALTER TABLE Students
ADD CONSTRAINT UQ_Students_Email UNIQUE (Email);

Messages

Click Run to see what this code prints.

CHECK Constraint

CHECK enforces a boolean condition that every row's data must satisfy. It's ideal for simple business rules, like requiring a value to fall within a valid range.

ALTER TABLE Enrollments
ADD CONSTRAINT CHK_Enrollments_EnrolledOn
    CHECK (EnrolledOn >= '2020-01-01');

Messages

Click Run to see what this code prints.

DEFAULT Constraint

DEFAULT supplies an automatic value for a column when an INSERT statement doesn't specify one, sparing every caller from having to repeat common values like 'today's date' or 'active by default'.

ALTER TABLE Students
ADD CONSTRAINT DF_Students_IsActive DEFAULT (1) FOR IsActive;

-- Now an INSERT that omits IsActive automatically gets 1 (true):
INSERT INTO Students (StudentID, FirstName, LastName, EnrollmentDate)
VALUES (201, 'Karan', 'Mehta', GETDATE());

SELECT StudentID, FirstName, IsActive FROM Students WHERE StudentID = 201;

Result

Click Run to see what this code prints.

Combining Constraints

Real tables typically use several constraints together. Here's a single CREATE TABLE that combines PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, and DEFAULT in one statement, which is usually cleaner than adding each one afterward with ALTER TABLE.

CREATE TABLE Instructors (
    InstructorID INT NOT NULL PRIMARY KEY,
    FullName NVARCHAR(100) NOT NULL,
    Email NVARCHAR(100) NOT NULL UNIQUE,
    Salary DECIMAL(10, 2) NOT NULL CHECK (Salary > 0),
    HireDate DATE NOT NULL DEFAULT (GETDATE())
);

Messages

Click Run to see what this code prints.

Common Mistakes

Avoid These Mistakes

Creating tables with no PRIMARY KEY at all, making individual rows impossible to reliably reference or update.
Forgetting that a FOREIGN KEY requires the referenced column to already be a PRIMARY KEY or have a UNIQUE constraint.
Assuming UNIQUE behaves exactly like PRIMARY KEY — UNIQUE columns can still accept NULL, PRIMARY KEY columns cannot.
Writing an overly strict CHECK constraint that rejects legitimate data the business didn't anticipate at design time.

Best Practices

Give every table a PRIMARY KEY, even simple lookup or junction tables.
Name constraints explicitly (like FK_Enrollments_Courses) rather than letting SQL Server auto-generate cryptic names — this makes error messages and later changes far easier to work with.
Add FOREIGN KEY constraints for every real relationship between tables, rather than relying on application code alone to keep data consistent.
Use DEFAULT for predictable values like creation timestamps or active flags, reducing repetition in every INSERT statement.

Frequently Asked Questions

Can a table have more than one PRIMARY KEY?

No, a table can have only one PRIMARY KEY, though that key can span multiple columns (a composite key) if uniqueness depends on the combination of several values.

Does a UNIQUE constraint allow multiple NULLs?

In SQL Server, a UNIQUE constraint allows at most one NULL value in that column, since SQL Server treats NULL as unknown rather than as a comparable duplicate value in this context.

What happens if I try to delete a row that other rows depend on via FOREIGN KEY?

SQL Server blocks the DELETE by default, raising a foreign key conflict error, unless the constraint was defined with ON DELETE CASCADE or a similar referential action.

Do I need to name every constraint?

No, SQL Server will auto-generate a name if you omit one, but explicit names (using CONSTRAINT ConstraintName) make errors and future maintenance much clearer, so it's strongly recommended.

Key Takeaways

PRIMARY KEY enforces uniqueness and non-null on a table's identifying column(s).
FOREIGN KEY enforces that a column's values must exist in a referenced table's key, preventing orphaned data.
UNIQUE enforces no-duplicates on a non-key column, while still permitting one NULL.
CHECK enforces a custom boolean rule; DEFAULT supplies an automatic value when one isn't provided.

Summary

Constraints are how SQL Server protects your data's integrity at the source. In the final lesson of this batch, you'll look at IDENTITY columns — SQL Server's mechanism for automatically generating primary key values, and how to retrieve the value it just generated.

Next Lesson →

Identity Columns