Some issues during changing index uniqueness

Some times we need to create a new unique index or alter existing one, but table may already contain duplicate data for this index and our changes could not be applied until data in table fixed to support uniqueness. We could do it by deleting records in the table but due to huge number of companies in environment it could be time consuming. To save your time you could use following job:

static void Job3(Args _args)
{
    MyTable myTable;
    DataArea da;
    int cnt;
    ;
    ttsbegin;
    while select da
    {
        cnt = 1;
        changecompany(da.Id)
        {
            myTable = null;
            while select forupdate myTable
            {
                cnt++;
                myTable.FieldForUniqueness = int2str( cnt );
                myTable.update();
            }
        }
    }
    ttscommit;
}

Another way to solve this problem is to make following steps:

  1. Try to synchronize your table
  2. You get an error saying that there are duplicates
  3. Rright away you go to Administration > Periodic > SQL Administration form.
  4. Select your table in the list and run Table actions > Check/Synchronize command.
  5. In appeared form enable “Check Allow duplicated’” and run.
  6. You will get a list of conflicting records to kill.