SQL Constraints

Constraints are rules attached to columns or tables that the database enforces automatically, rejecting any INSERT or UPDATE that would violate them. Constraints move data-validation logic out of individual applications and into the database itself, so the rule can never be bypassed no matter which application or script writes the data.

Why it matters

Application code has bugs, and data arrives from imports, scripts and other services that skip your checks entirely. Constraints are rules the database itself enforces on every write, whoever makes it. They are the last and most reliable line of defence for the correctness of your data.

Column-Level Constraints

These constraints apply to a single column's values.

• NOT NULL — the column must always have a value; NULL is rejected.
• UNIQUE — no two rows may share the same value in this column (NULLs are typically exempt).
• CHECK (condition) — every value must satisfy a boolean expression, e.g. CHECK (price >= 0).
• DEFAULT value — supplies a value automatically when none is provided in an INSERT.

Table-Level Constraints

PRIMARY KEY and FOREIGN KEY can also be declared at the table level, which is required when they span multiple columns (a composite key) or when adding the constraint to an existing table after it was created. A FOREIGN KEY constraint can specify behavior on deletion of the referenced row, such as ON DELETE CASCADE (delete dependent rows automatically) or ON DELETE RESTRICT (block the deletion while dependents exist).

Why Constraints Matter

Without constraints, invalid data — a negative price, a duplicate username, an order pointing to a nonexistent customer — can silently enter the database through any bug in any application, script, or manual data fix. Constraints act as a last line of defense that holds regardless of which code path writes the data, catching mistakes at the moment they happen rather than as a corrupted row discovered weeks later.

Example

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    balance DECIMAL(10, 2) NOT NULL DEFAULT 0.00 CHECK (balance >= 0),
    referred_by INTEGER REFERENCES accounts(id) ON DELETE SET NULL
);

INSERT INTO accounts (id, username) VALUES (1, 'asha_r');

INSERT INTO accounts (id, username, balance) VALUES (2, 'asha_r', 50.00);
-- fails: username must be UNIQUE

First INSERT succeeds (balance defaults to 0.00). Second INSERT is rejected: duplicate value violates unique constraint on username.

Exercise

Create an accounts table in which email is required and unique, balance can never be negative, and status defaults to active. The three inserts below should then behave as follows: the first succeeds, the second is rejected as a duplicate email, and the third is rejected for its negative balance.

Expected output: email | balance | status then asha@example.com | 100 | active

-- TODO: accounts (id, email, balance, status) with the constraints described

INSERT INTO accounts (id, email, balance) VALUES (1, 'asha@example.com', 100);
INSERT INTO accounts (id, email, balance) VALUES (2, 'asha@example.com', 50);
INSERT INTO accounts (id, email, balance) VALUES (3, 'ravi@example.com', -20);

SELECT email, balance, status FROM accounts;

CREATE TABLE accounts (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  balance INTEGER NOT NULL CHECK (balance >= 0),
  status TEXT NOT NULL DEFAULT 'active'
);

INSERT INTO accounts (id, email, balance) VALUES (1, 'asha@example.com', 100);
INSERT INTO accounts (id, email, balance) VALUES (2, 'asha@example.com', 50);
INSERT INTO accounts (id, email, balance) VALUES (3, 'ravi@example.com', -20);

SELECT email, balance, status FROM accounts;

Quick quiz

Interview questions