In this tutorial, we will see MySQL unique constraint syntax.

MySQL Unique Constraint:

Unique constraint is used to represent one or more columns in a table as uiquely.

1. Create MySQL Unique Constraint Syntax:

The unique constraint ensures that all the values in the columns are unique.

Create a user table with a unique email address.

CREATE TABLE `user` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(150) NULL DEFAULT NULL,
  `email` VARCHAR(150) NULL DEFAULT NULL,
  `city` VARCHAR(100) NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `email` (`email`)
)ENGINE=InnoDB;

Verify:

We can verify this table structure using the describe statement.

mysql> describe user;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(150) | YES  |     | NULL    |                |
| email | varchar(150) | YES  | UNI | NULL    |                |
| city  | varchar(100) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
4 rows in set (0.05 sec)

2. Create Unique Constraint Multiple Columns:

It is also possible to make multiple columns as unique. Here I am making name and email columns as unique.

CREATE TABLE `user` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(150) NULL DEFAULT NULL,
  `email` VARCHAR(150) NULL DEFAULT NULL,
  `city` VARCHAR(100) NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `name_email` (`name`, `email`)
)ENGINE=InnoDB;

Verify:

mysql> describe user;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(150) | YES  | MUL | NULL    |                |
| email | varchar(150) | YES  |     | NULL    |                |
| city  | varchar(100) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

3. Add Unique Constraint:

If you want to add a unique constraint to a column in an existing table, you should use the alter command like below.

ALTER TABLE `user`
  ADD UNIQUE INDEX `email` (`email`);

-- For multiple columns

ALTER TABLE `user`
	ADD UNIQUE INDEX `name_email` (`name`, `email`);

4. Deleting Unique Constraint:

To remove the Unique constraint from a column using alter command.

ALTER TABLE `user`
  DROP INDEX `email`;

Verify:

mysql> describe user;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(150) | YES  |     | NULL    |                |
| email | varchar(150) | YES  |     | NULL    |                |
| city  | varchar(100) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

Done!

References:

• MySQL Unique Constraints
• MySQL create table Syntax example

Happy Learning 🙂