Solved

mysql alter table to make a column unique

Posted on 2011-09-14
3
486 Views
Last Modified: 2012-05-12
Hi All,

How do I set a column in MySQL as unique?

There maybe duplicates, so they can be deleted.
0
Comment
Question by:detox1978
  • 2
3 Comments
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 total points
Comment Utility
I have never tried to alter a table and add a UNIQUE index to a column that has duplicates.  That would probably fail (I hope).  I think you might want to create a new table with UNIQUE on the column, then INSERT into the new table with a SELECT from the old table, processing the rows one at a time.

MySQL will throw error number #1062 if you attempt to insert duplicate data into a UNIQUE column.  You can trap this error and ignore it if you want to discard the duplicate values.
0
 
LVL 2

Author Comment

by:detox1978
Comment Utility
thanks for the info.  created a new table, inserted the data, dropped the original and renamed the new.

0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
Bingo!  Thanks for the points, ~Ray
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
These days socially coordinated efforts have turned into a critical requirement for enterprises.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to dynamically set the form action using jQuery.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now