Finance | Project Operations, Human Resources, ...

primary key and alternate Key

(0) Share

Report

Posted on by junior AX

1,552

Hi,

1. So if i put an index with allow duplicate No, and i don't set the primary index property to anything what would happen? what would it consider the primary key, would it consider it the RecId or the index with duplicate No?

2. What's the difference, if i put alternate key true or false with allow duplicate no in AX7?

I have the same question (0)

All responses (8)

Answers (0)

Suggested answer

Gunjan Bhattachayya 35,423 on at

Like (0)

Report

Hi junior AX,

1. You will need to set this index as the the Primary index for the table. If the primary index is still set as "SurrogateKey", RecId will be the primary index.

2. If you set the index as an alternate key, you can choose the index as the Replacement key.

You can check the table HcmWorker as an example. the Primary index is the SurrogateKey. There are two alternate keys, one of which is selected as the Reference key.

Was this reply helpful? Yes No
junior AX 1,552 on at

Like (0)

Report

So it's always better to set my allow duplicate no key in the primary index property instead of surrogate key? right?

why hcmWorker chose surrogate key as a primary index instead of one of the indexes. And why there are two alternate keys?

Was this reply helpful? Yes No
Gunjan Bhattachayya 35,423 on at

Like (0)

Report

Hi junior AX,

Keeping the primary key based on RecId is always better. When you create foreign keys with other tables, the relation is based on Int64fields. This improves database performance as the table operations are faster as compare to primary keys with string values. All the newer tables have Surrogate keys as the primary index. A few of the legacy tables (CustTable for an example) has a primary key based on a string field.

I am not sure why HcmWorker has two alternate keys since the fields in these keys are the same as well. Might be to improve perfomance while fetching records in certain scenarios.

Was this reply helpful? Yes No
junior AX 1,552 on at

Like (0)

Report

But i thought creating indexes improves performance.

And when i create a relation where field x = field y then the relation is not based on recIds.

Also isn't an index with allow duplicate no considered a primary key?

I think i got confused, can u explain more the difference between an index with allow duplicate no and the primary key property

Was this reply helpful? Yes No
Suggested answer

Gunjan Bhattachayya 35,423 on at

Like (0)

Report

Hi junior AX,

Please go through this post. It should clear your understanding. It talks about AX 2012, but it is the same for D365 F&O.

An index with allow duplicates "No" is an unique key. The primary index needs to be set in the table properties.

Was this reply helpful? Yes No
Suggested answer

Sergei Minozhenko 23,093 on at

Like (0)

Report

Hi JuniorAX,

1. The primary key is identifier for the record, something that will never be changed or changed very rarely. That's why it's usually surrogate index by RecId as RecId allocated when a record is created and never changes.

2. Unique index is just needed to check if record support uniqueness by set of field in the index

Was this reply helpful? Yes No
junior AX 1,552 on at

Like (0)

Report

Hi Sergie,

1. So it's better to leave the primary key as surrogate key and not put the unique index. If yes then why?

2. you say primary key is identifier for the record. isn't the unique index an identifier as well? i mean the primary key should always be unique as well.

Was this reply helpful? Yes No
Suggested answer

Martin Dráb 237,965 Most Valuable Professional on at

Like (0)

Report

1. Yes, it usually makes a better sense. The surrogate key contains just RecId, not DataAreaId, which makes it more efficient, and you can use links via the surrogate key and merely display other fields in forms (through reference group controls).

2. No, primary key shouldn't be unique. It MUST be unique.

Was this reply helpful? Yes No