Add drop unique key in MySql


An unique key is a key that can be a column or a group of column which defines uniqueness for that record/row in the table. In this snippet you will learn how to add and drop unique key in Mysql. Unique key is different from primary key, unique key can be null whereas primary key cannot be null.

Add unique key in mysql

Let’s take a live example, let us create a table called “users” in which we will be storing the data of people who are registering on a website (we are just focusing on the sql part).It will have 4 columns i.e ID which is a primary key, Name, email and phone number. Both email and phone must be unique, but phone number can be null.

Suppose you create the table “users” assuming that only email will be unique but later on you think phone number must be unique too, so in that case you have to alter the phone number field and make that column unique, check the sql below.

Drop unique key in table

Let us assume that by mistake you have made the phone number (uphone) column as unique and now you want to drop that unique key. Please note that it will not drop the column, it will just drop the unique key .

Add unique constraint on multiple columns

You can also make multiple column as unique. Let’s take an example of an inventory system where a product can be unique by it’s name and brand name. For e.g. we cannot have two same records of iphone 6 of Apple brand in products table. Have a look at the sql below. You have to name the unique key, I have named it as myuniquekey

Just like in the previous case you can also alter the products table to add multiple column as unique key after you have created the table.

Drop unique constraint on multiple columns

Dropping the unique key which you have set in the earlier example is very easy.

If you have any good examples explaining about unique key do share with us.


About Author

Entrepreneur & Dreamer

I am a passionate Software Professional, love to learn and share my knowledge with others.

Software is the hardware of my life.

Leave A Reply

CommentLuv badge

Get more stuff like this
in your inbox

Subscribe to our mailing list and get interesting stuff and updates to your email inbox.