I want to understand RECID and index mechanism through this blog.
All tests were done in AX3 (SP3+KR3) +SQL2005(SP2)
1, For the system tables which created by Microsoft will create index on RecID+Dataareaid. Other indexs also can be created but on the index of RecID+Dataareaid.
For user tables which created by users, first, if it hasn't an index, system will create an index on RecID+Dataareaid as an unique value.
Then when you want to create a record which use a RecID not existed in this table, yes, system can create the record using this RecId.
If you want to create another record which use the same RecId as above in this table, now system will report an error said the record already exists because the index of RecID+Dataareaid is a unique value by system.
* Mark, RecID only is limited that it can't be duplicated in the same table. Yes, if a RecId was used in one table, of course, this RecId can be used in another table. That is meaning the RecID can be duplicated among tables.
2, Create indexs on AX table to test
Create an index using a field named Field1 named Field1Idx, the option of allowduplicate is enabled by default in AX.
Then you'll find an index in database: Field1+RecID+Dataareaid as unique.
Example data in table
Field1 Field2 DataareaID RecID
A 1 1 Test 2056600387
B 2 2 TEST 2056600388
If we insert a record (3,3,TEST,2056600387) which the RecID is the same as Record A, you'll find the new record can be inserted. Only when your record is (1,1,TEST,2056600387) is the same as a record in table, then the system will report a error said the record already exists.
*Mark, but for AX orginal tables, because the index of RecID+Dataareaid is unique as a single index, it can't be duplicated.
3, Create an index using a field named Field1 named Field1Idx, the option of allowduplicate is disabled in AX.
Then you'll find an index in database: Field1+Dataareaid as unique.
Using the data exampled above.
If we insert a record (3,3,TEST,2056600387) which the RecID is the same as Record A, you'll find the new record can be inserted. when your record is (1,1,TEST,2056600387) is the same as a record in table, then the system will report a error said the record already exists. if when your record is (1,1,TEST,2056600389) which the RecID is different from A record , then the system also will report a error said the record already exists. That's meaning only when Field1+DataareaID is unique, then it can be inserted the table. It doesn't refer to RecId.
*Mark, so it is easy to generate duplicated RecID if we setup multi indexs on our private tables. We need to design tables very carefully and use other fields to insure record as a unique value.
All tests were done in AX3 (SP3+KR3) +SQL2005(SP2)
1, For the system tables which created by Microsoft will create index on RecID+Dataareaid. Other indexs also can be created but on the index of RecID+Dataareaid.
For user tables which created by users, first, if it hasn't an index, system will create an index on RecID+Dataareaid as an unique value.
Then when you want to create a record which use a RecID not existed in this table, yes, system can create the record using this RecId.
If you want to create another record which use the same RecId as above in this table, now system will report an error said the record already exists because the index of RecID+Dataareaid is a unique value by system.
* Mark, RecID only is limited that it can't be duplicated in the same table. Yes, if a RecId was used in one table, of course, this RecId can be used in another table. That is meaning the RecID can be duplicated among tables.
2, Create indexs on AX table to test
Create an index using a field named Field1 named Field1Idx, the option of allowduplicate is enabled by default in AX.
Then you'll find an index in database: Field1+RecID+Dataareaid as unique.
Example data in table
Field1 Field2 DataareaID RecID
A 1 1 Test 2056600387
B 2 2 TEST 2056600388
If we insert a record (3,3,TEST,2056600387) which the RecID is the same as Record A, you'll find the new record can be inserted. Only when your record is (1,1,TEST,2056600387) is the same as a record in table, then the system will report a error said the record already exists.
*Mark, but for AX orginal tables, because the index of RecID+Dataareaid is unique as a single index, it can't be duplicated.
3, Create an index using a field named Field1 named Field1Idx, the option of allowduplicate is disabled in AX.
Then you'll find an index in database: Field1+Dataareaid as unique.
Using the data exampled above.
If we insert a record (3,3,TEST,2056600387) which the RecID is the same as Record A, you'll find the new record can be inserted. when your record is (1,1,TEST,2056600387) is the same as a record in table, then the system will report a error said the record already exists. if when your record is (1,1,TEST,2056600389) which the RecID is different from A record , then the system also will report a error said the record already exists. That's meaning only when Field1+DataareaID is unique, then it can be inserted the table. It doesn't refer to RecId.
*Mark, so it is easy to generate duplicated RecID if we setup multi indexs on our private tables. We need to design tables very carefully and use other fields to insure record as a unique value.
