How to Create Unique Index for Existing table in MySQL which contains Records

Question

Here i like to explain my problem,

I Need to Create Unique Index in my Existing table, and the table contains many records.

I tried to execute this code

CREATE UNIQUE INDEX empid_name ON employee (importcompany_id, employee_id, name, relationship);

but am getting error as

#1062 - Duplicata du champ '0-Emp ID-Member Name-Relationship' pour la clef 'empid_name' 

Help me to sort out this problem, i need to make fields unique

Updated :

The reason for setting these fields unique is

Actually i have a table like this

id  company_ID  Employee_ID Name        Relationship    Dob     Age Gender       
1   EMPL        00001       Choodamani  Spouse      11-Aug-66   49  Female            
2   EMPL        00001       Komala      Mother      30-Oct-39   76  Female            
3   EMPL        00001       Varshini    Daughter    29-Apr-04   11  Female            
4   EMPL        00001       Vasudevan   Employee    15-Jul-62   53  Male    
5   EMPL        00002       Siddharth   Son         1-Jun-00    15  Male              
6   EMPL        00002       Poongavanam Mother      21-Oct-39   76  Female            
7   EMPL        00002       Aruna       Spouse      16-Sep-68   47  Female            
8   EMPL        00002       Abirami     Daughter    7-May-97    18  Female            
9   EMPL        00002       Murali      Employee    7-Oct-67    48  Male

if have insert a data like this,

    id  company_ID  Employee_ID Name        Relationship    Dob     Age Gender       
    1   EMPL        00001       Choodamani  Spouse      11-Aug-70   45  Female            
    2   EMPL        00001       Nirmal      Son      30-Oct-39   76  Female

this insert or update is done through import using excel sheet

Miscalculate

You have to clean up your data to get a table without duplicate entries. After that you'll have the possibility to add the UNIQUE INDEX. – Tope

is there any way to do it by without removing data???? – Miscalculate

I don't understand it. One possiblity is to have unique datasets and the other possibility is to allow duplicate entries in your table. If you want to add UNIQUE INDEX constaint, you mustn't have duplicate entries in your table. Please read dev.mysql.com/doc/refman/5.0/en/constraint-primary-key.html – Tope

If you include the 'dob' (date of birth) then you have a more unique representation. Though it still does not guarantee complete uniqueness. – Great

Answer 1

1. If you want to have unique index empid_name ON employee table with columns (importcompany_id, employee_id, name, relationship). Then you must delete existing duplicate data.

Easy way to do this is to add a UNIQUE index on the 4 columns. When you write the ALTER statement, include the IGNORE keyword. Like so:

ALTER IGNORE TABLE `employee` ADD UNIQUE INDEX(importcompany_id, employee_id, name, relationship);

This will drop all the duplicate rows. As an added benefit, future INSERTs that are duplicates will error out. As always, you may want to take a backup before running something like this.

2. Or Add primary key in your table then you can easily remove duplicates from your table. And then add unique index.

Thetisa

FYI, ALTER IGNORE was deprecated. You would have to first remove dups, then set unique index, then you can ALTER without the ignore statement – Tubate

Answer 2

Use alter table for this

 ALTER TABLE `employee` ADD UNIQUE INDEX(importcompany_id, employee_id, name, relationship);

See this for more reference.

Warfield

by using your updated answer, am getting this error #1062 - Duplicata du champ '0-Emp ID-Member Name-Relationship' pour la clef 'importcompany_id_3' – Miscalculate

Answer 3

The simplest solution is to add a new column called something like UniqueID

If you don't need it for any other reason, you could simply set it up to AutoIncrement (AI): it will be meaningless, but at least it will be unique

You then change your indexing so that the UniqueID column is the unique/primary key. If you want to maintain an index on employee you can do so, but if you have more than one record with that same value in that column it will throw an error if you specify it as unique.

Logroll

please see above i have updated my question..thats is the reason to do this – Miscalculate

If I have understood correctly, your import would change the DOB (and obviously age) for Choodamani, and try to insert record for Nirmal. (Not sure how Nirmal is female son aged 76, but that's another problem!!) – Logroll

So your import must NOT use the unique ID ... rather do select on the relevant fields, identify if it is an INSERT or an UPDATE and allow the table to work out the correct ID (that's what AutoIncrement does) – Logroll

Your code should identify for the first import that there is an existing record with same EmployeeID, Name and Relationship -> update. For the second it would identify that a new record is required -> insert. Alternatively, include existing unique ID for a known record in your import, and do insert if id column in import is blank, otherwise update – Logroll