Published / updated29 Aug 2026

Keys, Constraints and Relationships

Before you start

You need: tables and data types (Article 02).

Time: about 50 minutes, plus the practice.

Learning objective

Design a schema whose constraints make invalid data impossible to store, and diagnose a constraint violation from its error message.

Topics

Primary keys and clustered indexes
Foreign keys and referential integrity
UNIQUE — and why it is usually composite
CHECK constraints
DEFAULT constraints
Cascade options
One-to-many and many-to-many relationships
Reading a constraint error

Why constraints matter

Application code validates. Constraints guarantee.

An application checks a roll number is unique before inserting. Two clerks submit at the same moment, both checks pass, both insert, and the table now holds a duplicate. No amount of application code closes that window — only the database can, because only the database sees both writes.

Constraints also survive things application code does not: a data import script, a support engineer running an UPDATE in SSMS, a second application hitting the same database, and a bug in a future version of your own code.

Rule: every rule that must always be true belongs in the database, whether or not the application also checks it.

Primary keys

CREATE TABLE dbo.Student
(
    Id       INT IDENTITY(1,1) NOT NULL,
    PublicId UNIQUEIDENTIFIER  NOT NULL DEFAULT NEWID(),
    SchoolId INT               NOT NULL,
    Name     NVARCHAR(100)     NOT NULL,

    CONSTRAINT PK_Student PRIMARY KEY CLUSTERED (Id)
);

A primary key uniquely identifies a row. It implies NOT NULL and UNIQUE, and by default creates a clustered index, which physically orders the table by that column.

Name your constraints. CONSTRAINT PK_Student produces readable errors and lets you drop or alter it later; an unnamed constraint gets a generated name like PK__Student__3214EC0704E4BC85 that differs on every database, so scripts referencing it are not portable.

Surrogate versus natural keys

Id         INT IDENTITY(1,1)   -- surrogate: meaningless, stable
RollNumber NVARCHAR(20)        -- natural: meaningful, changeable

A natural key is real data — a roll number, an email. A surrogate key is an invented number with no meaning.

Prefer surrogate keys as the primary key. Natural keys change: a school renumbers its roll format, a student's email is corrected, a code is reassigned. Every foreign key pointing at it must then change too, across every table.

Keep the natural key, and enforce it with a UNIQUE constraint instead.

Clustered index choice

A table has one clustered index, which determines physical row order. A narrow, ever-increasing key — INT IDENTITY — is ideal, because new rows append to the end.

-- Bad: random GUIDs cause every insert to land mid-table and fragment the index
CONSTRAINT PK_Student PRIMARY KEY CLUSTERED (PublicId)

-- Good
CONSTRAINT PK_Student PRIMARY KEY CLUSTERED (Id),
CONSTRAINT UQ_Student_PublicId UNIQUE NONCLUSTERED (PublicId)

Foreign keys

A foreign key says a column's value must exist in another table.

CREATE TABLE dbo.ExamResult
(
    Id            INT IDENTITY(1,1) NOT NULL,
    SchoolId      INT               NOT NULL,
    StudentId     INT               NOT NULL,
    ExamId        INT               NOT NULL,
    MarksObtained DECIMAL(5,2)      NULL,
    IsAbsent      BIT               NOT NULL DEFAULT 0,

    CONSTRAINT PK_ExamResult PRIMARY KEY CLUSTERED (Id),

    CONSTRAINT FK_ExamResult_Student
        FOREIGN KEY (StudentId) REFERENCES dbo.Student (Id),

    CONSTRAINT FK_ExamResult_Exam
        FOREIGN KEY (ExamId) REFERENCES dbo.Exam (Id)
);

This makes two things impossible: inserting a result for a student who does not exist, and deleting a student who still has results. Both would leave orphan rows — data pointing at nothing, which no report can interpret and no application can display.

The referenced column must be a primary key or have a unique constraint.

Cascade options

CONSTRAINT FK_ExamResult_Student
    FOREIGN KEY (StudentId) REFERENCES dbo.Student (Id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION

Option	On delete of the parent
`NO ACTION`	Reject the delete (the default)
`CASCADE`	Delete the child rows too
`SET NULL`	Set the child column to `NULL` — the column must be nullable
`SET DEFAULT`	Set it to the column's default

Default to NO ACTION. ON DELETE CASCADE on ExamResult means deleting one student silently destroys their entire academic history. On FeePayment it destroys financial records the school is legally required to keep.

The rejection is a feature. It forces the application to decide — usually by marking the student inactive rather than deleting them.

CASCADE is reasonable only where the child row has no independent meaning: a StudentDocument row that exists purely as an attachment, for example.

Cascade paths also cannot form cycles. SQL Server rejects a schema where two cascade paths reach the same table, with an error about "may cause cycles or multiple cascade paths" — which is usually a signal that the design has a loop in it.

UNIQUE constraints

CONSTRAINT UQ_Student_PublicId UNIQUE (PublicId),
CONSTRAINT UQ_Student_Roll UNIQUE (SchoolId, RollNumber)

The second is the important one. RollNumber is not globally unique — it is unique within a school. A constraint on RollNumber alone means the second school to onboard cannot use NCA-2024-0001 because the first already has it.

Every uniqueness rule in a multi-tenant system is scoped to the tenant. This is invisible with one school of test data and blocks the product entirely with two.

The same applies elsewhere:

CONSTRAINT UQ_Teacher_Employee UNIQUE (SchoolId, EmployeeCode),
CONSTRAINT UQ_Subject_Code     UNIQUE (SchoolId, ClassName, Code),
CONSTRAINT UQ_ExamResult       UNIQUE (ExamId, StudentId),
CONSTRAINT UQ_Attendance       UNIQUE (StudentId, SubjectId, [Date])

UQ_ExamResult prevents recording two results for the same student in the same exam — a duplicate that otherwise silently doubles a total and halves an average.

UNIQUE and NULL

A UNIQUE constraint permits one NULL in SQL Server. Two NULLs violate it, even though NULL <> NULL. This surprises people coming from other databases, where any number of NULLs is allowed.

For "unique when present, any number of blanks", use a filtered index instead:

CREATE UNIQUE INDEX UX_Student_Email
ON dbo.Student (SchoolId, Email)
WHERE Email IS NOT NULL;

CHECK constraints

A CHECK enforces a rule about values in a row.

CONSTRAINT CK_Student_Status
    CHECK (Status IN (0, 1, 2, 3)),

CONSTRAINT CK_Student_Section
    CHECK (Section IN ('A', 'B', 'C')),

CONSTRAINT CK_ExamResult_Marks
    CHECK (MarksObtained IS NULL OR MarksObtained >= 0),

CONSTRAINT CK_ExamResult_Absent
    CHECK ((IsAbsent = 1 AND MarksObtained IS NULL)
        OR (IsAbsent = 0 AND MarksObtained IS NOT NULL)),

CONSTRAINT CK_FeeAccount_Amounts
    CHECK (TotalFees >= 0 AND PaidAmount >= 0 AND DiscountAmount >= 0),

CONSTRAINT CK_FeeAccount_Paid
    CHECK (PaidAmount + DiscountAmount <= TotalFees)

CK_ExamResult_Absent is worth studying. It encodes a real rule: an absent student has no marks, and a present student must have marks. Without it, an absent student with MarksObtained = 0 is indistinguishable from one who genuinely scored zero — and appears as a fail on their result sheet.

CK_FeeAccount_Paid makes over-payment impossible at the storage level, not just in the application that happens to be writing today.

CHECK and NULL

A CHECK passes when the expression is TRUE or UNKNOWN. Only an explicit FALSE rejects the row.

CONSTRAINT CK_Student_Phone CHECK (LEN(ParentPhone) = 10)

If ParentPhone were nullable, LEN(NULL) = 10 is UNKNOWN, so a NULL passes. That is often the intended behaviour, but it must be intended — not discovered later.

A CHECK can reference only columns in the same row. It cannot query another table; that needs a foreign key or a trigger.

DEFAULT constraints

PublicId    UNIQUEIDENTIFIER NOT NULL
    CONSTRAINT DF_Student_PublicId DEFAULT NEWID(),

Status      TINYINT NOT NULL
    CONSTRAINT DF_Student_Status DEFAULT 0,

CreatedAt   DATETIME2(3) NOT NULL
    CONSTRAINT DF_Student_CreatedAt DEFAULT SYSUTCDATETIME()

A default supplies a value when the insert omits the column. It does not fire when the insert explicitly supplies NULL — that is an error on a NOT NULL column, not a fallback to the default.

Use SYSUTCDATETIME() rather than GETDATE() for audit timestamps. GETDATE() returns server local time, so a server timezone change or a move to a different region makes historical timestamps incomparable.

Relationships

One-to-many

The most common. One school has many students; each student belongs to one school.

-- The FOREIGN KEY goes on the MANY side
CREATE TABLE dbo.Student
(
    Id       INT IDENTITY(1,1) NOT NULL,
    SchoolId INT               NOT NULL,

    CONSTRAINT PK_Student PRIMARY KEY (Id),
    CONSTRAINT FK_Student_School FOREIGN KEY (SchoolId) REFERENCES dbo.School (Id)
);

One-to-one

Rare. Implemented as a one-to-many with a UNIQUE constraint on the foreign key:

CONSTRAINT FK_Student_User FOREIGN KEY (UserId) REFERENCES dbo.[User] (Id),
CONSTRAINT UQ_Student_User UNIQUE (UserId)

Without the UNIQUE, two students could share one login.

Many-to-many

Needs a third table. A teacher teaches many subjects; a subject may be taught by many teachers.

CREATE TABLE dbo.TeacherSubject
(
    TeacherId INT NOT NULL,
    SubjectId INT NOT NULL,

    CONSTRAINT PK_TeacherSubject PRIMARY KEY (TeacherId, SubjectId),
    CONSTRAINT FK_TeacherSubject_Teacher FOREIGN KEY (TeacherId) REFERENCES dbo.Teacher (Id),
    CONSTRAINT FK_TeacherSubject_Subject FOREIGN KEY (SubjectId) REFERENCES dbo.Subject (Id)
);

The composite primary key does double duty: it identifies the row and prevents the same pairing being recorded twice.

Reading a constraint error

Error	Meaning	Usual cause
2627	Violation of PRIMARY KEY / UNIQUE constraint	Duplicate — a roll number that already exists
2601	Cannot insert duplicate key row in unique index	Same, via a unique index
547	INSERT/UPDATE conflicted with FOREIGN KEY	The parent row does not exist
547	DELETE conflicted with REFERENCE constraint	Child rows still point at this row
547	INSERT conflicted with CHECK constraint	A value broke a rule
515	Cannot insert NULL into column	A `NOT NULL` column was omitted with no default

The message names the constraint, which is why naming them matters:

Violation of UNIQUE KEY constraint 'UQ_Student_Roll'.
Cannot insert duplicate key in object 'dbo.Student'.
The duplicate key value is (1, NCA-2024-0012).

That tells you the school id and the roll number involved. An auto-generated constraint name tells you almost nothing.

Error 547 covers three different situations — foreign key on insert, foreign key on delete, and check violation. Read the rest of the message to tell them apart.

Errors you will hit

Message	Cause	Fix
`Violation of PRIMARY KEY constraint`	Duplicate key inserted	The row already exists
`Violation of UNIQUE KEY constraint`	Duplicate in a unique column	Check whether the constraint should be composite
`The INSERT statement conflicted with the FOREIGN KEY constraint`	Parent row does not exist	Insert the parent first
`The DELETE statement conflicted with the REFERENCE constraint`	Child rows still reference it	Delete children, or use a soft delete
`The INSERT statement conflicted with the CHECK constraint`	Value outside the allowed range	The constraint is doing its job
A second school cannot use a roll number the first has	`UNIQUE (RollNumber)` instead of `UNIQUE (SchoolId, RollNumber)`	Make it composite

The last row is the multi-tenant trap. Two schools may legitimately both issue NCA-2024-0012, and a single-column unique constraint makes that impossible.

Common mistakes

No constraints at all, trusting application validation
UNIQUE (RollNumber) instead of UNIQUE (SchoolId, RollNumber)
ON DELETE CASCADE on rows with independent meaning, destroying history
Unnamed constraints, producing unreadable errors and unportable scripts
A GUID as the clustered primary key
A natural key as the primary key, so a rename cascades everywhere
Assuming UNIQUE allows many NULLs — SQL Server allows one
A CHECK that passes on NULL unintentionally
GETDATE() for audit timestamps instead of SYSUTCDATETIME()
A many-to-many without the junction table's composite key
Adding a NOT NULL column to a populated table with no default

Practice

Build the full School Management System schema — School, User, Student, Teacher, Staff, Subject, Exam, ExamResult, FeeAccount, FeePayment, Attendance, AuditLog — with every constraint named. Include:

INT IDENTITY clustered primary key on each table, plus a unique PublicId
Foreign keys on every relationship, all NO ACTION
Composite UNIQUE on (SchoolId, RollNumber) and (SchoolId, EmployeeCode)
UNIQUE (ExamId, StudentId) on ExamResult
The absent/marks CHECK on ExamResult
The over-payment CHECK on FeeAccount

Then run the course debugging exercise — fix a constraint violation. Trigger each of these deliberately and record the exact error number and message:

Insert a duplicate roll number for the same school.
Insert the same roll number for a different school. Confirm it succeeds — if it does not, your unique constraint is not composite.
Insert an ExamResult for a StudentId that does not exist.
Delete a student who has results.
Insert an ExamResult with IsAbsent = 1 and MarksObtained = 0.
Insert a FeeAccount where PaidAmount exceeds TotalFees.
Insert a student omitting ParentPhone.
Insert two rows with NULL in a UNIQUE column.

Exercise 2 is the multi-tenant check. Exercise 5 is the one that keeps absent students off a fail list.

You can now

Choose a primary key and say why a surrogate is usual
Write foreign keys that stop orphaned rows
Make uniqueness composite where the system is multi-tenant
Use CHECK constraints so invalid data cannot be stored
Read a constraint-violation error and say which rule fired

Review questions

Why must RollNumber be unique per school rather than globally?
Why is ON DELETE CASCADE the wrong choice for FeePayment?
Why prefer a surrogate primary key over a natural one?
What does error 2627 mean, and what does 547 mean?

Next: CRUD and filtering

Before you start​

Learning objective​

Topics​

Why constraints matter​

Primary keys​

Surrogate versus natural keys​

Clustered index choice​

Foreign keys​

Cascade options​

UNIQUE constraints​

UNIQUE and NULL​

CHECK constraints​

CHECK and NULL​

DEFAULT constraints​

Relationships​

One-to-many​

One-to-one​

Many-to-many​

Reading a constraint error​

Errors you will hit​

Common mistakes​

Practice​

You can now​

Review questions​