MySQL Unique Constraint – Beginner’s Guide

In this tutorial, we will study Unique Constraints in MySQL. We will learn about the column and table level unique constraints with examples. Without further ado, let’s get started!

Introduction to MySQL Unique Constraint

Unique Constraints in MySQL are used to ensure that the data stored in a column or a combination of columns is unique and no two rows can have duplicate values in those columns. A Unique Key constraint uses a unique index to enforce the uniqueness of the values in a set of columns.

In MySQL, a Unique Constraint is used to ensure the values in a column or group of columns are unique. The syntax for defining a Unique constraint for a column is-

CREATE TABLE table_name(
    column_name data_type UNIQUE
);Code language: SQL (Structured Query Language) (sql)

To define Unique Constraint for two or more columns, use this-

CREATE TABLE table_name(
   column_name1 column_definition,
   column_name2 column_definition,
   UNIQUE(column_name1,column_name2)
);Code language: SQL (Structured Query Language) (sql)

Example of MySQL Unique Constraint

First of all, we will create a table named t2 and insert values into it. In the table, the first unique constraint is Phone column and the second unique constraint is both Name and Address columns. The code for the following is-

Create table t2 (
Id Int Auto_Increment,
Name varchar(300) Not Null,
Phone varchar(20) Not Null UNIQUE,
Address varchar(300) Not Null,
PRIMARY KEY (Id),
CONSTRAINT value UNIQUE(Name,Address)
);
Insert Into t2(Name, Phone, Address)
Values('Lizi', '247585', 'Oak street');Code language: SQL (Structured Query Language) (sql)

Now let us insert another value that has the same phone number as the above value.

Insert Into t2(Name, Phone, Address)
Values('Maze', '247585', 'Bay land');Code language: SQL (Structured Query Language) (sql)

When this query is executed then it will give us an error.

Output-

Now we will check the same for Name and Address.

Insert Into t2(Name, Phone, Address)
values('Lizi','233888', 'Oak street');Code language: SQL (Structured Query Language) (sql)

Output-

Unique Constraints and Indexes

Here we will see the indexes which are unique in the table named t2. The code is-

Show Indexes from t2;Code language: SQL (Structured Query Language) (sql)

Output-

Drop a Unique Constraint

To drop a unique constraint in a table, drop index or alter table statement can be used. The syntax for the following is-

DROP INDEX index_name ON table_name;Code language: SQL (Structured Query Language) (sql)

or

ALTER TABLE table_name
DROP INDEX index_name;Code language: SQL (Structured Query Language) (sql)

Here in table t2 we will drop the value constraint.

Drop Index value on t2;Code language: SQL (Structured Query Language) (sql)

Now when we run the Show index from t2; command then changes will appear.

Output-

Adding a new Constraint

To add a new constraint in an existing table then use ALTER TABLE ADD CONSTRAINT command. The syntax is-

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
UNIQUE (column_list);Code language: SQL (Structured Query Language) (sql)

Now we will add value as the unique constraint back to the table t2.

ALTER TABLE t2
ADD Constraint value 
UNIQUE (Name, Address);

Now run the Show Index command on table t2.

Output-

Conclusion

In this tutorial, we studied Unique Constraints in MySQL. We learned about the column and table level unique constraints with examples. I hope this tutorial was helpful to you. Thanks for reading!