Constraints: PRIMARY KEY, NOT NULL, DEFAULT

What stops someone from adding two players with the same id, or a player with no name? Constraints. They are rules you attach to columns so the database itself refuses bad data — no matter which app or person tries to insert it.

Definition

A constraint is a rule defined on a column or table that the database automatically enforces on every INSERT and UPDATE. If a change would break the rule, the database rejects the whole statement with an error.

• Automatically enforces — you don't have to remember to check; the database checks for you, every time.
• Rejects the statement — the bad row is never saved. The table stays clean.

This topic covers the three constraints from the course slides. The next topic covers UNIQUE, CHECK and FOREIGN KEY.

PRIMARY KEY

A PRIMARY KEY is a column (or set of columns) whose value uniquely identifies each row in a table. It can never be duplicated and can never be NULL. A table can have only one primary key.

Think of it as a player's unique jersey ID in the database. Two players may share the name "Rohit", but never the same id.

Two ways to write it:

-- Column level (quick)
id INT PRIMARY KEY

-- Table level, with your own name (recommended)
CONSTRAINT pk_players PRIMARY KEY (id)

A composite primary key uses more than one column, e.g. in a scorecard table one player can bat in many matches, but only once per match: PRIMARY KEY (match_id, player_id).

NOT NULL

NOT NULL means the column must always contain a value. Inserting a row without it, or setting it to NULL, is rejected.

NULL means "unknown / missing" — it is not zero and not an empty string. Columns without NOT NULL allow NULL by default.

DEFAULT

DEFAULT gives a column an automatic value that SQL uses when an INSERT does not provide a value for that column.

A new player starts the season at 0 runs — perfect use of DEFAULT 0.

Note: The default is used only when the column is left out of the INSERT. If you explicitly insert NULL, the column gets NULL (unless it is also NOT NULL).

Example — all three constraints together

CREATE TABLE players_new (
    id      INT         NOT NULL,
    name    VARCHAR(50) NOT NULL,
    team    VARCHAR(10),
    runs    INT         DEFAULT 0,
    CONSTRAINT pk_players_new PRIMARY KEY (id)
);

INSERT INTO players_new (id, name, team)        -- runs left out
VALUES (7, 'Shubman Gill', 'GT');

INSERT INTO players_new (id, name, team, runs)  -- runs given
VALUES (8, 'Rinku Singh', 'KKR', 120);

SELECT * FROM players_new;

How it works: Gill's row left out runs, so the DEFAULT 0 filled it in. Rinku's row gave 120, so the default was ignored.

Bonus — IDENTITY (auto-numbering in SQL Server)

Typing ids by hand is tedious. SQL Server can generate them:

CREATE TABLE fans (
    fan_id INT IDENTITY(1,1) PRIMARY KEY,   -- starts at 1, adds 1 each row
    name   VARCHAR(50) NOT NULL
);
INSERT INTO fans (name) VALUES ('Ravi'), ('Priya');   -- fan_id becomes 1, 2

IDENTITY(seed, increment) — seed is the first number, increment is the step. MySQL calls this AUTO_INCREMENT; PostgreSQL uses GENERATED ALWAYS AS IDENTITY (or SERIAL).

Common mistakes

• ❌ Two PRIMARY KEY clauses on different columns. ✅ Only one primary key per table — use a composite key PRIMARY KEY (a, b) if you need two columns.
• ❌ Thinking DEFAULT stops NULLs. ✅ DEFAULT only fills missing values; add NOT NULL to block explicit NULLs.
• ❌ Confusing NULL with 0 or ''. ✅ NULL means unknown; 0 is a real number.
• ❌ Leaving constraints unnamed. ✅ Name them (pk_players) — the error messages become readable and you can drop them later by name.

Interview tip

Q: Can a primary key contain NULL values? Can a table have two primary keys? A: No and no. A primary key is automatically NOT NULL and UNIQUE, and a table has at most one primary key — though that one key may be made of several columns (a composite key).