Right, so you’ve told your data it has to be there (NOT NULL) and what it has to look like (CHECK). Now let’s talk about telling it to be, well, unique. A UNIQUE constraint is how you tell the database, “Look, I don’t care what value you put in this column, but it had darn well better be different from every other value in this column for all the other rows.” It’s the database’s way of enforcing that one-of-a-kind snowflake status, but for your data.

Think of it as a softer, more flexible cousin of the PRIMARY KEY. A table can only have one primary key, but it can have as many UNIQUE constraints as you want. And unlike a primary key, a UNIQUE constraint can happily contain NULL values (and yes, we’ll get to the glorious mess that causes in a minute).

The Single-Column Uniqueness Guarantee

This is the most straightforward use. You have one column, say an email address or a product_sku, that must be unique across the entire table. The syntax is beautifully simple.

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE, -- This is the magic line
    created_at TIMESTAMPTZ DEFAULT NOW()
);

Here, the UNIQUE constraint slapped right on the email column means the database will flat-out reject any attempt to insert a duplicate email. It does this by automatically creating a unique index behind the scenes. That’s the real mechanism—an index that’s optimized for fast lookups to check for duplicates whenever you INSERT or UPDATE. This is also why it’s fast to JOIN on a uniquely constrained column; the database has a super-highway index to look it up.

The Multi-Column (Composite) Circus

This is where it gets interesting, and where people’s brains sometimes short-circuit. A multi-column UNIQUE constraint doesn’t mean that each column is unique individually. Oh no, that would be too simple. It means that the combination of values across those columns must be unique.

Let’s say you’re building a dreadful, legacy-style logging table where you can only have one log entry per day per user. The individual user_id will repeat. The log_date will definitely repeat. But the pair must be unique.

CREATE TABLE user_daily_logs (
    log_id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(user_id),
    log_date DATE NOT NULL DEFAULT CURRENT_DATE,
    log_text TEXT,
    -- The constraint is defined at the table level
    CONSTRAINT unique_user_per_day UNIQUE (user_id, log_date)
);

Now, try to insert these two rows:

INSERT INTO user_daily_logs (user_id, log_date) VALUES (1, '2023-10-25'); -- OK
INSERT INTO user_daily_logs (user_id, log_date) VALUES (1, '2023-10-25'); -- FAIL!

The second insert fails, as it should. The combination (1, 2023-10-25) already exists.

But this will work perfectly fine:

INSERT INTO user_daily_logs (user_id, log_date) VALUES (1, '2023-10-26'); -- OK
INSERT INTO user_daily_logs (user_id, log_date) VALUES (2, '2023-10-25'); -- OK

Because the combinations (1, 2023-10-26) and (2, 2023-10-25) are new and unique.

The NULL Gotcha: A Tale of Incomparable Weirdness

Remember when I said UNIQUE constraints allow NULLs? This is the designers’ questionable choice that will haunt your dreams. The SQL standard states that NULL is not equal to anything—not even to another NULL. So, how does a uniqueness check, which is fundamentally based on equality, handle something that is never equal to anything else?

It just gives up and says, “Fine, you’re all unique in your own special, undefined way.”

Let’s look at a table with a nullable unique column:

CREATE TABLE coupons (
    coupon_id SERIAL PRIMARY KEY,
    coupon_code VARCHAR(10) UNIQUE, -- This can be NULL
    discount_percent INTEGER NOT NULL
);

INSERT INTO coupons (coupon_code, discount_percent) VALUES ('SAVE10', 10); -- OK
INSERT INTO coupons (coupon_code, discount_percent) VALUES (NULL, 15); -- OK
INSERT INTO coupons (coupon_code, discount_percent) VALUES (NULL, 20); -- Also OK!
INSERT INTO coupons (coupon_code, discount_percent) VALUES ('SAVE10', 25); -- FAIL!

See the absurdity? You can have a million rows with a NULL coupon_code. They all violate the spirit of a “unique” code (how can “nothing” be a valid, unique code?), but they satisfy the pedantic, technical definition of the constraint. If you need a “unique-but-also-optional” column, you often have to combine UNIQUE with a CHECK constraint to prevent blank strings or some other hack. It’s a rough edge. I told you I’d be honest.

Best Practices and The Index Free Lunch

  1. The Free Index: Remember, a UNIQUE constraint is an index. It will speed up queries that search, join, or filter by that column (or set of columns). This isn’t a free lunch; it’s a lunch you were going to have to pay for anyway (with a manual CREATE INDEX), and the constraint is just picking up the tab. Defining a UNIQUE constraint for the sake of having the index is a perfectly valid strategy.

  2. Naming Your Constraints: Always, always, always name your table-level constraints. The database will autogenerate a name like tablename_columnname_key if you don’t. Later, when you need to drop this constraint because your business logic changed (and it will), trying to drop users_email_key is far less terrifying than trying to drop whatever random name the system generated for you. Name them for what they are, like unique_user_email.

  3. Order Matters in Composites: In a multi-column unique constraint, the order of columns in the definition matters for the underlying index. A constraint on (user_id, log_date) is excellent for queries looking for a specific user’s logs or a specific user on a specific date. It’s mostly useless for a query looking for all activity on a specific date across all users. Plan the order based on how you’ll most frequently query the data.