MySQL Unique Constraint Syntax Example

By | 2019-05-04T17:41:40+05:30 May 4th, 2019|mysql|

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.

A unique constraint is similar like primary key, but in a table, one primary key will be allowed whereas unique constraint can be applied to one or more columns.

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:

Happy Learning 🙂

About the Author:

Hi Folks, you have reach this so far, that shows you like what you are learning. Then why don't you support us to improve for bettor tutorials by leaving your valuable comments and why not you keep in touch with us for latest updates on your favorite blog @ facebook , twitter , Or Google+ ,

Leave A Comment