Previous
Written by: rJTutorial Team | Last Updated:

SQL UNIQUE Constraint

The UNIQUE constraint is a fundamental tool for maintaining data integrity in a database. Its primary job is to ensure that all values in a specific column or a combination of columns are distinct across every row in a table. By using this constraint, you instruct the database engine to automatically reject any attempt to insert or update data that would result in a duplicate entry.

Developer Tip: While both PRIMARY KEY and UNIQUE enforce uniqueness, a table can have only one Primary Key but multiple Unique constraints. Use UNIQUE for secondary identifiers like email addresses, phone numbers, or usernames.

 

Key Features of SQL UNIQUE Constraint

  • Prevents Duplicates: It acts as a gatekeeper, ensuring no two rows have the same value in the constrained column.
  • Multiple Constraints: You can apply UNIQUE to as many columns as needed within a single table.
  • Handling NULLs: Unlike a Primary Key, a UNIQUE column can typically accept NULL values. In most SQL dialects (like PostgreSQL and SQL Server), you can even have multiple NULLs because NULL is treated as an "unknown" value rather than a duplicate.
  • Composite Uniqueness: It can be applied to a combination of columns, ensuring that the pair (or triplet) of values is unique, even if individual values within those columns are repeated.
Watch Out: Be careful with how your specific database handles multiple NULL values in a unique column. While standard SQL allows multiple NULLs, some older configurations or specific engines might treat two NULLs as duplicates.

 

SQL UNIQUE Constraint Syntax

Defining UNIQUE in Table Creation

When designing a new table, you can define the constraint directly next to the column definition. This is the most common way to ensure your data stays clean from day one.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Email VARCHAR(100) UNIQUE,
    Username VARCHAR(50) UNIQUE
);
  • In this example, the Email column is protected. If you try to register a second employee with an email that already exists in the table, the database will throw an error.

Adding UNIQUE to Multiple Columns (Composite Constraint)

Sometimes, uniqueness depends on a combination of factors. For example, you might allow two people to have the same first name, but you don't want two people with the same first and last name in the same department.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    DepartmentID INT,
    UNIQUE (FirstName, LastName)
);
  • This ensures that the combination of FirstName and LastName is unique. You can have "John Smith" and "Jane Smith," but you cannot have two "John Smiths."
Best Practice: Always give your constraints a meaningful name when using the CONSTRAINT keyword. It makes maintenance and debugging much easier when the database returns an error like "Violation of UQ_Employee_Email" instead of a generic system-generated ID.

 

Adding a UNIQUE Constraint to an Existing Table

If you realize later that a column needs to be unique, you can modify the table using the ALTER TABLE command. However, this will only work if the existing data in that column is already unique.

ALTER TABLE Employees  
ADD CONSTRAINT UQ_Email UNIQUE (Email);
Common Mistake: Attempting to add a UNIQUE constraint to a column that already contains duplicate values. You must clean up or remove the duplicate data before the ALTER TABLE command will succeed.

 

Removing a UNIQUE Constraint

There are times when business requirements change and a column no longer needs to be unique. The syntax for removing a constraint varies slightly depending on your database engine (e.g., MySQL uses DROP INDEX, while SQL Server/PostgreSQL use DROP CONSTRAINT).

-- For SQL Server / PostgreSQL / Oracle
ALTER TABLE Employees  
DROP CONSTRAINT UQ_Email;

-- For MySQL
ALTER TABLE Employees
DROP INDEX UQ_Email;

 

Inserting Data into a Table with UNIQUE Constraint

Let's look at how the database reacts when we interact with these constraints in a real-world scenario.

-- Step 1: Successfully inserting a new record
INSERT INTO Employees (EmployeeID, Email)  
VALUES (1, '[email protected]');  -- ✅ Success!

-- Step 2: Attempting to insert a duplicate email
INSERT INTO Employees (EmployeeID, Email)  
VALUES (2, '[email protected]');  -- ❌ Error: Duplicate entry '[email protected]' for key 'Email'

-- Step 3: Inserting a NULL value (if allowed)
INSERT INTO Employees (EmployeeID, Email)  
VALUES (3, NULL);  -- ✅ Success (Most SQL dialects allow this)

 

Summary

  • The UNIQUE constraint is vital for preventing duplicate data in non-primary key columns.
  • It can be applied to single columns or composite groups of columns.
  • It differs from a PRIMARY KEY because it allows NULL values and you can have many of them per table.
  • Adding constraints to existing tables requires the data to be clean (no existing duplicates) before the command is run.
Previous