Constraints: PRIMARY KEY, NOT NULL, UNIQUE, CHECK, DEFAULT

Part of the free LogicWiz SQL & Databases course, Module 7: Creating tables. The queries and practice run in the app on real Postgres.

The college fee table has two students with roll number 101, a fee of -500, and a row with no name. Three different app screens wrote them. The database could have refused all three.

You'll learn to

• Add PRIMARY KEY, NOT NULL, UNIQUE, CHECK and DEFAULT to a table
• Explain how PRIMARY KEY and UNIQUE differ
• Predict what NULL does to UNIQUE and CHECK
• Write composite keys and named table-level constraints

Learn

Rules the database enforces

A constraint is a rule on a column or table that Postgres checks on every INSERT and UPDATE, whichever app or person sends it.

• PRIMARY KEY: identifies each row; unique and never NULL.
• NOT NULL: a value is required.
• UNIQUE: no two rows share this value.
• CHECK (condition): the value must pass a test.
• DEFAULT value: what to store when the INSERT leaves the column out.

App code has bugs and gets bypassed. Constraints are the rules that cannot be skipped.

All five in one table

CREATE TABLE students (
  roll_no    INT PRIMARY KEY,
  name       VARCHAR(40) NOT NULL,
  email      VARCHAR(60) UNIQUE,
  cgpa       NUMERIC(4,2) CHECK (cgpa BETWEEN 0 AND 10),
  is_active  BOOLEAN NOT NULL DEFAULT true
);

INSERT INTO students (roll_no, name, email, cgpa) VALUES
  (101, 'Aarav Sharma', '[email protected]', 8.6),
  (102, 'Meher Kaur', NULL, 9.1);

SELECT * FROM students;

The INSERT names four columns and leaves out is_active, so its DEFAULT true fills it in.

A duplicate roll number is refused

CREATE TABLE students (
  roll_no  INT PRIMARY KEY,
  name     VARCHAR(40) NOT NULL
);
INSERT INTO students VALUES (101, 'Aarav Sharma');
INSERT INTO students VALUES (101, 'Riya Patel');

The second INSERT fails: duplicate key value violates unique constraint "students_pkey". Postgres named the constraint for you: table name plus _pkey.

CHECK stops a negative fee

CREATE TABLE fees (
  roll_no  INT PRIMARY KEY,
  amount   NUMERIC(8,2) NOT NULL CHECK (amount > 0)
);
INSERT INTO fees VALUES (101, -500);

new row violates check constraint "fees_amount_check". The bad row never gets in, so no report ever has to deal with it.

PRIMARY KEY vs UNIQUE

Composite keys and named constraints

CREATE TABLE mess_bills (
  roll_no  INT NOT NULL,
  month    DATE NOT NULL,
  amount   NUMERIC(8,2) NOT NULL,
  CONSTRAINT mess_bills_pk PRIMARY KEY (roll_no, month),
  CONSTRAINT amount_positive CHECK (amount > 0)
);

SELECT conname, contype::text
FROM pg_constraint
WHERE conrelid = 'mess_bills'::regclass
ORDER BY conname;

One bill per student per month: the pair (roll_no, month) is the key, so the constraint goes at table level, after the columns. CONSTRAINT name gives it a readable name for error messages. In pg_constraint, p is primary key, c check, u unique, f foreign key.

CHECK only rejects a row when the condition is false. With NULL the condition is unknown, and unknown passes. So CHECK (amount > 0) alone lets a NULL amount in. If the value is required, add NOT NULL too.

"Primary key vs unique key" is one of the most asked DBMS questions. Know the words too: a candidate key is any minimal set of columns that can identify a row (roll_no, email); the one you pick is the primary key; the rest are alternate keys, which you declare UNIQUE.

In one line

Constraints are rules Postgres enforces on every write: PRIMARY KEY, NOT NULL, UNIQUE (allows NULLs), CHECK (NULL passes), DEFAULT.

Interview corner

What is the difference between a primary key and a unique key?

A table has one primary key, and it can never be NULL; it is the row's identity, which foreign keys reference. A table can have many UNIQUE constraints, and they allow NULLs (several of them in Postgres). Both create a unique index behind the scenes.

What are candidate, primary, alternate and composite keys?

A candidate key is any minimal set of columns that uniquely identifies a row. The one you choose as the main identity is the primary key; the other candidates are alternate keys (declare them UNIQUE). A composite key is a key made of more than one column, like (show_id, seat_no).

Does a CHECK constraint stop NULL values?

No. CHECK rejects a row only when the condition is false; with NULL it is unknown, so the row passes. Add NOT NULL when the value is required: amount NUMERIC NOT NULL CHECK (amount > 0).

Why enforce rules in the database when the app already validates input?

Many apps, scripts and people write to the same database, and any of them can have a bug or skip the check. Constraints are enforced on every write, can't be bypassed, and hold even under concurrent writes, where an app's "check then insert" can race.