Beginners guide to MySQL unique key

by  
Share:
unique key

A unique key is a key that can be a column or a group of columns that defines uniqueness for that row in the table. In this tutorial, I will be explaining, how to add/drop unique key in MySQL.

The unique key is different from the primary key. Some of the properties of a unique key are listed below:

  • More than one unique key in one table
  • Can be candidate key
  • Can be NULL, multiple rows can be NULL as well.

Add unique key on a single column in a table

Let’s take an example. We will create a table named “users” that will store the data of people registering on some website.

Take a look at the fields of the table.

Column Type Required Comments
uid integer Yes Primary key – will be auto increment as well
uname varchar Yes Name of the person
uemail varchar Yes Unique Key – email address of the person

Take a look below at the SQL syntax to create the table in MySQL.

CREATE TABLE IF NOT EXISTS `users` (
  `uid` int(11) NOT NULL,
  `uname` varchar(255) NOT NULL,
  `uemail` varchar(100) NOT NULL,
  PRIMARY KEY (`uid`),
  UNIQUE (`uemail`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Let me show you another technique to achieve the same result. The trick here to add unique key after table is created.

CREATE TABLE IF NOT EXISTS `users` (
  `uid` int(11) NOT NULL,
  `uname` varchar(255) NOT NULL,
  `uemail` varchar(100) NOT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

ALTER TABLE `users` ADD UNIQUE  (`uemail`);

Drop unique key on a single column in a table

In case, you want to drop the unique key (just the uniqueness) not the column. Use the SQL below to drop the unique key.

ALTER TABLE `users` DROP INDEX `uemail`;

Add multiple unique keys in a table

Similar to the previous example, we will create two unique keys in the “users” table.

Take a look at the fields of the table.

Column Type Required Comments
uid integer Yes Primary key – will be auto increment as well
uname varchar Yes Name of the person
uemail varchar Yes Unique Key – email address of the person
umobile varchar No Unique Key – mobile number of the person

Take a look below at the SQL syntax to create the table in MySQL.

CREATE TABLE IF NOT EXISTS `users`(
    `uid` INT(11) NOT NULL,
    `uname` VARCHAR(255) NOT NULL,
    `uemail` VARCHAR(100) NOT NULL,
    `umobile` VARCHAR(10) NULL,
    PRIMARY KEY(`uid`),
    UNIQUE(`uemail`),
    UNIQUE(`umobile`)
) ENGINE = INNODB DEFAULT CHARSET = utf8 AUTO_INCREMENT = 1

Drop multiple unique keys in a table

If you want to drop one or more unique key, use the SQL given below.

ALTER TABLE `users` DROP INDEX `uemail`, DROP INDEX `umobile`

Add a unique key on multiple columns in a table

In this example, we will create a table named “products“. Both name and model column combines a unique key. For instance, there cannot be two Iphone 11 in the table.

Take a look at the fields of the table.

Column Type Required Comments
prd_id integer Yes Primary key – will be auto increment as well
prd_name varchar Yes Unique Key – Name of the product
prd_model varchar Yes Unique Key – Model number of the product
prd_price double Yes Price of the product
prd_stock integer Yes Stock of the product

We will be clubbing both prd_name and prd_model with an index named “myuniquekey“. Take a look below at the SQL syntax to create the table in MySQL.

CREATE TABLE IF NOT EXISTS `product` (
  `prd_id` int(11) NOT NULL AUTO_INCREMENT,
  `prd_name` varchar(255) NOT NULL,
  `prd_model` varchar(255) NOT NULL,
  `prd_price` DOUBLE NOT NULL,
  `prd_stock` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`prd_id`),
  UNIQUE `myuniquekey` (`prd_name`, `prd_model`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Drop unique key on multiple columns in a table

To drop the unique key, we need to drop the index “myuniquekey“. See the SQL below

ALTER TABLE `product` DROP INDEX `myuniquekey`;

Get the latest updates directly in your inbox for FREE: Subscribe here

Share your feedback / let me know your doubts regarding this tutorial in the comment box given below.

Share:
Hasan

Shahrukh Khan (Hasan)

A software engineer who's a die-hard coder, blogger, dreamer and mentor with years of expertise in web development. Know more...

Related Posts

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.