PRIMARY KEY and UNIQUE constraints both guarantee that values in a column (or set of columns) are distinct, but a PRIMARY KEY is the single defining identifier for a table's rows while UNIQUE is a more flexible, repeatable constraint for other columns that must also stay distinct.
Key Points: • A table allows exactly one PRIMARY KEY but can define multiple UNIQUE constraints. • PRIMARY KEY columns cannot contain NULL; UNIQUE columns can generally allow NULLs, and multiple NULLs are typically permitted since NULL is not considered equal to itself. • PRIMARY KEY is automatically indexed and is the natural target for foreign key relationships from other tables. • UNIQUE is often used for secondary business identifiers, like an email or SSN, that must be distinct but aren't the row's main key.
Example: An orders table might use order_id as its PRIMARY KEY while also enforcing a UNIQUE constraint on order_number, a human-readable identifier that must never repeat but isn't used to link to other tables.
Code Example:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
order_number VARCHAR(20) UNIQUE,
total DECIMAL(10,2)
);Interview Tip: A concise interview answer is:
"PRIMARY KEY uniquely identifies each row, is limited to one per table, and never allows NULLs, while UNIQUE enforces distinct values on other columns, can be applied multiple times per table, and generally permits NULLs. I use PRIMARY KEY for the row's core identifier and UNIQUE for secondary constraints like a business identifier that must also stay distinct."