How To

How to Add Drop Unique Key in MySQL

A unique key is a key that can be a column or a group of a column that defines the uniqueness of that record/row in the table. A unique key is different from the Primary key. The unique key can be a NULL value, whereas the Primary key cannot be a NULL value. 

Add and Drop Unique Key in MySQL

Adding or dropping a unique key helps us to manage relations between multiple tables. Also, It helps to manage redundancy in MySQL databases.

How to Add Unique Key in MySQL

Let’s take an example, create a table name person. Here we are storing the data of a person’s details on a website. There are four columns, i.e., id, which is a primary key, Name, Email, Phone. Email and Phone both must be unique.

CREATE TABLE person (
    id int(255),
    Name varchar(255),
    Email varchar(255),
    Phone int(10),
    PRIMARY KEY (id),
    UNIQUE (Email),
    UNIQUE (Phone)
)

Suppose we create a table person and declare Email as a unique key, but later we want Phone must be unique too; in that case, we have to alter the Phone field and make that column unique.

CREATE TABLE person (
    id int(255),
    Name varchar(255),
    Email varchar(255),
    Phone int(10),
    PRIMARY KEY (id),
    UNIQUE (Email),
)
ALTER TABLE `person` ADD UNIQUE  (`Phone`);

How to Drop Unique Key in MySQL

We made the Phone column a unique key by mistake, but now we have to drop the unique key, so remember that we don’t have to drop the column; we have to drop the unique key.

ALTER TABLE person DROP CONSTRAINT Phone;

How to Add Unique Constraint on multiple columns

We can also make multiple columns unique. We have to name the unique key; here, I named it UC_student.

CREATE TABLE student (
    id int(255),
    Name varchar(255),
    Email varchar(255),
    Phone int(10),
    PRIMARY KEY (id),
    CONSTRAINT UC_student UNIQUE (id,Name)
    )

As per before, we can alter the student table to add multiple unique keys after we created the table.

CREATE TABLE student (
    id int(255),
    Name varchar(255),
    Email varchar(255),
    Phone int(10),
    PRIMARY KEY (id),
    CONSTRAINT UC_student UNIQUE (id,Name)
    )
ALTER TABLE `student` ADD UNIQUE  (`Phone`);

How to Drop Unique Constraint on multiple columns

Drop the unique key constraint which we have set in the earlier example is easy.

ALTER TABLE person DROP CONSTRAINT UC_student;

Read Also: How to Add Unlimited Fields in Form Using JavaScript and Store Into Database With PHP

Conclusion

In this article, we have learned how to add and drop a unique key in MySQL. I hope you can add or remove a unique key in your MySQL table.

Keep Coding 🙂

Nachiket Panchal

Founder & Administrator of `errorsea` Having interest in Programming & Technology.

Recent Posts

5 Important Things To Know About WordPress Before You Use It

There is a reason big-name companies like CNN use WordPress. WordPress is a popular content…

3 years ago

How to Install MySQL on Your PC in 3 Easy Steps

In this tutorial, I'm going to show you how to install MySQL on your computer.…

5 years ago

Download and Install Turbo C++ for Windows 10 (Full Installation Guide)

Download Turbo C++ for windows 10 in just 7 Mb and run your first C++…

5 years ago

PHP .HTACCESS Redirects

We can redirect any webpage to any other or redirect the whole domain or website…

5 years ago

PHP Redirect Pages

There are lots of methods to redirect pages, like refresh-redirect from META tag, redirect from…

5 years ago

PHP Include & Required

Include files in PHP are used in appending various global or config files. We can…

5 years ago