CODEWITHSUNDEEP
mysqlphpmyadminunique-constraint
StackOverflowNewbie
StackOverflowNewbie

Reputation: 40633

MySQL: making a column unique?

I have a table that is in production. I realize that some of the columns should be unique. Is it safe to go into phpMyAdmin and change those columns to make it unique?

ALTER TABLE  `foo` ADD UNIQUE ( `bar` )

Upvotes: 30

Views: 87104

Answers (5)

Rahul Mankar
Rahul Mankar

Reputation: 990

Follow the below steps to apply unique column value from phpmyadmin panel:

Go to the table structure. Click on the unique keyword as like below -

enter image description here

Click on the ok from confirmation box -

enter image description here

Unique value constraint for column will apply. Or you can run mysql query:

ALTER TABLE user ADD UNIQUE(email);

Upvotes: 36

Eoin
Eoin

Reputation: 1515

I had this problem and my values were not unique. I also couldn't find an easy way to edit this problem in PHPMyAdmin. Here's how I solved it:

  1. I clicked into the table I needed to update

  2. I exported the table, changing it to be a CSV export and then edited it manually to update the non-unique values.

  3. Making sure I was still in the table I had exported (because I wanted to keep the headers intact), I imported my newly saved CSV

Hope that saves someone some time in the future.

Upvotes: 1

Itay Moav -Malimovka
Itay Moav -Malimovka

Reputation: 53597

  1. You do not have duplicates -> will apply the key without issues
  2. You do have duplicates -> will give an error message, nothing happened to your data
  3. All is unique, except several rows with NULL in them, unique constraint is still applied, as NULL is not checked when checking for unique values (you can have the entire table have a NULL value in a unique field without any error message).

One more thing, if you have a prod DB, you must also have a dev DB which you can test on without fear, right?

Upvotes: 17

MD Sayem Ahmed
MD Sayem Ahmed

Reputation: 29166

If there are already some duplicate values in those columns, then this will generate an error. If there aren't any duplicate values in those columns, then you will be fine.

Upvotes: 2

Oscar Gomez
Oscar Gomez

Reputation: 18488

It will only be a problem if the pre-existing values on the table are not unique, otherwise I don't think there will be any problem.

Upvotes: 1

Related Questions