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

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.

Answer 2

Use alter table for this

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

See this for more reference.

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.