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, );
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) );
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');
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');
When this query is executed then it will give us an error.
Now we will check the same for Name and Address.
Insert Into t2(Name, Phone, Address) values('Lizi','233888', 'Oak street');
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;
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;
ALTER TABLE table_name DROP INDEX index_name;
Here in table t2 we will drop the value constraint.
Drop Index value on t2;
Now when we run the Show index from t2; command then changes will appear.
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);
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.
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!