Constraints — Rules the Database Enforces

ReadingRoom's API checks that a loan has a due date. But an admin script, a data import, or a second service can write to the same database without going through your API. Constraints move the most important rules into the database, where nothing can skip them.

The rule

Validate in the app for kindness; constrain in the database for truth.

App validation gives users friendly error messages. Constraints guarantee that invalid data is never stored, no matter who writes it.

CREATE TABLE Members (
    Id     int IDENTITY PRIMARY KEY,
    Name   nvarchar(100) NOT NULL,
    Email  nvarchar(200) NOT NULL UNIQUE
);

CREATE TABLE Loans (
    Id         int IDENTITY PRIMARY KEY,
    BookId     int  NOT NULL REFERENCES Books(Id),
    MemberId   int  NOT NULL REFERENCES Members(Id),
    LoanedOn   date NOT NULL DEFAULT CAST(GETDATE() AS date),
    DueDate    date NOT NULL,
    ReturnedAt date NULL,
    CONSTRAINT CK_Loans_Due CHECK (DueDate > LoanedOn)
);

(IDENTITY and GETDATE() are SQL Server; PostgreSQL uses GENERATED ALWAYS AS IDENTITY and CURRENT_DATE.)

Constraint	Rejects…
`PRIMARY KEY`	a duplicate or missing id
`FOREIGN KEY` / `REFERENCES`	a loan pointing at a book that does not exist
`NOT NULL`	a missing value
`UNIQUE`	a second member with the same email
`CHECK`	a value breaking a rule, like a due date before the loan date
`DEFAULT`	nothing — it fills in a value when none is given

A foreign key also protects the other direction: by default you cannot delete a book that still has loans pointing at it. ON DELETE CASCADE would delete those loans instead — use it deliberately.

The shortcut

Turn each business rule into a sentence starting "there must never be…", and the constraint falls out.

From rules to constraintsStep 1 of 4

"Never two members with one email"

UNIQUE on Members.Email.

The trap: relying on a "check if the email exists, then insert" in C#. Two requests at the same moment both pass the check. Only a UNIQUE constraint stops the second insert.

Drills

Answer fast — trust the rule.

quick drill

Which constraint stops two members registering with the same email?

quick drill

An insert sets Loans.BookId = 999, but no book 999 exists. What stops it?

quick drill

Which constraint does NOT reject anything?

quick drill

Why is a C# "does this email exist?" check not enough on its own?

Remember

Validate in the app for kindness; constrain in the database for truth.
Phrase each rule as "there must never be…", then pick the constraint.
Race conditions beat app checks; UNIQUE and foreign keys do not lose.

Constraints — Rules the Database Enforces

Constraints — Rules the Database Enforces

The rule

The shortcut

"Never two members with one email"

Drills

Remember

Practice — level up