What is Unique Constraint in MySQL | How to Create Unique Constraint in MySQL - MySQL Developer Tutorial

What is Unique Constraint in MySQL | How to Create Unique Constraint in MySQL

Unique Constraint enforces the uniqueness of value in a column or columns.

Let's say if we want to create the unique constraint on first name column in customer table, we can use below statement. In below example, we are creating Unique Constraint on single column.

CREATE TABLE `customer` (
  `idcustomer` int(11) NOT NULL,
  `firstname` varchar(50)  NULL,
  `lastname` varchar(30)  NULL,
  `age` int(11) DEFAULT NULL,
  `phonenumber` char(11) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` char(1) NOT NULL,
  Constraint uq_firstname Unique (firstname)
) ;

To create Unique Constraint on multiple columns, you can use below example. In below example, we are creating Unique Constraint on first name and last name columns.

CREATE TABLE `customer` (
  `idcustomer` int(11) NOT NULL,
  `firstname` varchar(50)  NULL,
  `lastname` varchar(30)  NULL,
  `age` int(11) DEFAULT NULL,
  `phonenumber` char(11) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` char(1) NOT NULL,
  Constraint uq_multicolumn Unique (firstname,lastname)

) ;

Create Unique Constraint on existing table :

Let's say if the table already exists and we need to create the Unique Constraint, then we can use below example. In below example, we are going to create Unique Constraint on Age column. Make sure there is no duplicate values in Age column otherwise you will not be able to create Unique Constraint.

Alter Table Customer

Add Constraint uq_age Unique (age);

Null Values in Unique Constraint : 

MySQL let you insert multiple Null values in column even Unique Constraint is created. In Microsoft SQL Server if you have created the Unique Constraint, you will be only able to insert single Null value but in MySQL you can insert multiple Null values in column on which we have Unique Constraint.

Check the detail demo "What is Unique Constraint in MySQL and How to create Unique Constraint in MySQL"