A unique key in MySQL is a single field or combination of fields that ensure all values going to store into the column will be unique. It means a column cannot stores duplicate values. For example, the email addresses and roll numbers of students in the “student_info” table or contact number of employees in the “Employee” table should be unique.
MySQL allows us to use more than one column with UNIQUE constraint in a table. It can accept a null value, but MySQL allowed only one null value per column. It ensures the integrity of the column or group of columns to store different values into a table.
Needs of Unique Key
- It is useful in preventing the two records from storing identical values into the column.
- It stores only distinct values that maintain the integrity and reliability of the database for accessing the information in an organized way.
- It also works with a foreign key in preserving the uniqueness of a table.
- It can contain null value into the table.
Syntax
The following syntax is used to create a unique key in MySQL.
If we want to create only one unique key column into a table, use the syntax as below:
CREATE TABLE table_name(
col1 datatype,
col2 datatype UNIQUE,
...
); If we want to create more than one unique key column into a table, use the syntax as below:
CREATE TABLE table_name(
col1 col_definition,
col2 col_definition,
...
[CONSTRAINT constraint_name]
UNIQUE(column_name(s))
); If we have not specified the name for a unique constraint, MySQL generates a name for this column automatically. So, it is recommended to use the constraint name while creating a table.
Parameter Explanation
The following table explains the parameters in detail.
| Parameter Name | Descriptions |
|---|---|
| table_name | It is the name of the table that we are going to create. |
| col1, col2 | It is the column names that contain in the table. |
| constraint_name | It is the name of the unique key. |
| column_name(s) | It is the column name(s) that is going to be a unique key. |
Unique Key Example
The following example explains how a unique key used in MySQL.
This statement creates a table “Student2” with a UNIQUE constraint:
CREATE TABLE Student2 (
Stud_ID int NOT NULL UNIQUE,
Name varchar(45),
Email varchar(45),
Age int,
City varchar(25)
); Next, execute the insert queries listed below to understand how it works:
mysql> INSERT INTO Student2 (Stud_ID, Name, Email, Age, City)
VALUES (1, 'Peter', '[email protected]', 22, 'Texas'),
(2, 'Suzi', '[email protected]', 24, 'California'),
(3, 'Joseph', '[email protected]', 23, 'Alaska');
mysql> INSERT INTO Student2 (Stud_ID, Name, Email, Age, City)
VALUES (1, 'Stephen', '[email protected]', 22, 'Texas'); Output
In the below output, we can see that the first INSERT query executes correctly, but the second statement fails and gives an error that says: Duplicate entry ‘1’ for key Stud_ID.
If you want to define the unique key on multiple columns, use the query as below:
CREATE TABLE Student3 (
Stud_ID int,
Roll_No int,
Name varchar(45) NOT NULL,
Email varchar(45),
Age int,
City varchar(25),
CONSTRAINT uc_rollno_email Unique(Roll_No, Email)
); In the output, we can see that the unique key value contains two columns that are Roll_No and Email.
To verify this, execute the following statement:
mysql> SHOW INDEX FROM Student3; Here, we can see that the unique constraint has successfully added into the table:
DROP Unique Key
The ALTER TABLE statement also allows us to drop the unique key from the table. The following syntax is used to drop the unique key:
ALTER TABLE table_name DROP INDEX constraint_name; In the above syntax, the table_name is the name of the table that we want to modify, and constraint_name is the name of the unique key we are going to remove.
Example
This statement will remove the uc_rollno_email constraint from the table permanently.
mysql> ALTER TABLE Student3 DROP INDEX uc_rollno_email; We can execute the SHOW INDEX statement to very this.
Unique Key Using ALTER TABLE Statement
This statement allows us to do the modification into the existing table. Sometimes we want to add a unique key to the column of an existing table; then, this statement is used to add the unique key for that column.
Syntax
Following are the syntax of the ALTER TABLE statement to add a unique key:
ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE(column_list); Example
This statement creates a table “Students3” that have no unique key column into the table definition.
CREATE TABLE Student3 (
Stud_ID int,
Roll_No int,
Name varchar(45) NOT NULL,
Email varchar(45),
Age int,
City varchar(25)
); After creating a table, if we want to add a unique key to this table, we need to execute the ALTER TABLE statement as below:
mysql> ALTER TABLE Student3 ADD CONSTRAINT uc_rollno_email UNIQUE(Roll_No, Email); We can see the output where both statements executed successfully.
To verify this, execute the following statement:
mysql> SHOW INDEX FROM Student3; Here, we can see that the unique constraint has successfully added into the table:
Leave a Reply