Unique index violation problem

(0) Share

Report

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

All responses (8)

Answers (1)

Artezys 45 on at

Like (0)

Report

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 on at

Like (0)

Report

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
Suggested answer

Community Member on at

Like (0)

Report

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 Cal... 294,765 Super User 2025 Season 1 on at

Like (0)

Report

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 Microsoft Employee on at

Like (0)

Report

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 Cal... 294,765 Super User 2025 Season 1 on at

Like (0)

Report

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 16,444 on at

Like (0)

Report

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 2,574 on at

Like (0)

Report

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.