Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Unique index violation...
Microsoft Dynamics AX (Archived)

Unique index violation problem

(0) ShareShare
ReportReport
Posted on by Ludwig Reinhard 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

*This post is locked for comments

  • 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 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 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... 292,516 Super User 2025 Season 1 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... 292,516 Super User 2025 Season 1 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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Daivat Vartak – Community Spotlight

We are honored to recognize Daivat Vartak as our March 2025 Community…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Kudos to the February Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,516 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,409 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans