Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics AX forum
Answered

Unique index violation problem

Posted on by Microsoft Employee

Hi,

I created a new field ("ID") in the mainaccount table and filled it with values that are unique. Thereafter, I created a new index and used my new "ID" field as a replacement index by specifying "allow duplicates: NO" and "alternate Key: YES".

My third step was to define my new index as the replacement key in the main account table.

Once the table gets compiled, the synchronization window pops up and comes up with a warning "new unique index ..." may cause unique index violations.

If I ignore this warning then I get an infolog with the message "synchronization failed" because of an index problem

Does anybody know how to fix my problem?

My aim is basically to create a new replacement key based on a new field that I defined.

Ludwig

  • Artezys Profile Picture
    Artezys 45 on at
    RE: Unique index violation problem

    Hi Ludwig,

    I bumped into the same issue, whilst upgrading to the latest AX 2012 update. Giving that my knowledge of dax is very limited, I wonder if you could share with me a solution, step by step.

    Appreciate in advanced.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Unique index violation problem

    First add unique values in the field which is assigned as an index.And then try to synchronize it.You won't get error and warnings.And for the net time if u try to add data in the field,if it is non -unique,then automatically we got error message as

    pastedimage1575919890997v1.png

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Unique index violation problem

    Please perform Check Synchronize on this table this will give you the detail which all record in the table is causing unique index violation

    and decide which record to delete to solve the issue

    Please verify and update us if this solves you issue

  • Verified answer
    André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 288,459 Super User on at
    RE: Unique index violation problem

    Hi Ludwig,

    If you know in which partition these records are, you can log in into this partition by naming this partition in the AX 2012 client configuration utility.

    There are three partitions used in the AX 2012 R3 demo database:

    - initial (standard demo data)

    - ext (extended)

    - ps (Public Sector)

    When you are logged in the other partition you can use your job here as well.

    Another option is to use a SQL script to make the records unique.

  • Ludwig Reinhard Profile Picture
    Ludwig Reinhard Microsoft Employee on at
    RE: Unique index violation problem

    Dear Denis, Nigel and Andre,

    Many thanks for your feedback. I double checked my data and finally wrote a job to ensure that I do not have any dublicate value in my field. Yet, AX always told me that there are still dublicate values. After getting lot's of new grey hairs :-) I finally noticed that my SQL database includes a second partition that I cannot directly access from the AX development environment. I can clearly identify the values that need to be corrected but do not know how to do the correction from the AX development environment as I always seem to work in the "initial" partition.  I could possibly simply delete the records from the SQL database directly but do not want to go that way because I do not know what other negative effects this might have. (Please note that I am currently working with a AX2012 R3 CU8 Contoso Machine that is freely available for download from partnersource).

    Hope you can give me a hint on how to fix my problem.

    Many thanks and best regards,

    Ludwig

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 288,459 Super User on at
    RE: Unique index violation problem

    HI Ludwig,

    I do agree with Nigel and Denis. Probably you missed some records or the duplicates are in another partition?

  • Suggested answer
    Denis Macchinetti Profile Picture
    Denis Macchinetti 16,444 on at
    RE: Unique index violation problem

    Hi Ludwig

    As you can see from the infolog, the index that created the issue is composed from the fields Partition and ImportID.

    The infolog say also that :

    1- The table is share. DataareaId is missing

    2- There is a record with ImportID 0. "The duplicate key value is ( 5637144577, 0)

    Indeed, there is more than one records with the ImportID field empty.

    So, fill the field with the proper value.

    Hope this help you.

  • Suggested answer
    DaxNigel Profile Picture
    DaxNigel 2,572 on at
    RE: Unique index violation problem

    I cannot say why it has happened, but to resolve the issue you need to go into SQL and you can write a SELECT query to review the data and determine why the unique control is failing. Once you find the issue you can remove the offending record.

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 288,459 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,783 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans