Constraints: UNIQUE, CHECK, FOREIGN KEY

PRIMARY KEY, NOT NULL and DEFAULT handle the basics. Real databases need three more rules: "no two players can wear the same jersey number" (UNIQUE), "runs can never be negative" (CHECK) and "a player's team must be a team that actually exists" (FOREIGN KEY).

Definitions

UNIQUE ensures that no two rows have the same value in a column (or combination of columns). Unlike a primary key, a table can have many UNIQUE constraints, and the column may contain NULL.

CHECK ensures that every value in a column satisfies a condition (a true/false expression), such as runs >= 0. A row whose value makes the condition FALSE is rejected.

FOREIGN KEY is a column in one table that refers to the primary key (or a unique key) of another table. It guarantees that every value you store already exists in the referenced table, keeping the two tables consistent (this is called referential integrity).

Summary table

Syntax

CREATE TABLE table_name (
    col1 data_type UNIQUE,                         -- column-level UNIQUE
    col2 data_type CHECK (condition),              -- column-level CHECK
    col3 data_type,
    CONSTRAINT uq_name UNIQUE (col1),              -- table-level, named
    CONSTRAINT ck_name CHECK (col2 >= 0),
    CONSTRAINT fk_name FOREIGN KEY (col3)
        REFERENCES parent_table (parent_column)
);

PRIMARY KEY vs UNIQUE

Example — CHECK and UNIQUE

CREATE TABLE squad (
    id        INT         NOT NULL,
    name      VARCHAR(50) NOT NULL,
    jersey_no INT,
    runs      INT DEFAULT 0,
    CONSTRAINT pk_squad PRIMARY KEY (id),
    CONSTRAINT uq_squad_jersey UNIQUE (jersey_no),
    CONSTRAINT ck_squad_runs CHECK (runs >= 0)
);

INSERT INTO squad (id, name, jersey_no, runs)
VALUES (1, 'Virat Kohli', 18, 750),
       (2, 'Faf du Plessis', 13, 450);

SELECT * FROM squad;

Now try to break the rules:

Example — FOREIGN KEY: linking players to teams

The practice database has a teams table whose primary key is team (RCB, MI, CSK, RR, DC, KKR, GT). A new contracts table can refer to it:

CREATE TABLE contracts (
    contract_id INT         NOT NULL,
    player_name VARCHAR(50) NOT NULL,
    team        VARCHAR(10) NOT NULL,
    price_cr    DECIMAL(5,2),
    CONSTRAINT pk_contracts PRIMARY KEY (contract_id),
    CONSTRAINT fk_contracts_team FOREIGN KEY (team) REFERENCES teams (team)
);

INSERT INTO contracts VALUES (1, 'Shubman Gill', 'GT', 16.50);   -- ✅ GT exists in teams
INSERT INTO contracts VALUES (2, 'Unknown Star', 'XYZ', 1.00);   -- ❌ XYZ does not exist

The second insert fails with: The INSERT statement conflicted with the FOREIGN KEY constraint "fk_contracts_team".

Words to know:

• Parent table (referenced) — teams. Child table (referencing) — contracts.
• The foreign key also protects the parent: you cannot delete team GT from teams (or DROP the teams table) while contracts still point to it.
• Options like ON DELETE CASCADE tell SQL to delete the child rows automatically when the parent row is deleted.

Note (playground): SQLite only enforces foreign keys when they are switched on. The FOREIGN KEY example below switches them on for you behind the scenes, so it behaves like SQL Server.

Common mistakes

• ❌ Thinking UNIQUE = PRIMARY KEY. ✅ UNIQUE allows NULL and you can have many of them.
• ❌ A foreign key pointing to a column that is not a primary/unique key. ✅ REFERENCES must target a PRIMARY KEY or UNIQUE column.
• ❌ Different data types on the two sides (INT vs VARCHAR). ✅ Make the foreign key column the same type as the parent column.
• ❌ Inserting child rows before parent rows. ✅ Insert the team first, then the players/contracts.
• ❌ CHECK (runs > 0) when 0 is valid. ✅ Think about edge cases: CHECK (runs >= 0).

Interview tip

Q: What is referential integrity? A: It is the guarantee that a relationship between two tables stays valid — every foreign key value in the child table matches an existing primary key value in the parent table. The FOREIGN KEY constraint enforces it by rejecting inserts/updates with non-existent parents and blocking deletes of parents that still have children.