Constraints: Primary, Foreign, Unique, Check and Default – SQL Server | Lesson 10

Published / updated3 Oct 2026

Introduction

Your master tables exist, but they accept anything. Two subjects can share the code MATH. A section can point to a class that does not exist.

In this lesson, you add constraints: rules that SQL Server checks on every insert, update, and delete. Bad data is refused before it is saved.

By the end of this lesson, you will be able to:

Explain the six kinds of constraints and when to use each.
Add UNIQUE, CHECK, DEFAULT, and FOREIGN KEY constraints to existing tables.
Allow only one current academic year with a filtered unique index.
Read the error messages that constraints produce.
Test constraints safely inside a transaction that is undone.

Why this matters

Code has bugs, imports have typos, and someone runs a quick fix directly in SSMS. Every one of those paths can write bad data, and only the database sees all of them.

A constraint is a promise the database keeps forever. "Every section belongs to a real class" stops being a hope and becomes a fact you can rely on in every report.

The concept

A constraint is a rule attached to a table. Think of the security guard at the school gate: every person who enters is checked, no matter which door they came from.

Constraints work the same way. They check every row, no matter which program sends it.

Example: the six kinds of constraints

Constraint	Rule	SMS example
`NOT NULL`	The column must have a value	Every subject has a name
`PRIMARY KEY`	Unique and never NULL; identifies the row	`Subjects.Id`
`UNIQUE`	No two rows share the value	No two subjects with code `MATH`
`CHECK`	The value must pass a test	An academic year ends after it starts
`DEFAULT`	A value used when none is given	A new section gets capacity 40
`FOREIGN KEY`	The value must exist in another table	A section's class must exist

Example: UNIQUE vs PRIMARY KEY

Both stop duplicates. The differences:

A table has one primary key, but can have many unique constraints.
A primary key never allows NULL. A unique constraint allows one NULL.
The primary key is the row's identity; unique constraints protect other real-world values, like Subjects.Code.

A unique constraint can cover several columns together. UQ_Sections_ClassId_Name allows section "A" in every class, but only once per class.

Example: CHECK

A CHECK constraint holds a true-or-false test. The row is saved only if the test is true:

Example
CONSTRAINT CK_AcademicYears_Dates CHECK (EndDate > StartDate)

Example: DEFAULT

A DEFAULT gives a value when an insert leaves the column out:

Example
CONSTRAINT DF_Sections_Capacity DEFAULT (40) FOR Capacity

Example: FOREIGN KEY

A foreign key links a column to the primary key of another table. SQL Server then refuses two things: a section whose ClassId does not exist, and deleting a class that still has sections.

Example
CONSTRAINT FK_Sections_Classes FOREIGN KEY (ClassId) REFERENCES dbo.Classes (Id)

This rule is called referential integrity: every reference points to something real.

Example: what happens on delete

You can tell a foreign key what to do when someone deletes the row it points to:

Option	What happens when a class is deleted	Use it when
`NO ACTION` (the default)	The delete is refused while sections exist	Almost always: school records
`ON DELETE CASCADE`	All its sections are deleted too	Child rows that mean nothing alone, and only after careful thought
`ON DELETE SET NULL`	Each section's `ClassId` becomes NULL	The link is optional

The SMS uses the default everywhere. A cascade that silently deletes sections, then enrollments, then attendance, is how a single mistake erases a year of records.

Example: one current year with a filtered unique index

The school wants exactly one current academic year. A normal UNIQUE on IsCurrent would allow only one row with 0, which is wrong. A filtered unique index applies the rule only to rows that match a condition:

Example
CREATE UNIQUE INDEX UQ_AcademicYears_IsCurrent
    ON dbo.AcademicYears (IsCurrent)
    WHERE IsCurrent = 1;

Now many years can have IsCurrent = 0, but only one can have 1. An index is a sorted lookup structure; you will study indexes in Lesson 26.

Apply it to the SMS

Step 1: add the constraints

ALTER TABLE ... ADD CONSTRAINT adds a rule to an existing table. Each one is wrapped in a check, so the script stays re-runnable. OBJECT_ID takes a type code for each kind of constraint: 'UQ', 'C' (check), 'D' (default), and 'F' (foreign key).

Save this as database\010-constraints.sql:

database/010-constraints.sql
-- Lesson 10: add constraints to the master tables.
-- Safe to run more than once: each constraint is added only if it does not exist yet.
USE SchoolDb;
GO

-- AcademicYears ---------------------------------------------------------------
IF OBJECT_ID(N'dbo.UQ_AcademicYears_Name', N'UQ') IS NULL
    ALTER TABLE dbo.AcademicYears
        ADD CONSTRAINT UQ_AcademicYears_Name UNIQUE (Name);

IF OBJECT_ID(N'dbo.CK_AcademicYears_Dates', N'C') IS NULL
    ALTER TABLE dbo.AcademicYears
        ADD CONSTRAINT CK_AcademicYears_Dates CHECK (EndDate > StartDate);

IF OBJECT_ID(N'dbo.DF_AcademicYears_IsCurrent', N'D') IS NULL
    ALTER TABLE dbo.AcademicYears
        ADD CONSTRAINT DF_AcademicYears_IsCurrent DEFAULT (0) FOR IsCurrent;

-- Only one academic year can be the current one.
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'UQ_AcademicYears_IsCurrent')
    CREATE UNIQUE INDEX UQ_AcademicYears_IsCurrent
        ON dbo.AcademicYears (IsCurrent)
        WHERE IsCurrent = 1;
GO

-- Classes ---------------------------------------------------------------------
IF OBJECT_ID(N'dbo.UQ_Classes_Name', N'UQ') IS NULL
    ALTER TABLE dbo.Classes
        ADD CONSTRAINT UQ_Classes_Name UNIQUE (Name);

IF OBJECT_ID(N'dbo.UQ_Classes_DisplayOrder', N'UQ') IS NULL
    ALTER TABLE dbo.Classes
        ADD CONSTRAINT UQ_Classes_DisplayOrder UNIQUE (DisplayOrder);

IF OBJECT_ID(N'dbo.CK_Classes_DisplayOrder', N'C') IS NULL
    ALTER TABLE dbo.Classes
        ADD CONSTRAINT CK_Classes_DisplayOrder CHECK (DisplayOrder BETWEEN 1 AND 10);
GO

-- Sections --------------------------------------------------------------------
IF OBJECT_ID(N'dbo.FK_Sections_Classes', N'F') IS NULL
    ALTER TABLE dbo.Sections
        ADD CONSTRAINT FK_Sections_Classes FOREIGN KEY (ClassId) REFERENCES dbo.Classes (Id);

IF OBJECT_ID(N'dbo.UQ_Sections_ClassId_Name', N'UQ') IS NULL
    ALTER TABLE dbo.Sections
        ADD CONSTRAINT UQ_Sections_ClassId_Name UNIQUE (ClassId, Name);

IF OBJECT_ID(N'dbo.DF_Sections_Capacity', N'D') IS NULL
    ALTER TABLE dbo.Sections
        ADD CONSTRAINT DF_Sections_Capacity DEFAULT (40) FOR Capacity;

IF OBJECT_ID(N'dbo.CK_Sections_Capacity', N'C') IS NULL
    ALTER TABLE dbo.Sections
        ADD CONSTRAINT CK_Sections_Capacity CHECK (Capacity BETWEEN 1 AND 60);
GO

-- Subjects --------------------------------------------------------------------
IF OBJECT_ID(N'dbo.UQ_Subjects_Code', N'UQ') IS NULL
    ALTER TABLE dbo.Subjects
        ADD CONSTRAINT UQ_Subjects_Code UNIQUE (Code);

IF OBJECT_ID(N'dbo.UQ_Subjects_Name', N'UQ') IS NULL
    ALTER TABLE dbo.Subjects
        ADD CONSTRAINT UQ_Subjects_Name UNIQUE (Name);
GO

-- Check: list every constraint on the master tables.
SELECT
    OBJECT_NAME(parent_object_id) AS TableName,
    name AS ConstraintName,
    type_desc AS ConstraintType
FROM sys.objects
WHERE type IN ('PK', 'UQ', 'F', 'C', 'D')
  AND OBJECT_NAME(parent_object_id) IN ('AcademicYears', 'Classes', 'Sections', 'Subjects')
ORDER BY TableName, ConstraintName;
GO

Press F5. The last query lists 16 constraints: 4 primary keys, 6 unique, 3 check, 2 default, and 1 foreign key.

Example: a sqlcmd setting you must know

If you rebuild with run-all.cmd from Lesson 4, the filtered index fails:

Output
Msg 1934, Level 16, State 1, Line 17
CREATE INDEX failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

SSMS turns the QUOTED_IDENTIFIER setting on by default, but sqlcmd does not. Filtered indexes need it on, and so does every later INSERT, UPDATE, or DELETE on a table that has one. .NET apps turn it on automatically; for sqlcmd, add -I to the sqlcmd line in database\run-all.cmd:

database/run-all.cmd
@echo off
rem Builds SchoolDb by running every database\NNN-*.sql script in order.
rem Usage:  database\run-all.cmd                      (server: localhost)
rem         database\run-all.cmd "(localdb)\MSSQLLocalDB"
setlocal

set "SERVER=%~1"
if "%SERVER%"=="" set "SERVER=localhost"

where sqlcmd >nul 2>nul
if errorlevel 1 (
    echo sqlcmd was not found. Install it with: winget install Microsoft.Sqlcmd
    exit /b 1
)

echo Server: %SERVER%
for /f "delims=" %%F in ('dir /b /on "%~dp0???-*.sql"') do (
    echo Running %%F
    rem -I turns on QUOTED_IDENTIFIER, as SSMS does (needed for filtered indexes, Lesson 10)
    sqlcmd -S "%SERVER%" -E -C -I -b -i "%~dp0%%F"
    if errorlevel 1 (
        echo FAILED: %%F
        exit /b 1
    )
)

echo Done. SchoolDb is up to date.
endlocal

Step 2: test every constraint

The best way to trust a rule is to break it on purpose. This script inserts test rows inside a transaction: a group of changes that can be undone together. ROLLBACK at the end undoes everything, so the tables stay empty.

It uses INSERT and DELETE, which you learn properly in Lesson 15 and Module 4. Here they only test the rules. DECLARE @ClassId INT creates a variable: a named box that holds one value while the script runs.

queries/010-constraint-tests.sql
-- Lesson 10: test every kind of constraint.
-- Everything runs inside a transaction that is rolled back at the end,
-- so the tables stay empty. Five statements fail on purpose: read each error.
USE SchoolDb;
GO

BEGIN TRANSACTION;

DECLARE @ClassId INT;

-- Valid rows to test against.
INSERT INTO dbo.Classes (Name, DisplayOrder) VALUES (N'Class 1', 1);
SET @ClassId = SCOPE_IDENTITY();  -- the Id SQL Server just created
INSERT INTO dbo.AcademicYears (Name, StartDate, EndDate, IsCurrent) VALUES ('2026-27', '2026-06-01', '2027-03-31', 1);
INSERT INTO dbo.Subjects (Code, Name) VALUES ('MATH', N'Mathematics');

-- 1. DEFAULT: Capacity is not given, so DF_Sections_Capacity fills in 40.
INSERT INTO dbo.Sections (ClassId, Name) VALUES (@ClassId, 'A');
SELECT Name, Capacity FROM dbo.Sections;

-- 2. UNIQUE: a second subject with the code MATH. Fails.
INSERT INTO dbo.Subjects (Code, Name) VALUES ('MATH', N'Maths');

-- 3. CHECK: a year that ends before it starts. Fails.
INSERT INTO dbo.AcademicYears (Name, StartDate, EndDate, IsCurrent) VALUES ('2027-28', '2028-03-31', '2027-06-01', 0);

-- 4. Filtered UNIQUE index: a second current year. Fails.
INSERT INTO dbo.AcademicYears (Name, StartDate, EndDate, IsCurrent) VALUES ('2027-28', '2027-06-01', '2028-03-31', 1);

-- 5. FOREIGN KEY: a section for a class that does not exist. Fails.
INSERT INTO dbo.Sections (ClassId, Name) VALUES (99999, 'B');

-- 6. FOREIGN KEY again: deleting a class that still has a section. Fails.
DELETE FROM dbo.Classes WHERE Id = @ClassId;

ROLLBACK TRANSACTION;  -- undo everything above

-- The tables are empty again.
SELECT COUNT(*) AS ClassesLeft FROM dbo.Classes;
GO

Example: reading constraint errors

Run it with F5. The first result shows section A with Capacity 40: the default worked. Then the Messages tab shows one error per broken rule:

Output
Msg 2627, Level 14, State 1, Line 17
Violation of UNIQUE KEY constraint 'UQ_Subjects_Code'. Cannot insert duplicate key in object 'dbo.Subjects'. The duplicate key value is (MATH).
Msg 547, Level 16, State 1, Line 20
The INSERT statement conflicted with the CHECK constraint "CK_AcademicYears_Dates". The conflict occurred in database "SchoolDb", table "dbo.AcademicYears".
Msg 2601, Level 14, State 1, Line 23
Cannot insert duplicate key row in object 'dbo.AcademicYears' with unique index 'UQ_AcademicYears_IsCurrent'. The duplicate key value is (1).
Msg 547, Level 16, State 1, Line 26
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Sections_Classes". The conflict occurred in database "SchoolDb", table "dbo.Classes", column 'Id'.
Msg 547, Level 16, State 1, Line 29
The DELETE statement conflicted with the REFERENCE constraint "FK_Sections_Classes". The conflict occurred in database "SchoolDb", table "dbo.Sections", column 'ClassId'.

Each message names the exact constraint. That is why you named them. The last result shows ClassesLeft = 0: the rollback undid the test rows.

Common mistakes

Leaving rules only in the application. Another app or a manual fix bypasses them. Fix: put rules that must never break in the database.
Unnamed constraints. Errors then show random names. Fix: always use CONSTRAINT Name.
Adding a constraint to a table that already has bad data. The ALTER fails. Fix: find and fix the bad rows first, then add the constraint.
Using UNIQUE where a filtered index is needed. UNIQUE (IsCurrent) blocks a second past year. Fix: use WHERE IsCurrent = 1.
Testing rules on real data. Test rows get left behind. Fix: test inside a transaction and roll back.

In real projects

From 16 years of .NET projects

On one project, the team skipped foreign keys "for speed". A year later, 4% of the order lines pointed to customers that no longer existed, and finance reports never matched. Cleaning that took longer than building the feature.

My rule now: anything that must always be true goes in a constraint, and anything that depends on context goes in code. "A mark belongs to a real enrollment" is a constraint. "Teachers can edit marks only until results are published" is code.

Best practices

Add a foreign key for every relationship in the ER diagram.
Protect natural keys, like codes and admission numbers, with UNIQUE.
Use CHECK for simple value rules and fixed lists of allowed values.
Use a filtered unique index for "only one row may have this value".
Test constraints by breaking them inside a rolled-back transaction.

Recap

Constraints are rules the database checks on every change.
Six kinds: NOT NULL, PRIMARY KEY, UNIQUE, CHECK, DEFAULT, FOREIGN KEY.
Foreign keys enforce referential integrity in both directions: insert and delete.
A filtered unique index allows "only one current year".
Named constraints produce readable errors.
run-all.cmd now uses -I, so QUOTED_IDENTIFIER is on, as in SSMS.

Homework

Library module task: Make ISBNs unique and check that PublishedYear is between 1450 and 2100. Then create BookCopies with a foreign key to Books, a unique (BookId, CopyNumber), and a Status that defaults to Available and allows only Available, Issued, or Lost.

A correct result looks like: the script runs twice without errors, and a test insert of a copy for a missing book fails with a foreign key error.

Show the solution

library-assignment/010-book-constraints.sql
-- Library homework, Lesson 10: constraints on Books, and the BookCopies table.
USE SchoolDb;
GO

IF OBJECT_ID(N'dbo.UQ_Books_Isbn', N'UQ') IS NULL
    ALTER TABLE dbo.Books
        ADD CONSTRAINT UQ_Books_Isbn UNIQUE (Isbn);

IF OBJECT_ID(N'dbo.CK_Books_PublishedYear', N'C') IS NULL
    ALTER TABLE dbo.Books
        ADD CONSTRAINT CK_Books_PublishedYear CHECK (PublishedYear BETWEEN 1450 AND 2100);
GO

IF OBJECT_ID(N'dbo.BookCopies', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.BookCopies
    (
        Id         INT IDENTITY(1, 1) NOT NULL,
        BookId     INT                NOT NULL,
        CopyNumber SMALLINT           NOT NULL,
        Status     VARCHAR(9)         NOT NULL CONSTRAINT DF_BookCopies_Status DEFAULT ('Available'),
        CONSTRAINT PK_BookCopies PRIMARY KEY (Id),
        CONSTRAINT FK_BookCopies_Books FOREIGN KEY (BookId) REFERENCES dbo.Books (Id),
        CONSTRAINT UQ_BookCopies_BookId_CopyNumber UNIQUE (BookId, CopyNumber),
        CONSTRAINT CK_BookCopies_Status CHECK (Status IN ('Available', 'Issued', 'Lost'))
    );
END;
GO

Notice the constraints written inside CREATE TABLE. For a new table, that is simpler than adding them afterwards. Lesson 11 uses this style for every new table.

Quiz

Which constraint stops two subjects from having the code MATH?
1. CHECK
2. DEFAULT
3. UNIQUE
4. FOREIGN KEY
Show answer
Answer: C. UNIQUE UNIQUE refuses a second row with the same value.
What does a foreign key from Sections to Classes prevent?
1. Two sections named A
2. A section for a class that does not exist, and deleting a class that has sections
3. Sections without a capacity
4. Classes without a name
Show answer
Answer: B. A section for a class that does not exist, and deleting a class that has sections Referential integrity works in both directions: insert and delete.
How do you allow only one academic year with IsCurrent = 1?
1. UNIQUE (IsCurrent)
2. CHECK (IsCurrent = 1)
3. A filtered unique index WHERE IsCurrent = 1
4. DEFAULT (1) FOR IsCurrent
Show answer
Answer: C. A filtered unique index WHERE IsCurrent = 1 The filter applies uniqueness only to current years; past years can all be 0.
An insert leaves out Capacity. What value does the section get?
1. NULL
2. 0
3. 40, from DF_Sections_Capacity
4. The insert fails
Show answer
Answer: C. 40, from DF_Sections_Capacity A DEFAULT constraint fills in a value when the insert does not give one.
Why does the test script end with ROLLBACK TRANSACTION?
1. To save the test rows
2. To undo the test rows so the tables stay empty
3. To delete the constraints
4. Because GO requires it
Show answer
Answer: B. To undo the test rows so the tables stay empty Rolling back undoes every change made since BEGIN TRANSACTION.

Interview questions

Questions are grouped by experience level. Click a question to see a model answer. All questions: SQL Server interview questions.

Fresher (0-2 yrs)

F What kinds of constraints does SQL Server support? concept

NOT NULL (a value is required), PRIMARY KEY (unique and not NULL), UNIQUE (no duplicates), CHECK (the value must pass a test), DEFAULT (a value when none is given), and FOREIGN KEY (the value must exist in another table). Together they keep data valid no matter which program writes it.

See the example · Permalink

F What is the difference between a PRIMARY KEY and a UNIQUE constraint? concept

Both prevent duplicates. A table has only one primary key, and it never allows NULL. A table can have many unique constraints, and each allows one NULL. The primary key is the row's identity; unique constraints protect other business values such as a subject code.

See the example · Permalink

F What does a foreign key do? concept

A foreign key makes a column's values refer to an existing primary key in another table. SQL Server then refuses a value that does not exist there, such as a section for class 99, and refuses to delete a row that is still referenced. This is called referential integrity.

See the example · Permalink

Mid (3-5 yrs)

M How do you allow only one row to have IsCurrent = 1? coding

Use a filtered unique index: CREATE UNIQUE INDEX ... ON dbo.AcademicYears (IsCurrent) WHERE IsCurrent = 1. Uniqueness then applies only to rows that match the filter, so many past years can have 0 but only one year can have 1. A plain UNIQUE (IsCurrent) would wrongly allow only one past year.

See the example · Permalink

M What happens if you delete a class that still has sections? concept

The delete fails with error 547: "The DELETE statement conflicted with the REFERENCE constraint FK_Sections_Classes". By default a foreign key blocks deleting a referenced row. You can choose ON DELETE CASCADE or SET NULL instead, but for school records blocking is safer.

See the example · Permalink

M Should a rule like 'an academic year ends after it starts' be a CHECK constraint or application code? concept

Both, for different reasons. The application checks it to give the user a friendly message. The CHECK constraint guarantees it for every path into the database, including imports and manual fixes. A simple, permanent rule about one row belongs in a CHECK.

See the example · Permalink

Senior (6-9 yrs)

S You need to add a foreign key to a table that already contains invalid rows. What do you do? scenario

First find and fix or archive the invalid rows, then add the constraint normally so SQL Server checks all existing data. WITH NOCHECK can add it without checking old rows, but the constraint is then marked untrusted, the optimizer cannot rely on it, and the bad data stays.

How to answer: Show a safe process: measure the bad rows with a query, agree with the business how to fix each one, clean in a transaction, then add the constraint. Explain why WITH NOCHECK is only a temporary step, and that untrusted constraints lose query-plan benefits.

See the example · Permalink

S A script that creates a filtered index works in SSMS but fails through sqlcmd. Why? scenario

Filtered indexes require QUOTED_IDENTIFIER ON. SSMS turns it on by default, but sqlcmd does not, so the CREATE INDEX fails with error 1934. Run sqlcmd with -I, or put SET QUOTED_IDENTIFIER ON in the script. Later inserts and updates on that table need the setting too.

How to answer: Explain the session setting and why tools differ. Add the hidden danger: stored procedures and triggers remember the setting they were created with, so creating them through sqlcmd without -I breaks them later. Close with the fix: standardize the setting in every deployment tool.

See the example · Permalink

Architect / Lead (10+ yrs)

A Which business rules belong in database constraints, and which belong in application code? design

Rules that must always be true, whatever the context, belong in constraints: uniqueness, required values, valid ranges, and relationships. Rules that depend on who is acting, when, or on several steps belong in code: permissions, workflows, and time windows. Many important rules live in both places.

How to answer: Give the test: "must this be true for every row, forever, from every source?" Use SMS examples on both sides (a mark belongs to an enrollment vs a teacher may edit marks only before results are published). Mention the cost of each: constraints are cheap and reliable but rigid; code is flexible but can be bypassed.

See the example · Permalink

Resources

Code for this lesson: git tag lesson-10-constraints (code repository coming soon)
Files: database/010-constraints.sql, database/run-all.cmd, queries/010-constraint-tests.sql, library-assignment/010-book-constraints.sql
Video: coming soon

What's next

The masters are protected. Next, you create the tables for the people in the school. Lesson 11: Teachers, students and guardians

Want live sessions, code reviews, and interview preparation? Join the next batch at Sahasra Technologies.

Introduction​

Why this matters​

The concept​

Example: the six kinds of constraints​

Example: UNIQUE vs PRIMARY KEY​

Example: CHECK​

Example: DEFAULT​

Example: FOREIGN KEY​

Example: what happens on delete​

Example: one current year with a filtered unique index​

Apply it to the SMS​

Step 1: add the constraints​

Example: a sqlcmd setting you must know​

Step 2: test every constraint​

Example: reading constraint errors​

Common mistakes​

In real projects​

Best practices​

Recap​

Homework​

Quiz​

Interview questions​

Fresher (0-2 yrs)​

Mid (3-5 yrs)​

Senior (6-9 yrs)​

Architect / Lead (10+ yrs)​

Resources​

What's next​

Introduction

Why this matters

The concept

Example: the six kinds of constraints

Example: UNIQUE vs PRIMARY KEY

Example: CHECK

Example: DEFAULT

Example: FOREIGN KEY

Example: what happens on delete

Example: one current year with a filtered unique index

Apply it to the SMS

Step 1: add the constraints

Example: a sqlcmd setting you must know

Step 2: test every constraint

Example: reading constraint errors

Common mistakes

In real projects

Best practices

Recap

Homework

Quiz

Interview questions

Fresher (0-2 yrs)

Mid (3-5 yrs)

Senior (6-9 yrs)

Architect / Lead (10+ yrs)

Resources

What's next