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', '[email protected]', 'USA');

INSERT INTO Customers (CustomerID, Name, Email, Country)
VALUES (2, 'Jane Smith', '[email protected]', 'Canada');

-- This will fail because '[email protected]' already exists
INSERT INTO Customers (CustomerID, Name, Email, Country)
VALUES (3, 'Alice Johnson', '[email protected]', 'UK');

The third insert will fail because the Email [email protected] 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.

Suggested Quiz

Edit Quiz

6 Questions

What does the UNIQUE constraint ensure in SQL?

• A

  Column stores only values that remain non-duplicate

• B

  Column allows values without checking duplicates

• C

  Column accepts multiple identical values in rows

• D

  Column stores only values that are always non-NULL

Explanation:

UNIQUE ensures that all values in the column (or column group) must be distinct, preventing duplicate entries.

How does UNIQUE differ from a PRIMARY KEY?

• A

  UNIQUE allows many NULL values; primary key does not

• B

  UNIQUE removes NULLs; primary key always stores NULL

• C

  UNIQUE forces indexing; primary key prevents indexes

• D

  UNIQUE enforces sorting; primary key avoids ordering

Explanation:

UNIQUE permits multiple NULL values because each NULL is considered different, unlike a PRIMARY KEY which forbids NULLs entirely.

What happens when inserting a duplicate value in a UNIQUE column?

• A

  SQL accepts duplicate row and stores both values

• B

  SQL replaces earlier value with most recently inserted

• C

  SQL rejects duplicate and generates an integrity error

• D

  SQL converts duplicate value into a NULL automatically

Explanation:

If a value violates the UNIQUE constraint, SQL blocks the insert or update and reports an error.

Which statement about UNIQUE and NULL is correct?

• A

  UNIQUE treats all NULL values as equal duplicates

• B

  UNIQUE treats all NULL values as separate unique rows

• C

  UNIQUE converts all NULL entries into empty strings

• D

  UNIQUE allows NULL only when no data exists in column

Explanation:

Multiple NULLs are allowed because each NULL is evaluated as a distinct, incomparable value under UNIQUE.

Why do many databases create an index for UNIQUE?

• A

  To organize values alphabetically before inserting

• B

  To format numeric data before storing into table

• C

  To convert NULLs into unique sequential values

• D

  To improve checking of duplicates during validation

Explanation:

An index helps the engine quickly detect duplicate values, making UNIQUE constraint enforcement more efficient.

What does the UNIQUE keyword check in a subquery?

• A

  Ensures subquery returns exactly one numeric column

• B

  Ensures subquery output contains only distinct rows

• C

  Ensures subquery compares values without using joins

• D

  Ensures subquery ignores rows that contain any NULL

Explanation:

UNIQUE validates that the subquery produces no duplicate tuples—each returned row must differ in at least one attribute.

Quiz Completed Successfully

Your Score :   2/6

Accuracy :  0%

Login to View Explanation

1/6 1/6 < Previous Next >