Blog

In this guide, we will explain how to create, add, and drop unique constraints in MySQL with syntax and examples.

What is a unique constraint in MySQL?

A unique constraint is a single field or combination of fields that uniquely defines a record. Some of the fields can contain null values as long as the combination of values is unique.

What is the difference between a unique constraint and a primary key?

Create unique Contraint – Using a CREATE TABLE statement

The syntax for creating a unique constraint using a CREATE TABLE statement in MySQL is:

CREATE TABLE table_name
(
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...

  CONSTRAINT constraint_name UNIQUE (uc_col1, uc_col2, ... uc_col_n)
);

table_nameThe name of the table that you wish to create.column1, column2The columns that you wish to create in the table.constraint_nameThe name of the unique constraint.uc_col1, uc_col2, … uc_col_nThe columns that make up the unique constraint.

Example

Let’s look at an example of how to create a unique constraint in MySQL using the CREATE TABLE statement.

CREATE TABLE contacts
( contact_id INT(11) PRIMARY KEY AUTO_INCREMENT,
  reference_number INT(11) NOT NULL,
  last_name VARCHAR(30) NOT NULL,
  first_name VARCHAR(25),
  birthday DATE,
  CONSTRAINT contacts_unique UNIQUE (contact_id)
);

In this example, we’ve created a unique constraint on the contacts table called contacts_unique. It consists of only one field – the reference_number field.

We could also create a unique constraint with more than one field as in the example below:

CREATE TABLE contacts
( contact_id INT(11) PRIMARY KEY AUTO_INCREMENT,
  reference_number INT(11) NOT NULL,
  last_name VARCHAR(30) NOT NULL,
  first_name VARCHAR(25),
  birthday DATE,
  CONSTRAINT contacts_unique UNIQUE (last_name, first_name)
);

Create unique contraint – Using an ALTER TABLE statement

The syntax for creating a unique constraint using an ALTER TABLE statement in MySQL is:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ... column_n);

table_nameThe name of the table to modify. This is the table that you wish to add a unique constraint to.constraint_nameThe name of the unique constraint.column1, column2, … column_nThe columns that make up the unique constraint.

Example

Let’s look at an example of how to add a unique constraint to an existing table in MySQL using the ALTER TABLE statement.

ALTER TABLE contacts
ADD CONSTRAINT contacts_unique UNIQUE (reference_number);

In this example, we’ve created a unique constraint on the existing contacts table called contacts_unique. It consists of the field called reference_number.

We could also create a unique constraint with more than one field as in the example below:

ALTER TABLE contacts
ADD CONSTRAINT contact_name_unique UNIQUE (last_name, first_name);

Drop Unique Constraint

The syntax for dropping a unique constraint in MySQL is:

ALTER TABLE table_name
DROP INDEX constraint_name;

table_nameThe name of the table to modify. This is the table that you wish to remove the unique constraint from.constraint_nameThe name of the unique constraint to remove.

Example

Let’s look at an example of how to remove a unique constraint from a table in MySQL.

ALTER TABLE contacts
DROP INDEX contacts_unique;

In this example, we’re dropping a unique constraint on the contacts table called contacts_unique.

Next Topic : Click Here