SQL - Unique Key

SQL - The UNIQUE Constraint

What is a UNIQUE Constraint?

The UNIQUE constraint ensures that all values in a column or a set of columns are distinct from one another. It prevents duplicate entries in the specified column(s), which is crucial for maintaining data integrity.

For example, in a users table, you would want to ensure that every user has a unique email address. Applying a UNIQUE constraint to the email column enforces this rule at the database level.

UNIQUE vs. PRIMARY KEY

While both UNIQUE and PRIMARY KEY constraints enforce uniqueness, they have key differences:

• Number per Table: A table can have only one PRIMARY KEY, but it can have multiple UNIQUE constraints.
• NULL Values: A PRIMARY KEY column cannot contain NULL values. By definition, it is both UNIQUE and NOT NULL. A UNIQUE constraint, however, typically allows one or more NULL values (the exact behavior can vary between database systems; for instance, SQL Server allows multiple NULLs, while PostgreSQL and MySQL allow multiple NULLs as of recent versions, treating each NULL as distinct).
• Purpose: The PRIMARY KEY is the main identifier for a record in a table. UNIQUE constraints are used for other columns that must be unique but are not the primary identifier (e.g., email, employee_id, product_sku).

Defining a UNIQUE Constraint

You can define a UNIQUE constraint when you create a table.

Syntax for a Single Column

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL
);

Syntax for Multiple Columns (Composite Unique Key)

A composite unique key ensures that the combination of values in the specified columns is unique.

CREATE TABLE event_registrations (
    event_id INT,
    user_id INT,
    registration_date TIMESTAMP,
    PRIMARY KEY (event_id, user_id),
    UNIQUE (event_id, user_id) -- Ensures a user can't register for the same event twice
);

Verification

If you try to insert a duplicate value into a unique column, the database will return an error.

INSERT INTO users (username, email, password_hash) VALUES ('john.doe', 'john.doe@example.com', 'hash123');
-- Success

INSERT INTO users (username, email, password_hash) VALUES ('jane.doe', 'john.doe@example.com', 'hash456');
-- ERROR: Duplicate entry 'john.doe@example.com' for key 'email'

Adding a UNIQUE Constraint to an Existing Table

You can add a UNIQUE constraint to an existing table using the ALTER TABLE statement. It’s good practice to name your constraints.

Syntax

-- The table 'products' already exists with a column 'sku'
ALTER TABLE products
ADD CONSTRAINT uq_product_sku UNIQUE (sku);

Note: This operation will fail if the column already contains duplicate values. You must clean the data first.

Dropping a UNIQUE Constraint

To remove a UNIQUE constraint, you also use ALTER TABLE, referencing the constraint by its name.

Syntax

The exact syntax varies slightly by RDBMS.

-- For MySQL:
ALTER TABLE products DROP INDEX uq_product_sku;

-- For PostgreSQL / SQL Server:
ALTER TABLE products DROP CONSTRAINT uq_product_sku;

Best Practices

• Always name your constraints. It makes them easier to manage and drop later.
• Use PRIMARY KEY for the single, non-null, core identifier of a row.
• Use UNIQUE for business keys or other fields that must be unique but are not the primary identifier, such as emails, usernames, or SKU codes.