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
Adding or dropping a unique key helps us to manage relations between multiple tables. Also, It helps to manage redundancy in MySQL databases.
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`);
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;
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`);
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
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 🙂
There is a reason big-name companies like CNN use WordPress. WordPress is a popular content…
In this tutorial, I'm going to show you how to install MySQL on your computer.…
Download Turbo C++ for windows 10 in just 7 Mb and run your first C++…
We can redirect any webpage to any other or redirect the whole domain or website…
There are lots of methods to redirect pages, like refresh-redirect from META tag, redirect from…
Include files in PHP are used in appending various global or config files. We can…