How to create a unique index for an existing table in MySQL that contains records

Question

Here I like to explain my problem,

I need to create a 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 i get an error like

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

Help me figure this out, I need to make unique fields

Updated:

The reason for setting these unique fields is

I actually 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 you insert such data,

  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 by import using an excel sheet

Answer 1

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

An easy way to do this is to add a UNIQUE index in 4 columns. When you write the ALTER statement, specify the IGNORE keyword . For example:

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

This will delete all duplicate rows. As an added benefit, future INSERTS that are duplicates will be erroneous. As always, you can take a backup before starting something like this.

2. Or Add a primary key to a table, then you can easily remove duplicates from your table. And then add a 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 details.

Answer 3

The simplest solution is to add a new column called UniqueID

If you don't need it for any other reason, you can just set it to AutoIncrement (AI): it will be pointless, but at least it will be unique

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