CSE Roadmap

Core tier · about 3 hours · 5 parts

Part 4 of 5 · 1 min read

UNIQUE: no primary key needed

Requiring a column's values to never repeat, without making it the row's identifier.

UNIQUE: no primary key needed

Think of a company requiring every employee to have a different badge number. The company's official employee ID, not the badge number, is still what every other system actually uses to identify them.

In plain words: UNIQUE requires a column's values to never repeat across rows, the same way PRIMARY KEY does, but without making that column the table's official identifier. A table can have a primary key and one or more separate UNIQUE constraints at the same time.

A worked example. Requiring every customer's email address to be unique, while customer_id stays the actual primary key.

The key lines.

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    full_name   TEXT NOT NULL,
    email       TEXT UNIQUE,
    address     TEXT NOT NULL
);

UNIQUE constraint

A UNIQUE constraint requires a column's values to never repeat across rows, without making that column the table's primary key.

Example: email TEXT UNIQUE stops two customers from sharing the same email, even though customer_id remains the actual primary key used by foreign keys elsewhere.

Remember this

Every constraint here does the same basic thing: it rejects a write before it happens, rather than allowing bad data in and hoping to catch it later. This is what makes database-level constraints stronger than an application-layer check that some future script might simply forget to run.

Quick notes

  • PRIMARY KEY: unique and never NULL, identifying each row.
  • FOREIGN KEY: must match an existing row in another table, or be NULL if nullable.
  • NOT NULL: forbids a column from ever being blank.
  • CHECK: a custom condition, possibly across several columns of the same row.
  • UNIQUE: values never repeat, without becoming the table's primary key.