EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial MySQL UNIQUE INDEX

MySQL UNIQUE INDEX

Priya Pedamkar
Article byPriya Pedamkar

Updated May 16, 2023

MySQL UNIQUE INDEX

Introduction to MySQL UNIQUE INDEX

UNIQUE INDEX constraints are used in MySQL to maintain the uniqueness of values in the database. MySQL has the ‘PRIMARY KEY’ constraint to define unique columns in a table. But it can specify only one column per table. So, when multiple columns are expected to have distinct values throughout the data, UNIQUE INDEX is used. Also, the Primary key constraint by default holds the ‘NOT NULL’ condition, whereas UNIQUE INDEX can also accommodate null values. Unique INDEX is not just a special index that helps to maintain data integrity and safety, but it also improves data retrieval performance.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Syntax:

The “CREATE UNIQUE INDEX ()” statement defines UNIQUE INDEX()”. Thus the statement will create a column mentioned within the () as a unique column, allowing no duplicates.

The syntax can be as below:

CREATE UNIQUE INDEX index_name
ON table_name (index_column1, index_column2….);

The above is to define a unique index explicitly. But we can define the columns as a UNIQUE INDEX while creating a table.

CREATE TABLE table_name(
column1 data_type,
….
UNIQUEINDEX (index_column1, index_column2 ….)
);

How does UNIQUE INDEX work in MySQL?

So let us create a table with a PRIMARY KEY and a UNIQUE INDEX. Let us consider a table for storing details of employees with the table name EMP_DETAILS. The table will have employee id, first name, second name, country, position, and email id as column names. We will keep ‘id’ as the PRIMARY KEY and define position and email as UNIQUE INDEX.

Code:

CREATE TABLE EMP_DETAILS (
id int,
first_name varchar(50),
secnd_name varchar(50),
country varchar(50),
position varchar(50),
email varchar(90),
PRIMARY KEY (id),
UNIQUE INDEX ind (position, email));

Output:

The table has the id as PRIMARY KEY; hence, it is by default NOT NULL. The columns position and email are marked UNIQUE, but since both were not defined as NOT NULL in the query, they can hold null values but not duplicates.

MySQL UNIQUE index 1

Example #1

We will insert values into this table and check whether the unique columns will accept duplicate values.

Code:

INSERT INTO EMP_DETAILS VALUES ('1','Alex','Potter','Belgium','Manager','[email protected]');

Output:

MySQL UNIQUE INDEX 2

So we have inserted values to one row of the table EMP_DETAILS. With our previous query, we have defined the field position and email as unique indexes. So these fields will not accept duplicate values.

Example #2

Let’s try inserting the same values into a second row of this table.

Code:

INSERT INTO EMP_DETAILS VALUES ('2','Alex','Potter','Belgium','Manager','[email protected]');

Output:

We get the below error message as a result:

MySQL UNIQUE INDEX 3

The first UNIQUE INDEX defined was field position. That is why the error message was displaying about the field position.

Example #3

Here let us see for the email field as well.

Code:

INSERT INTO EMP_DETAILS VALUES ('2','Alex','Potter','Belgium','Accountant','[email protected]');

Output:

We get the below error message as a result:

MySQL UNIQUE INDEX 4

When the unique constraint is created at the unique backend index is defined automatically.

The UNIQUE INDEX defined can also be dropped if it seems to be unnecessary at a later point in time.

Example #4

Here let us consider another table for adding or removal of UNIQUE INDEX.

Code:

CREATE TABLE student_details (
id int,
first_name varchar(50),
secnd_name varchar(50),
class varchar(50),
subject1 varchar(50),
email varchar(90));

Output:

adding or removal

So we have just created a table named student_details. We have not defined any primary_keys or Unique_Indexes.

Example #5

Here let’s define the UNIQUE INDEX in the table below.

Code:

CREATE UNIQUE INDEX ind ON student_details(id, email);

Output:

So we created a unique index:

MySQL UNIQUE INDEX 6

Example #6

Here let’s see the index in the table student_details.

Code:

SHOW INDEX FROM student_details;

Output:

in the table student_details

Example #7

Let’s remove the unique index added by the previous query by using the DROP INDEX command.

Code:

ALTER TABLE student_details DROP INDEX ind;

Output:

by using DROPINDEX command

Example #8

Now, let’s try retrieving the indexes in the table student_details.

Code:

SHOW INDEX FROM student_details;

Output:

MySQL UNIQUE INDEX 9

So the unique_indexes inserted to the table student_details are now removed, which confirms that all columns will accept any number of duplicate values. Thus we have checked in detail creating or defining a UNIQUE INDEX, how to insert values into the index, and how to remove the unique index from a table.

Conclusion

Here we have seen the UNIQUE INDEX. In MySQL, UNIQUE INDEX is used to define multiple non-duplicate columns at once. While PRIMARY KEY constraint also assures non-duplicate values in a column, only one PRIMARY KEY can be defined per table. So for scenarios where multiple columns are to be made distinct, UNIQUE INDEX is used.

Recommended Articles

We hope that this EDUCBA information on “MySQL UNIQUE INDEX” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. Conditions in MySQL
  2. Wildcards in MySQL
  3. Unique Key in MySQL
  4. MySQL Trigger
C++ PROGRAMMING Course Bundle - 9 Courses in 1 | 5 Mock Tests
37+ Hour of HD Videos
9 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ASP.NET Course Bundle - 28 Courses in 1 | 5 Mock Tests
123+ Hours of HD Videos
28 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
SQL Course Bundle - 51 Courses in 1 | 6 Mock Tests
204+ Hours of HD Videos
51 Courses
6 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
SOFTWARE TESTING Course Bundle - 13 Courses in 1
53+ Hour of HD Videos
13 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Popular Course in this category
MYSQL Course Bundle - 18 Courses in 1 | 3 Mock Tests
 93+ Hour of HD Videos
18 Courses
3 Mock Tests & Quizzes
  Verifiable Certificate of Completion
  Lifetime Access
4.5
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more