Constraints: Protecting Your Data

Why constraints matter

The five constraint types

Five constraint types — know when each applies

Constraint	What It Enforces	Key Behaviour	Common Use
PRIMARY KEY	Row uniqueness + NOT NULL	One per table. Creates a clustered index by default.	Unique identifier for each row (CustomerId, OrderId)
FOREIGN KEY	Referential integrity between tables	Values must exist in the referenced table. Can CASCADE or RESTRICT on delete/update.	Link Orders to Customers, Items to Categories
UNIQUE	No duplicate values (allows one NULL)	Multiple per table. Creates a nonclustered index.	Email addresses, tax IDs, usernames
CHECK	Values must satisfy a Boolean expression	Can reference multiple columns in the same row. Evaluated on INSERT and UPDATE.	Age >= 0, Status IN ('Active','Inactive'), EndDate > StartDate
DEFAULT	Provides a value when none is specified	Does NOT prevent explicit NULLs (use NOT NULL for that). Applied on INSERT only.	CreatedDate = GETUTCDATE(), Status = 'Pending', Country = 'NZ'

PRIMARY KEY: one identity per row

Every table should have a primary key. It guarantees that each row is uniquely identifiable.

-- Single-column primary key
CREATE TABLE Customers (
    CustomerId INT NOT NULL,
    Email NVARCHAR(320) NOT NULL,
    FullName NVARCHAR(200) NOT NULL,
    CONSTRAINT PK_Customers PRIMARY KEY (CustomerId)
);

-- Composite primary key (two columns together must be unique)
CREATE TABLE OrderItems (
    OrderId INT NOT NULL,
    LineNumber INT NOT NULL,
    ProductId INT NOT NULL,
    Quantity INT NOT NULL,
    CONSTRAINT PK_OrderItems PRIMARY KEY (OrderId, LineNumber)
);

A primary key automatically creates a clustered index (unless you specify NONCLUSTERED). This means the table data is physically stored in primary key order.

💡 Exam tip: Natural key vs surrogate key

A natural key uses a real-world value (email, SSN, ISBN). A surrogate key uses a system-generated value (INT IDENTITY, SEQUENCE, GUID).

The exam tends to favour surrogate keys for primary keys because:

• They are stable (email addresses change; IDs do not)
• They are narrow (4-byte INT vs 320-byte email = smaller indexes)
• They avoid business logic leaking into the physical schema

But natural keys work well for lookup/reference tables (CountryCode CHAR(2), CurrencyCode CHAR(3)).

FOREIGN KEY: linking tables together

A foreign key ensures that a value in one table exists in another table. It prevents orphaned records — like an order that references a customer who does not exist.

CREATE TABLE Orders (
    OrderId INT NOT NULL IDENTITY(1,1),
    CustomerId INT NOT NULL,
    OrderDate DATE NOT NULL DEFAULT GETUTCDATE(),
    CONSTRAINT PK_Orders PRIMARY KEY (OrderId),
    CONSTRAINT FK_Orders_Customers
        FOREIGN KEY (CustomerId) REFERENCES Customers(CustomerId)
        ON DELETE RESTRICT    -- Cannot delete a customer who has orders
        ON UPDATE CASCADE     -- If CustomerId changes, update all orders
);

Cascading actions

Action	ON DELETE	ON UPDATE
RESTRICT / NO ACTION	Block the delete if child rows exist	Block the update if child rows reference the old value
CASCADE	Delete all child rows when the parent is deleted	Update all child rows when the parent key changes
SET NULL	Set the FK column to NULL in child rows	Set the FK column to NULL in child rows
SET DEFAULT	Set the FK column to its DEFAULT value	Set the FK column to its DEFAULT value

💡 Scenario: Ingrid's cascading dilemma at Nordic Shield

Ingrid Andersen at Nordic Shield Insurance is designing the claims database. When a policy is cancelled, what should happen to its claims?

• CASCADE DELETE? No — insurance regulations require claims history to be retained even after policy cancellation.
• SET NULL? Possibly — the claim still exists but the PolicyId becomes NULL. But then you lose the link to which policy the claim was under.
• RESTRICT? Yes — the safest choice. Block policy deletion if it has claims. If a policy needs to be cancelled, use a Status = 'Cancelled' flag instead of deleting the row.

The exam loves these scenarios. The answer depends on the business requirement, not just the technical capability.

UNIQUE: no duplicates allowed

UNIQUE constraints ensure no two rows have the same value in the specified column(s). Unlike PRIMARY KEY, you can have multiple UNIQUE constraints per table, and UNIQUE allows one NULL (since NULL is “unknown,” not a value).

ALTER TABLE Customers
ADD CONSTRAINT UQ_Customers_Email UNIQUE (Email);

Composite UNIQUE constraints

You can make a combination of columns unique:

-- Each product can only appear once per order
ALTER TABLE OrderItems
ADD CONSTRAINT UQ_OrderItems_Product
    UNIQUE (OrderId, ProductId);

CHECK: validate your values

CHECK constraints enforce a Boolean condition on column values. If the condition evaluates to FALSE, the INSERT or UPDATE is rejected.

ALTER TABLE Products
ADD CONSTRAINT CK_Products_Price CHECK (Price >= 0);

-- Multi-column CHECK
ALTER TABLE Events
ADD CONSTRAINT CK_Events_Dates CHECK (EndDate > StartDate);

-- CHECK with a list of allowed values
ALTER TABLE Policies
ADD CONSTRAINT CK_Policies_Status
    CHECK (Status IN ('Draft', 'Active', 'Expired', 'Cancelled'));

💡 Exam tip: CHECK vs application validation

The exam may ask whether validation should happen in the CHECK constraint or in the application.

Use CHECK when: the rule is simple, universal, and must be enforced regardless of how data enters (API, import, direct SQL). Price must be non-negative? CHECK.

Use application logic when: the rule requires external data (check against another service), complex business logic (discount rules based on customer tier), or user-friendly error messages. CHECK constraints return cryptic engine-level errors.

Best practice: use BOTH. CHECK as the safety net, application logic for user experience.

DEFAULT: sensible starting values

DEFAULT constraints supply a value when the INSERT statement does not specify one. They do NOT prevent NULL — you need NOT NULL for that.

CREATE TABLE AuditLog (
    LogId INT NOT NULL IDENTITY(1,1),
    EventType NVARCHAR(50) NOT NULL,
    CreatedAt DATETIME2 NOT NULL DEFAULT GETUTCDATE(),
    CreatedBy NVARCHAR(128) NOT NULL DEFAULT SYSTEM_USER,
    IsProcessed BIT NOT NULL DEFAULT 0,
    CONSTRAINT PK_AuditLog PRIMARY KEY (LogId)
);

Common DEFAULT patterns:

• GETUTCDATE() for timestamps (use UTC, not local time)
• SYSTEM_USER or SUSER_SNAME() for audit trails
• NEWSEQUENTIALID() for GUID primary keys (sequential for index performance)
• 0 or 1 for BIT flags
• 'Pending' or 'Draft' for status columns

Question

What is the difference between PRIMARY KEY and UNIQUE?

Click or press Enter to reveal answer

Answer

PRIMARY KEY enforces uniqueness AND NOT NULL. One per table. Creates a clustered index by default. UNIQUE enforces uniqueness but allows one NULL value. You can have multiple UNIQUE constraints per table. Creates a nonclustered index.

Click to flip back

Question

What does ON DELETE CASCADE do on a foreign key?

Click or press Enter to reveal answer

Answer

When a parent row is deleted, all child rows that reference it are automatically deleted too. Use with caution — cascading deletes can remove large amounts of data. In compliance-heavy environments, RESTRICT (block the delete) is often safer.

Click to flip back

Question

Can a CHECK constraint reference columns from another table?

Click or press Enter to reveal answer

Answer

No. CHECK constraints can only reference columns within the same row of the same table. If you need cross-table validation, use a trigger or stored procedure. The exam tests this limitation.

Click to flip back

Knowledge Check

Dev at PixelForge Studios is designing a Projects table. Each project must have a unique name (no duplicates), a start date, and an optional end date. When an end date is provided, it must be after the start date. Which combination of constraints should Dev use?

APRIMARY KEY on ProjectName, CHECK (EndDate > StartDate)BUNIQUE on ProjectName, CHECK (EndDate > StartDate OR EndDate IS NULL)CUNIQUE on ProjectName, CHECK (EndDate > StartDate)DPRIMARY KEY on ProjectName, DEFAULT for EndDate

Check Answer

Knowledge Check

Ingrid at Nordic Shield Insurance needs to ensure that when a PolicyHolder record is deleted, all related Addresses are also removed, but Claims must be preserved (the delete should be blocked if claims exist). How should she configure the foreign keys?

ABoth FK_Addresses and FK_Claims with ON DELETE CASCADEBFK_Addresses with ON DELETE CASCADE, FK_Claims with ON DELETE SET NULLCFK_Addresses with ON DELETE CASCADE, FK_Claims with ON DELETE RESTRICTDFK_Addresses with ON DELETE RESTRICT, FK_Claims with ON DELETE RESTRICT

Check Answer

🎬 Video coming soon

Next up: Specialised Tables and Graph Queries — temporal, in-memory, external, ledger, and graph tables, plus partitioning strategies.