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.

Index

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 🙂