Once again I am putting in front the same question that has been discussed again and again. What difference it makes if we assign an index as ‘primary index’. Does it have do to anything with ‘primary key’?
As far as I know primary key defines uniqueness whether it is a single column or combination of columns. If we make an index of two fields and set it to unique by allowing duplicates to No (thereby making it unique), and then make this index of ours as ‘Primary Index’, then also this does not go along with the property of a PRIMARY KEY because it will be accepting Null values. And as per the definitions a primary key defines uniqueness plus it does not accept NULL values.
But here in our case, making a unique index (set allow duplicates to NO) and making it as ‘Primary Index’ does not at all make it as ‘Primary Key’. (However when it comes to not accepting Null values, we can do the same by setting ‘Mandatory’ to YES).
So why do we use ‘Primary Index’?
Is making a unique index and setting the fields as mandatory the right approach to make a primary key?
Is there lies then any relation between primary key and primary index.?
Primary key is mainly a logically concept. You can have many unique indexes if it makes sense, but only one primary identification of a record.
Besides the logical function, primary keys are used for foreign key relations, AX uses them for single-record caching and so on.
About NULL values, the answer is simple: AX doesn’t support them. If you have an empty string field, for example, the database contains an empty string, not NULL.
You can easily look into database to see that AX tables indeed use Primary Keys.
Thanks a lot Martin,
Just one thing I am not really able to grab is that if we make an unique index and if it is then assigned to ‘Primary Index’ in the table properties, what purpose it serves? In AX 2012, Primary Index is by default the Surrogate Key, but what if we make our index (unique index precisely) as the PRIMARY INDEX???
Any help on this will be really appreciated.
Thanks in advance
As I wrote, it’s used for foreign key relations and single-record caching, maybe also for other purposes. It doesn’t matter whether it is a surrogate or a natural key.
Hi Martin,
Is it mandatory to set PrimaryIndex property for all the tables? If we don’t have any unique value field, is it mandatory to create RecID index and set it as PrimaryIndex?
Thank you very much.
It’s strongly recommended to have a primary index. In AX 2012, simply leave the surrogate key there. You shouldn’t create an additional index for RecId; the surrogate key is more efficient.