What is the difference between PRIMARY KEY and UNIQUE constraints?

Both reject duplicate values, but a primary key also forbids NULLs, and a table can have only one. A table can have many UNIQUE constraints, and their columns accept NULLs.

• A primary key is the row's main identifier, and foreign keys usually point to it. PostgreSQL requires its values to be unique and not null.
• A UNIQUE column accepts many NULLs by default, because two NULLs never count as equal. From PostgreSQL 15, declaring it UNIQUE NULLS NOT DISTINCT allows only one NULL.
• Both create a unique B-tree index automatically, and a foreign key can reference either one.

CREATE TABLE users (
    id SERIAL PRIMARY KEY,        -- Cannot be NULL
    email VARCHAR(255) UNIQUE,    -- Can have many NULLs
    username VARCHAR(50) UNIQUE   -- Multiple unique constraints allowed
);