How to Add Drop Unique Key in MySQL

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

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 you create a table person and declare Email as a unique key but later you want Phone must be unique too, in that case, you 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`);

Drop Unique Key in MySQL

By mistake, you made Phone column as a unique key but now you have to drop the unique key so remember that you don’t have to drop the column, you have to drop the unique key.

ALTER TABLE person DROP CONSTRAINT Phone;

Add Unique Constraint on multiple columns

You can also make multiple columns unique. You have to name the unique key, here I named it as 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 you can alter student table to add multiple unique keys after you 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`);

Drop Unique Constraint on multiple columns

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

ALTER TABLE person DROP CONSTRAINT UC_student;

Leave a Reply

Your email address will not be published. Required fields are marked *