SQL | UNIQUE Constraint

The UNIQUE constraint in SQL ensures that values in a column or set of columns are distinct, preventing duplicates. Unlike a PRIMARY KEY, it allows multiple NULL values since each NULL is treated as unique, while a primary key requires all values to be unique and non-NULL.

Features:

• Ensures column(s) have unique values.
• Multiple NULLs are permitted.
• Can apply to one or more columns.
• Does not automatically create an index (though many databases do for performance).
• Can be added or removed using ALTER TABLE.

Syntax:

CREATE TABLE table_name (
 column1 datatype UNIQUE,
 column2 datatype,
 ...
);

In the above syntax:

• CREATE TABLE table_name: creates a new table.
• column1 datatype UNIQUE: defines a column with a data type and enforces unique values.
• column2 datatype: defines another column without the unique constraint.
• Repeat for additional columns as needed.

Example of Using the SQL UNIQUE Constraint

Example 1: Creating a Table with UNIQUE Constraints

Let's create a Customers table where the Email column must be unique.

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(100),
    Email VARCHAR(100) UNIQUE,
    Country VARCHAR(50)
);

In this case, each customer must have a unique email address. If you try to insert a duplicate email, SQL will raise an error.

INSERT INTO Customers (CustomerID, Name, Email, Country)
VALUES (1, 'John Doe', 'john.doe@example.com', 'USA');

INSERT INTO Customers (CustomerID, Name, Email, Country)
VALUES (2, 'Jane Smith', 'jane.smith@example.com', 'Canada');

-- This will fail because 'john.doe@example.com' already exists
INSERT INTO Customers (CustomerID, Name, Email, Country)
VALUES (3, 'Alice Johnson', 'john.doe@example.com', 'UK');

The third insert will fail because the Email john.doe@example.com already exists in the Customers table.

Example 2: Using UNIQUE with Multiple Columns

We can also apply the UNIQUE constraint to multiple columns to ensure that the combination of those columns is unique.

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    ProductID INT,
    OrderDate DATE,
    UNIQUE (CustomerID, ProductID)
);

In this example, the combination of CustomerID and ProductID must be unique, meaning a customer cannot order the same product more than once.

Example 3: Checking for Unique Values Using Subqueries

SQL allows you to check for uniqueness in subqueries. You can use the UNIQUE keyword in a subquery to ensure that the results do not contain duplicate values.

SELECT CustomerID
FROM Orders
WHERE UNIQUE (
    SELECT OrderID
    FROM OrderDetails
    WHERE Orders.CustomerID = OrderDetails.CustomerID
);

In this example, we check if there are any duplicate OrderID values for each customer in the Orders table. If the subquery returns unique values, the CustomerID will be selected.

Important Points

• Evaluates to true on an empty subquery.
• Returns true only if there are unique tuples present as the output of the sub-query (two tuples are unique if the value of any attribute of the two tuples differs).
• Returns true if the sub-query has two duplicate rows with at least one attribute as NULL.