Back to: MySQL Tutorials for Beginners and Professionals
UNIQUE Constraint in MySQL with Examples
In this article, I am going to discuss UNIQUE Constraint in MySQL with Examples. Please read our previous article where we discussed NOT NULL Constraint in MySQL.
MySQL UNIQUE Constraint
When you want a column or columns not to accept any duplicate values, then you need to apply UNIQUE Constraint for that column or columns in MySQL. That means the UNIQUE Constraint is useful to restrict storing of duplicate data row values in a given column or combination of columns. But it accepts NULL values in that column.
Multiple columns in a single table can have the UNIQUE Constraint. We can apply the UNIQUE constraint on any data type column such as INT, VARCHAR, etc.
Example to understand MySQL UNIQUE Constraint:
Let us understand the Unique Constraint in MySQL with some examples. In order to understand MySQL Unique Constraint, first, create the EmployeeDB and Employee table by executing the following SQL Script. As you can see, here, we applied the UNIQUE constraint to Id, and Email columns. It means these two columns will not accept the duplicate value.
Create database EmployeeDB; Use EmployeeDB; CREATE TABLE Employee ( Id INT UNIQUE, Name VARCHAR(50) NOT NULL, Email VARCHAR(50) UNIQUE, Department VARCHAR(50) NOT NULL );
Once you execute the above query, let us verify the Employee table structure by executing the below SQL Statement.
DESCRIBE Employee;
Once you execute the above SQL Statement, it will give you the below output.
As we have applied the UNIQUE constraint to the Id and Email column, so here you can see the key column of these two fields have UNI value which indicates these two columns are the UNIQUE column.
Examples:
Now try to execute the below SQL Insert statements.
INSERT INTO Employee (Id, Name, Email, Department) VALUES (1, ‘Anurag’, ‘Anurag@dotnettutorials.net’, ‘IT’);
When we execute the above INSERT statement, it executed as expected. Now, try to execute the below SQL Statement.
INSERT INTO Employee (Id, Name, Email, Department) VALUES (1, ‘Sambit’, ‘Sambit@dotnettutorials.net’, ‘IT’);
When we try to execute the above INSERT SQL statement, we will get the following error. This is because we are trying to insert duplicate Id values into the Id column.
Error Code: 1062. Duplicate entry ‘1’ for key ’employee.Id’
Now, try to execute the below SQL Statement.
INSERT INTO Employee (Id, Name, Email, Department) VALUES (2, ‘Sambit’, ‘Anurag@dotnettutorials.net’, ‘IT’);
Now, when you try to execute the above INSERT SQL statement, you should get the following error. This is because now we are trying to insert duplicate Email values into the Email column.
Error Code: 1062. Duplicate entry ‘Anurag@dotnettutorials.net’ for key ’employee.Email’
When we try to insert a duplicate value (other than null) into a column on which the UNIQUE constraint is applied, then the SQL statement gets terminated by displaying an error message showing the table and column name. Now, execute the below INSERT Statement.
INSERT INTO Employee (Id, Name, Email, Department) VALUES (NULL, ‘Sambit’, NULL, ‘IT’);
The above SQL statement will be executed successfully. This is because the UNIQUE Constraint allows null value.
How to add UNIQUE Constraints to existing Columns in MySQL?
It is also possible to add UNIQUE Constraints to existing columns in MySQL. Let us understand this with an example. First, delete all the data from the Employee table by executing the below TRUNCATE statement.
TRUNCATE Table Employee;
In our Employee table, suppose we want to add UNIQUE Constraint on the Name column. Then we can do the same by executing the below SQL Statement.
ALTER TABLE Employee ADD UNIQUE (Name);
Once you execute the above query, it will create a Unique Constraint on the Name column. You can verify the same by exploring the Indexes folder as shown in the below image. As you can see, currently the Employee table contains three indexes i.e. three UNIQUE Constraints. In our upcoming article, we will discuss Indexes in detail.
How to DELETE UNIQUE Constraints in MySQL?
You can use the following SQL Statement to delete an existing UNIQUE Constraint. The following SQL Statement will delete the UNIQUE Constraint from the Name column.
ALTER TABLE Employee DROP INDEX Name;
Once you execute the above SQL query, it will delete the UNIQUE Constraint from the Name column. You can verify the same again by exploring the Indexes folder as shown in the below image.
As we already discussed except the NOT NULL constraint, all other constraints can be imposed either at the Table or Column level. Let us understand the same concept with UNIQUE Constraint.
Imposing UNIQUE Constraint at Column Level:
Let us understand how to Impose a Unique Constraint at column level with an example. As you can see in the below Create Table statement, we have specified UNIQUE Constraints directly at the column level for the Id and Email Columns.
CREATE TABLE Student ( Id INT UNIQUE, Name VARCHAR(50), Email VARCHAR(10) UNIQUE );
Imposing UNIQUE Constraint at Table Level:
Let us understand how to impose MySQL UNIQUE constraint at table level with an example. As you can see in the below Create Table statement, first, we created all the three columns and then we created UNIQUE constraints and while creating UNIQUE constraint we specified the column name to which the constraint belongs.
CREATE TABLE StudentAddress ( Id INT, Name VARCHAR(50), Email VARCHAR(10), UNIQUE(Name), UNIQUE(Email) );
Note: There is no difference in behavior whether we imposed the UNIQUE Constraint at the table level or at the column level but if we constraint the imposed at the table level, then we have the chance to impose the composite UNIQUE constraints.
Creating UNIQUE Constraint with Custom name in MySQL:
Let us understand how to provide a custom name to the constraint. In order to understand this, please execute the below SQL Script which will create the StudentDetails table. If you further notice, here we created two unique constraints by giving our own names such as ID_ Unique and Email_Unique.
CREATE TABLE StudentDetails ( Id INT, NAME VARCHAR(30), Email VARCHAR(100), CONSTRAINT ID_ Unique UNIQUE (Id), CONSTRAINT Email_Unique UNIQUE(Email) );
Composite UNIQUE Constraint in MySQL:
It is also possible in MySQL to apply a single UNIQUE Constraint on multiple columns. In this case, duplicate values are allowed on a single column, but the combination of duplicate values is not allowed. If this is not clear at the moment, then don’t worry we will try to understand the same with some examples. Please execute the below SQL Script to create the Customer table.
CREATE TABLE Customer ( Id INT NOT NULL, Name VARCHAR(30), Email VARCHAR(50), UNIQUE KEY Unique_Name_Email (Name, Email) );
Here, we have created one UNIQUE Constraint with the name Unique_Name_Email which is applied to the Name and Email Column of the Customer table. Now, execute the below INSERT SQL Statements.
INSERT INTO Customer (Id, Name, Email) VALUES (1, 'Sambit', 'Sambit@dotnettutorials.net'); INSERT INTO Customer (Id, Name, Email) VALUES (2, 'Sambit', 'Sambit1@dotnettutorials.net'); INSERT INTO Customer (Id, Name, Email) VALUES (3, 'Sambit1', 'Sambit1@dotnettutorials.net');
Once you execute the above SQL Query, three records are inserted into the Customer table. Here, if you notice. In the second and third SQL Statements, we have some duplicate values. In Statement2, the Name is duplicate and in Statement3, the Email is duplicate. But all these records are inserted, this is because the combination of Name and Email is not duplicated. Now, try to execute the below SQL Statement.
INSERT INTO Customer (Id, Name, Email) VALUES (4, ‘Sambit’, ‘Sambit@dotnettutorials.net’);
When you try to execute the above SQL Query, you will get the following error. This is because the combination of the above Name and Email is already existing in the database.
Error Code: 1062. Duplicate entry ‘Sambit-Sambit@dotnettutorials.net’ for key ‘customer.Unique_Name_Email’
In the next article, I am going to discuss the CHECK Constraint in MySQL with Examples. Here, in this article, I try to explain UNIQUE Constraint in MySQL with Examples and I hope you enjoy this UNIQUE Constraint in MySQL with Examples article.