sql_unique

SQL UNIQUE Constraint

The SQL UNIQUE Constraint

The UNIQUE constraint ensures that all values in a column or a set of columns are distinct from one another within a table. This means no two rows can have the same value (or combination of values) in the specified unique column(s).

Both UNIQUE and PRIMARY KEY constraints guarantee uniqueness for a column or set of columns. However, there are key differences:

• A table can have only one PRIMARY KEY constraint, but it can have multiple UNIQUE constraints.

• PRIMARY KEY columns automatically enforce NOT NULL. UNIQUE constraint columns, by default, can allow one NULL value (though some databases might allow multiple NULLs, behavior can vary; typically, NULL is not considered equal to another NULL). It’s common to also apply NOT NULL to a UNIQUE column if NULLs are not desired.

SQL UNIQUE Constraint on CREATE TABLE

The following SQL creates a UNIQUE constraint on the Email column when the Users table is created.

General Syntax (most databases like SQL Server, Oracle, PostgreSQL, MySQL):

CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    Username VARCHAR(50) NOT NULL UNIQUE, -- Inline UNIQUE constraint
    Email VARCHAR(100) NOT NULL,
    DateRegistered DATE,
    CONSTRAINT UQ_User_Email UNIQUE (Email) -- Named UNIQUE constraint
);

MySQL Specific Syntax (alternative for inline):

CREATE TABLE Users (
    UserID INT NOT NULL,
    Username VARCHAR(50) NOT NULL,
    Email VARCHAR(100) NOT NULL,
    DateRegistered DATE,
    PRIMARY KEY (UserID),
    UNIQUE (Username),
    UNIQUE KEY UQ_User_Email (Email) -- MySQL specific 'UNIQUE KEY'
);

To define a UNIQUE constraint on multiple columns (a composite unique key), you must use the named constraint syntax:

MySQL / SQL Server / Oracle / PostgreSQL:

CREATE TABLE ProductSuppliers (
    ProductID INT,
    SupplierID INT,
    SupplyPrice DECIMAL(10, 2),
    PRIMARY KEY (ProductID, SupplierID), -- Assuming this is the PK
    CONSTRAINT UQ_ProductSupplier_Price UNIQUE (ProductID, SupplierID, SupplyPrice) -- Example for multi-column unique
);

A more realistic multi-column unique constraint might be UNIQUE (columnA, columnB) if no two rows should have the same combination of values for columnA and columnB.

SQL UNIQUE Constraint on ALTER TABLE

To create a UNIQUE constraint on a column when the table already exists, use the ALTER TABLE statement.

MySQL / SQL Server / Oracle / PostgreSQL:

ALTER TABLE Users
ADD UNIQUE (Username);

To name a UNIQUE constraint, or to define it on multiple columns:

MySQL / SQL Server / Oracle / PostgreSQL:

ALTER TABLE Users
ADD CONSTRAINT UQ_Username UNIQUE (Username);

-- For multiple columns:
-- ALTER TABLE UserProfiles
-- ADD CONSTRAINT UQ_UserProfile_Detail UNIQUE (UserID, ProfileType);

To DROP a UNIQUE Constraint

To drop a UNIQUE constraint, you need to know its name. If you didn’t name it, the system assigned a default name which you’ll need to find (usually via database metadata tables).

MySQL:

ALTER TABLE Users
DROP INDEX UQ_Username; -- Or the system-generated name

SQL Server / Oracle / PostgreSQL:

ALTER TABLE Users
DROP CONSTRAINT UQ_Username; -- Or the system-generated name

Practical Use Cases:

• Ensuring email addresses or usernames are unique in a user registration system.

• Preventing duplicate product codes or serial numbers in an inventory table.

• Enforcing that a combination of StudentID and CourseID is unique in an enrollment table, meaning a student can only enroll in a specific course once.