Meritshot Tutorials

  1. Home
  2. »
  3. SQL UNIQUE Constraint

SQL Tutorial

SQL UNIQUE Constraint

The UNIQUE constraint in SQL ensures that all values in a column (or a group of columns) are distinct across all rows in the table. This means that no two rows can have the same value for the specified column(s). This constraint is useful for maintaining data integrity and ensuring that key attributes are unique.

Syntax

CREATE TABLE table_name (

    column_name datatype UNIQUE,

    …

);

— For multiple columns

CREATE TABLE table_name (

    column1 datatype,

    column2 datatype,

    UNIQUE (column1, column2)

);

  • column_name: The name of the column to which the UNIQUE constraint is applied.
  • datatype: The type of data that the column will store (e.g., INT, VARCHAR).

Example:

1 Creating a Table with a UNIQUE Constraint

Suppose you want to create a table called Customers where the Email column must have unique values:

CREATE TABLE Customers (

    CustomerID INT PRIMARY KEY,

    FullName VARCHAR(100),

    Email VARCHAR(100) UNIQUE,

    PhoneNumber VARCHAR(15)

);

In this table, the Email column cannot have duplicate values across the Customers table.

2 Adding a UNIQUE Constraint to an Existing Column

If you have an existing table Employees and want to ensure that the PhoneNumber column contains only unique values:

ALTER TABLE Employees

ADD CONSTRAINT unique_phone UNIQUE (PhoneNumber);

This command adds a UNIQUE constraint to the PhoneNumber column, ensuring no two employees can have the same phone number.

3 Creating a Composite UNIQUE Constraint

If you want to ensure that the combination of FirstName and LastName in the Employees table is unique:

CREATE TABLE Employees (

    EmployeeID INT PRIMARY KEY,

    FirstName VARCHAR(50),

    LastName VARCHAR(50),

    Email VARCHAR(100),

    UNIQUE (FirstName, LastName)

);

This command ensures that no two rows can have the same combination of FirstName and LastName.

4 Handling Unique Constraint Violations

If you try to insert a duplicate value into a column with a UNIQUE constraint:

INSERT INTO Customers (CustomerID, FullName, Email, PhoneNumber)

VALUES (1, ‘Rajesh Kumar’, ‘rajesh.kumar@example.com’, ‘9876543210’);

If rajesh.kumar@example.com already exists in the table, this command will result in an error due to the UNIQUE constraint on the Email column.

Tips to Remember

  1. Enforce Data Integrity: Use UNIQUE constraints to prevent duplicate data and maintain data integrity.
  2. Consider Composite Keys: You can apply UNIQUE constraints to multiple columns to ensure that combinations of values are unique.
  3. Handling Errors: When inserting or updating data, handle errors related to unique constraints by checking for existing values.

Frequently Asked Questions

Q1: Can a column with a UNIQUE constraint have NULL values?
A1: Yes, a column with a UNIQUE constraint can have multiple NULL values, as NULL is considered a distinct value.

Q2: Can I have multiple UNIQUE constraints on a single table?
A2: Yes, you can apply multiple UNIQUE constraints on different columns or combinations of columns within a table.

Q3: What happens if I try to insert a duplicate value into a column with a UNIQUE constraint?
A3: SQL will return an error, preventing the insertion or update of a row that would violate the uniqueness of the column.

Q4: How do I check which columns have UNIQUE constraints?
A4: Use a DESCRIBE or SHOW INDEX command to view the constraints and indexes applied to columns in a table.

Q5: Can I remove a UNIQUE constraint once it’s applied?
A5: Yes, you can drop a UNIQUE constraint using an ALTER TABLE command.

ALTER TABLE Customers

DROP CONSTRAINT unique_phone;