SQL UNIQUE Constraint

The UNIQUE constraint is used to prevent having identical values in a column. That means the UNIQUE constraint ensures that all values in a column are different.

Both UNIQUE constraint and PRIMARY KEY constraint ensures uniqueness for a column or a set of columns.

A PRIMARY KEY constraint automatically has a UNIQUE constraint.

Use a UNIQUE constraint instead of a PRIMARY KEY constraint when you want to ensure the uniqueness of a column or a set of columns that is not the primary key.

You can either specify a UNIQUE constraint during the creating of a table using the CREATE TABLE statement or after the creation using the ALTER TABLE statement.

SQL UNIQUE Constraint on CREATE TABLE

The following SQL statement will create a UNIQUE constraint on the "id" column during the "Employee" table creation.

• For MySQL, MariaDB:

    CREATE TABLE employee (
      id INT NOT NULL,
      first_name VARCHAR(40),
      last_name VARCHAR(40),
      birth_date DATE,
      hire_date DATE,
  
      UNIQUE(id)
    );
  

• For Oracle, SQL Server:

    CREATE TABLE employee (
      id INT NOT NULL UNIQUE, 
      first_name VARCHAR(40),
      last_name VARCHAR(40),
      birth_date DATE,
      hire_date DATE
    );
  

You can also define a UNIQUE constraint for multiple columns. The following SQL statement will create a UNIQUE constraint for the combination of the "id", "first_name", and "last_name" columns of the "Person" table:

• For MySQL, MariaDB, Oracle, SQL Server:

    CREATE TABLE person (
      id INT NOT NULL,
      first_name VARCHAR(40) NOT NULL,
      last_name VARCHAR(40) NOT NULL,
      birth_date DATE,
    
      CONSTRAINT person_uq UNIQUE (id, first_name, last_name)
    );
  

SQL UNIQUE Constraint on ALTER TABLE

The following SQL statement will create a UNIQUE constraint on the "id" column on an already existed "Employee" table.

• For MySQL, MariaDB, Oracle, SQL Server:

  ALTER TABLE employee
      ADD UNIQUE(id);
  

You can also create a UNIQUE constraint on multiple columns. The following SQL statement will create a UNIQUE constraint for the combination of the "id", "first_name", and "last_name" columns of the "Person" table:

• For MySQL, MariaDB, Oracle, SQL Server:

  ALTER TABLE person
      ADD CONSTRAINT person_uq UNIQUE (id, first_name, last_name);
  

DROP a UNIQUE Constraint

You can drop a UNIQUE constraint using the ALTER TABLE statement.

The following SQL statement will drop the UNIQUE constraint from the "Employee" table.

• For MySQL, MariaDB:

  ALTER TABLE employee
      DROP INDEX employee_uq;
  

• For Oracle, SQL Server:

  ALTER TABLE employee
      DROP CONSTRAINT employee_uq;