非唯一索引可以避免记录重复 [英] can non-unique index avoids the duplication of records

问题描述

我有一个无法创建主键和唯一索引的表，原因是该表包含员工的薪水数据，员工代码是唯一的，但是由于薪水代码不同并且每个薪水代码与单个员工相关联，这就是我们允许重复记录的原因.

现在的问题是，薪水数据被第三方供应商推送到共享数据库中，而当我们将薪水数据迁移到我们的数据库中时，就发生了数据记录被插入两次或三次的情况.

例如，假设第三方推送了5000条记录，现在我们需要将这5000条记录提取到我们的数据库中，但是上个月发生了3到4次数据提取，它变成了20,000条记录而不是5000条记录，原因很简单我们的表中没有任何验证.

现在建议我创建唯一索引以避免重复，但是由于重复了员工代码，所以我无法做到这一点.

现在我们只剩下非唯一索引了，我听不懂，这对避免重复真的很有帮助.

我的主要目的是避免重复. 请建议我更好的解决方案.

这是一些数据

   SALARY_REPORT_ID EMP_NAME    EMP_CODE    PAY_CODE    PAY_CODE_NAME   AMOUNT  PAY_MODE    PAY_CODE_DESC   YYYYMM  REMARK  EMP_ID  PRAN_NUMBER PF_NUMBER   PRAN_NO ATTOFF_EMPCODE  REFERENCE_ID
    13488158    Mr. Javed Jafri 91559037    104     7427    1   HOUSE RENT ALLOW    201802      119     22782303        150025  1-268
    13488159    Mr. Javed Jafri 91559037    100     23885   3   BASIC PAY   201802      119     22782303        150025  1-268
    13488160    Mr. Javed Jafri 91559037    103     9590    1   DEARNESS ALLOW. 201802      119     22782303        150025  1-268
    13488161    Mr. Javed Jafri 91559037    100     191800  1   BASIC PAY   201802      119     22782303        150025  1-268
    13488162    Mr. Javed Jafri 91559037    303     40000   2   PF SUB-PAY(GPF) 201802      119     22782303        150025  1-268
    13488163    Mr. Javed Jafri 91559037    502     20  2   G.T.I.S.    201802      119     22782303        150025  1-268
    13488164    Mr. Javed Jafri 91559037    503     72  2   SCLIS   201802      119     22782303        150025  1-268
    13488165    Mr. Javed Jafri 91559037    999     69441   1   NET EARNING 201802      119     22782303        150025  1-268
    13488166    Mr. Javed Jafri 91559037    998     195692  2   GROSS DEDUCTION 201802      119     22782303        150025  1-268
    13488167    Mr. Javed Jafri 91559037    997     265133  1   GROSS EARNING   201802      119     22782303        150025  1-268
    13488168    Mr. Javed Jafri 91559037    134     16006   3   WAGE REVISION ARREARS   201802      119     22782303        150025  1-268
    13488169    Mr. Javed Jafri 91559037    108     400 1   CONVEYANCE ALLOWANCE    201802      119     22782303        150025  1-268
    13488170    Mr. Javed Jafri 91559037    134     16025   3   WAGE REVISION ARREARS   201802      119     22782303        150025  1-268
    13488171    Mr. Javed Jafri 91559037    506     600 2   GSLI(Board Employee)    201802      119     22782303        150025  1-268
    13488172    Mr. Javed Jafri 91559037    312     155000  2   INCOME TAX  201802      119     22782303        150025  1-268

更多缩进方式

解决方案

如果我正确理解了您的问题，那么您应该在(employee_id，pay_code)上创建多列唯一索引.您必须找到应该唯一的列的组合，并基于该列创建索引.

I have table in which I am not able to create primary key as well as unique indexing,the reason is, table contains employee's salary data,employee code is unique but because pay codes are different and each pay code associated to individual employee,that's why we are allowing the duplication of records.

Now the problem is,salary data is pushed by the third party vendor into the shared database and when we migrate the salary data into our database, then it happened that data records inserted twice or thrice times.

for example, suppose third party pushed 5000 records, now we need to fetch those 5000 records into our database, but it happened last month that data fetched 3 to 4 times, it becomes 20,000 records instead of 5000, the reaso was simple we don't have any validation in our table.

Now i was suggested to create unique index to avoid duplication, but because employee code is repeated, i am not able to do it.

Now we are left with non-unique index and I am not able to understand , is it really helpful in order to avoid duplication.

My main purpose is to avoid duplication. Please suggest me the better solution.

Here is some data

   SALARY_REPORT_ID EMP_NAME    EMP_CODE    PAY_CODE    PAY_CODE_NAME   AMOUNT  PAY_MODE    PAY_CODE_DESC   YYYYMM  REMARK  EMP_ID  PRAN_NUMBER PF_NUMBER   PRAN_NO ATTOFF_EMPCODE  REFERENCE_ID
    13488158    Mr. Javed Jafri 91559037    104     7427    1   HOUSE RENT ALLOW    201802      119     22782303        150025  1-268
    13488159    Mr. Javed Jafri 91559037    100     23885   3   BASIC PAY   201802      119     22782303        150025  1-268
    13488160    Mr. Javed Jafri 91559037    103     9590    1   DEARNESS ALLOW. 201802      119     22782303        150025  1-268
    13488161    Mr. Javed Jafri 91559037    100     191800  1   BASIC PAY   201802      119     22782303        150025  1-268
    13488162    Mr. Javed Jafri 91559037    303     40000   2   PF SUB-PAY(GPF) 201802      119     22782303        150025  1-268
    13488163    Mr. Javed Jafri 91559037    502     20  2   G.T.I.S.    201802      119     22782303        150025  1-268
    13488164    Mr. Javed Jafri 91559037    503     72  2   SCLIS   201802      119     22782303        150025  1-268
    13488165    Mr. Javed Jafri 91559037    999     69441   1   NET EARNING 201802      119     22782303        150025  1-268
    13488166    Mr. Javed Jafri 91559037    998     195692  2   GROSS DEDUCTION 201802      119     22782303        150025  1-268
    13488167    Mr. Javed Jafri 91559037    997     265133  1   GROSS EARNING   201802      119     22782303        150025  1-268
    13488168    Mr. Javed Jafri 91559037    134     16006   3   WAGE REVISION ARREARS   201802      119     22782303        150025  1-268
    13488169    Mr. Javed Jafri 91559037    108     400 1   CONVEYANCE ALLOWANCE    201802      119     22782303        150025  1-268
    13488170    Mr. Javed Jafri 91559037    134     16025   3   WAGE REVISION ARREARS   201802      119     22782303        150025  1-268
    13488171    Mr. Javed Jafri 91559037    506     600 2   GSLI(Board Employee)    201802      119     22782303        150025  1-268
    13488172    Mr. Javed Jafri 91559037    312     155000  2   INCOME TAX  201802      119     22782303        150025  1-268

More Indent way

解决方案

If I understood your question correctly, then you should create a multi-column unique index on (employee_id, pay_code). You must find a combination of columns that should be unique and create an index based on that.

这篇关于非唯一索引可以避免记录重复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！