MySQL UNIQUE Constraint

  1. Overview
  2. MYSQL
  3. MySQL Database
  4. MySQL UNIQUE Constraint

The UNIQUE constraint in MySQL ensures that all values in a specified column or combination of columns are distinct. This is useful for ensuring data integrity and preventing duplicate values in columns that require unique values.

  • The UNIQUE constraint ensures that no two rows in the table can have the same value for the specified column(s).
  • While the PRIMARY KEY constraint also enforces uniqueness, a PRIMARY KEY column cannot accept NULL values, while a column with a UNIQUE constraint can.

Key Differences Between UNIQUE and PRIMARY KEY:

  1. Uniqueness: Both constraints enforce uniqueness, but:
    • A table can have only one PRIMARY KEY, whereas it can have multiple UNIQUE constraints.
    • A PRIMARY KEY automatically has a UNIQUE constraint.
    • A UNIQUE constraint allows NULL values (but only one NULL is allowed in a column with a UNIQUE constraint).
  2. NULL Values: A column with a UNIQUE constraint can contain NULL values, but a column with a PRIMARY KEY constraint cannot contain NULL values.

Using the UNIQUE Constraint

1. UNIQUE Constraint on Table Creation

You can apply the UNIQUE constraint while creating a table. This guarantees that all the values in the specified column(s) are unique.

Example: Create Table with UNIQUE Constraint on a Single Column
CREATE TABLE Persons (
    ID INT NOT NULL,
    LastName VARCHAR(255) NOT NULL,
    FirstName VARCHAR(255),
    Age INT,
    UNIQUE (ID)
);

In this example:

  • The ID column is constrained to have unique values across all records.
  • No two rows can have the same ID.
Example: Create Table with UNIQUE Constraint on Multiple Columns
CREATE TABLE Persons (
    ID INT NOT NULL,
    LastName VARCHAR(255) NOT NULL,
    FirstName VARCHAR(255),
    Age INT,
    CONSTRAINT UC_Person UNIQUE (ID, LastName)
);

In this example:

  • A composite unique constraint is applied to the combination of ID and LastName, ensuring that the combination of these two values must be unique across all records in the table.
  • The constraint is named UC_Person.

2. Adding UNIQUE Constraint to an Existing Table

You can add a UNIQUE constraint to an existing column using the ALTER TABLE statement.

Example: Add UNIQUE Constraint on an Existing Column
ALTER TABLE Persons
ADD UNIQUE (ID);

In this example:

  • A UNIQUE constraint is added to the ID column in the Persons table, ensuring that all values in the ID column are distinct.
Example: Add UNIQUE Constraint on Multiple Columns
ALTER TABLE Persons
ADD CONSTRAINT UC_Person UNIQUE (ID, LastName);

In this example:

  • A composite UNIQUE constraint is added to ensure that the combination of ID and LastName is unique in the Persons table.

3. Dropping a UNIQUE Constraint

To remove a UNIQUE constraint from a table, you use the DROP INDEX statement, which drops the index associated with the UNIQUE constraint.

Example: Drop a UNIQUE Constraint
ALTER TABLE Persons
DROP INDEX UC_Person;

In this example:

  • The UNIQUE constraint named UC_Person is dropped from the Persons table.

Important Notes:

  • Multiple UNIQUE Constraints: A table can have multiple UNIQUE constraints. However, it can have only one PRIMARY KEY constraint.
  • NULL Values: A UNIQUE constraint allows NULL values, but only one NULL value is allowed per column with a UNIQUE constraint. If multiple NULL values are inserted into the same column, the constraint will not raise an error because NULL is treated as a distinct value.
  • Naming Constraints: You can name a UNIQUE constraint when you want to reference it easily later (e.g., to drop or modify it). This is especially useful when applying the constraint to multiple columns.

Summary of UNIQUE Constraint Usage:

  • On Table Creation: The UNIQUE constraint ensures that all values in a specified column or combination of columns are unique.
  • On Table Modification: You can add a UNIQUE constraint to an existing table using the ALTER TABLE statement.
  • Dropping Constraints: The UNIQUE constraint can be removed using the DROP INDEX statement.

The UNIQUE constraint helps ensure data integrity by preventing duplicate values in a column, making it essential for fields like email addresses, usernames, or product IDs.

Was this article helpful?