Constraints

Constraints

What it means

Constraints enforce rules on the data a table will accept, at the database level — regardless of which application or query inserts the data. The main ones: PRIMARY KEY (uniquely identifies each row, implicitly unique + not null), FOREIGN KEY (a column must reference a valid row in another table), UNIQUE (no duplicate values), CHECK (a custom boolean condition every row must satisfy), and NOT NULL.

Examples

CREATE TABLE Customers (
    Id INT PRIMARY KEY IDENTITY(1,1),
    Email NVARCHAR(255) NOT NULL UNIQUE,
    Age INT CHECK (Age >= 0)
);

CREATE TABLE Orders (
    Id INT PRIMARY KEY IDENTITY(1,1),
    CustomerId INT NOT NULL,
    Amount DECIMAL(10,2) CHECK (Amount > 0),
    CONSTRAINT FK_Orders_Customers FOREIGN KEY (CustomerId)
        REFERENCES Customers(Id)
);

-- Attempting to violate a constraint fails loudly, protecting data integrity
INSERT INTO Orders (CustomerId, Amount) VALUES (999, 50.00);
-- Error: The INSERT statement conflicted with the FOREIGN KEY constraint
-- (assuming CustomerId 999 doesn't exist in Customers)

Common mistake

Relying only on application-level validation (checking in code) instead of database constraints — if any other process, script, or direct database access bypasses that application layer, invalid data slips through unnoticed.

-- Application code checks "Amount must be positive" before inserting...
-- but a raw script or a different app connecting to the same database has no such check

-- A CHECK constraint enforces the rule no matter what inserts the data
ALTER TABLE Orders ADD CONSTRAINT CK_Orders_Amount CHECK (Amount > 0);

Quick practice

1. What does a FOREIGN KEY constraint enforce?

   Answer

   That a column's value must match an existing value in the referenced table's key column — preventing "orphan" rows that point to nonexistent records.

2. What’s the difference between PRIMARY KEY and UNIQUE?

   Answer

   Both prevent duplicate values, but a table can have only one PRIMARY KEY (which is also implicitly NOT NULL) and multiple UNIQUE constraints, which do allow NULL values (typically one NULL, depending on configuration).

3. Why are database-level constraints more reliable than only validating in application code?

   Answer

   Constraints are enforced by the database itself for every insert/update, regardless of which application, script, or tool is writing the data — application-level checks only apply if that specific code path is used.