SQL Unique

What UNIQUE does

A UNIQUE constraint prevents duplicate entries in a column. It is useful for fields that should identify something distinctly but are not the table's primary key, such as an email address, a username, or a national ID number. When you try to insert or update a row so that it would repeat an existing value, the database rejects the change.

A table can have many UNIQUE constraints but only one PRIMARY KEY. This is the main practical difference between the two: UNIQUE lets you enforce distinctness on several columns at once, while the primary key marks the single official identifier for each row.

Defining UNIQUE at table creation

CREATE TABLE Users (
    UserID     INT NOT NULL PRIMARY KEY,
    Email      VARCHAR(255) UNIQUE,
    Username   VARCHAR(50),
    CONSTRAINT uq_username UNIQUE (Username)
);

Email uses a short column-level form, while Username uses a named table-level form. Both achieve the same result, but the named version lets you refer to the constraint later by the name uq_username.

Uniqueness across multiple columns

You can require a combination of columns to be unique together even when each column on its own may repeat. For example, the same room can be booked on many dates, and the same date can have many rooms, but a specific room on a specific date should appear only once.

CREATE TABLE Bookings (
    BookingID  INT NOT NULL PRIMARY KEY,
    RoomID     INT NOT NULL,
    BookedDate DATE NOT NULL,
    CONSTRAINT uq_room_date UNIQUE (RoomID, BookedDate)
);

• A single column can be marked UNIQUE to block repeated values.
• Several columns can be combined so only the combination must be distinct.
• Most databases allow one NULL in a UNIQUE column, since NULL is treated as unknown rather than equal to another NULL.
• Add a UNIQUE constraint to an existing table with ALTER TABLE ... ADD CONSTRAINT.

ALTER TABLE Users
ADD CONSTRAINT uq_email UNIQUE (Email);