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.