What is the use with Primary Index ?
i am created an index on a field with prop “Allow duplicate → NO”
(so it restricts duplicate data on that filed it accepts null values)
(Even after assign a index to primary index it accepts null values on that field)
now what is the use of primary index ?
why i should assign a index to primary index ?
Hi Roshan,
this has been already discussed in the forum.
Hi karanti,
in the above link they are discussed about Primary & cluster indeces,
But i am asking different…
when i am assigning a index to primary index what are the additional features are added to that field ?
The fields which you have used in the primary index - should have the Mandatory property set to “Yes”.
Indexes are more the data base concepts - only a less will be taken about indexes in x++(as per my knowledge).
default sorting of the data when you fetch will be done on the base of primary index.
optimization of data fetching.
What i am thinking is when we create a primary key in database automatically it will create a primaryindex
That stops the duplicate and null values.
but here even after assigning the index to primary index it will accept null values (if i didn’t choose the prop what u mentioned above)
So Here the primary index is used only for fetch the data form x++ coding…
By default it will not add any more costraint to that field… is this ?
when i am creating a primary key in database level automatically it will create a primary index
and it will restrict the duplicate and null values
but here after assigning the index to primary index still it accepts the null values (if the prop what u said set to no)
What i understood from u r reply is:
Here we are using primary index to fetch the data quickly from X++
By default it will not add any more costraint to that field… is this ?
This is a fundamental concept that every developer MUST know.
The ONLY thing that makes primary key stand out from other keys is that the primary key defines the uniqueness of the records in the table. It’s basically the identity of the record. There is one and only one record in the database for each combination of primary key fields. The default sort order has nothing to do with primary key. Some DBMS’s set the primary key as the default sort order, but being the default sort order does not make an index the primary key. That’s why the primary key, and default sort order are two different properties.
Go to the bookstore, and purchase a book about database design. Forget about SQL Server, AX, or whatever product you are using. Get yourself a book that explains the attributes of a database. Make sure that this book includes a chapter on how to normalize data. You need to know these things. You can’t call yourself a database developer if you don’t understand these things.
No and No. The ONLY purpose of the primary key is to uniquely identify records.
Being the default sort order does NOT make an index the primary key. On SQL Server that’s what the “Clustered Index” is for. You can set any index as the clustered index, that doesn’t have to be the primary key.
You can play around perhaps with the order of the field and putting them in order of selectivity, but the primary key itself has nothing to do with any type of performance optimization.
The primary key is determined as a result of normalizing your data, not because of what you want the default sort order to be, and not what is the fastest way to fetch data. Again, it is what uniquely identifies records in the table.
I didn’t mean it Dester.
Do you mean that primary index will not play any role in performace optimaization?
I have only mentioned some of the advantages of taking an index as primary index in ax. These are some of the things i have read in some books.
Once again thanks for your information.
Need not be necessary Denster.(there are many things in ax apart from this)
Any developer that works with databases should know what “primary key” means. Every ERP developer works with databases, so every ERP developer MUST know about basic database concept. Primary key is one of the MOST fundamental concepts, and EVERY ERP developer MUST know what it means.
It doesn’t matter what product you are working with. The primary key has the same meaning in every single DBMS in the world. If you don’t know what primary key means, you will never be able to properly program any type of data retrieval.
Here, browse around on this site for a while: http://databases.about.com/od/administration/tp/beginners_faq.htm
I think Primary Key and Primary Index are different things - here we are discussing about the primary indexes.
I have a basic knowledge on Oracle.
when we are creating a primary key it creates a primary index on that field.
what i am asking is who restrict the null & duplicate values The primary key (or) Primary index ?
and when i create a primary key it creates a primary index
is in the same way the primary index can create primary key on that field ?
if Yes,
Even after assigning primary index to a field it accepts the null values y?.
If No,
I Seen the structure of a table in sql server what i created an primary index on a field
It shows a primary key symbol on that field…y?
Whatever you call it, there is one and only one “primary key”/“primary index”, and that is the one for which:
1 - Primary key values are unique for each record
2 - Every instance MUST have a primary key value (that means no null values)
3 - No duplicates (for combinations of the fields, not each individual one)
I can’t speak for AX because I work with NAV. In NAV though, in the table designer, they are all called “keys”. When NAV runs on SQL Server, it creates an “index” for each “key”. For purposes of NAV, the word “key” really means “sort order”. On SQL Server, what that means is that the “primary key” is created as an “index” with a “primary key constraint”. Often people call this thing the “primary index”.
When you define an index as the “primary key”, that implies uniqueness, and it should in any relational DBMS. As soon as you put a checkmark by a “primary key” property, you should no longer be able to disable the “unique” property. Uniqueness is part of the definition for primary keys, so it should really be irrelevant.
In SQL Server you see those little key symbols by the fields that are part of the primary key, so that you can tell visually which fields are part of the primary key. Knowing what the primary key is of any table is probably one of the most important things to know when you are developing for a relational database.
It might be that you have a compound key that allows null values in individual fields. You should NOT be able to enter the same combination of fields (whether that includes null values or not) more than once. It shouldn’t though, because it is contrary to what is generally accepted, but I can see how technically “1,,2” is still a unique combination, or even “,,”. However, you should NOT be able to enter either combination more than once.
Now if AX has something else that they call “primary index”, and if that would mean something other than “primary key”, so if you can have one “primary index” as well as a different “primary key”, then I would say that is a dumbass name, and it would be very confusing [:D]
[:D]
No input to this except to say that AX has a lot “dumbass” naming conventions that confuse everyone - item dimensions, item storage dimensions, finanical dimensions, physical dimensions etc etc. So in all probability they are different and it was done this way jus tto confuse us mere humans. [:D]
[:D]
No input to this except to say that AX has a lot “dumbass” naming conventions that confuse everyone - item dimensions, item storage dimensions, finanical dimensions, physical dimensions etc etc. So in all probability they are different and it was done this way jus tto confuse us mere humans. [:D]
Hi Roshan,
To answer Your last question,
If You want to ensure that the set of values is unique throughout a table, create an index from these values and set its property Allow Duplicates to “No”. It can, but doesn’t have to be, the primary index set in the table preferences. Remember that creating a new index is not always the best solution (indexes take disk space and have to be updated You know). Sometimes x++ validation is a better option.
AX doesn’t store NULL values in a database, therefore every data entry in AX is some kind of a value. It might be confusing at the beginning for people who are used to common understanding of storing values in a db. For example, if You don’t fill out a string field in AX, it will contain an empty character (stringField == “”), but even that is a piece of data, so it’s not a NULL value (stringField == NULL). The same goes for numeric values - the default value of an integer or real field in AX is 0 and not NULL.
If You want to force the user to specify a value of a field, set Mandatory property on a field to Yes. This might be tricky sometimes since if You do so, numeric fields will not accept 0 and enum fields will not accept the first position of the enum - that might not be what You’re expecting. If You want to ensure that the user has provided some input and settled for a 0 value (numeric fields) or the first position (enum fields), You will have to do some x++ programming.
Regards,
Lukens
Hi Roshan,
independend from all the stuff which has been tried to answer your question I would like to give you a REALLY hint about this issue:
The PrimaryIndex property on table level has NOTHING to do with optimisation on fetching records within X++. It also has nothing to deal with uniqueness of records within a table.
The only thing where a PrimaryIndex will be taken into account is the complex table caching mechanism!
There exist a set of rules when records will be selected from the AX table cache instead of the underlaying database.
One of the rules said
“… the cache is used only if the select statement contains equal-to (==) predicates in the where clause that exactly match all the fields in the primary index of the table. The PrimaryIndex property on the table must therefore be set to the uniques index used when accessing the cache …”
(taken from “Inside Microsoft Dynamics AX 4.0”, Page 445)
Since AX2009 it looks like the PrimaryIndex property seems to be obsolet 'cause the new kernel feature of AX2009 also uses other unique indexes on table for the caching mechanism.
regards
WoHeGro
Actually, PrimaryIndex is used for at least one more thing.
If you do a Refresh on a form data source and pass “retainPosition=true” to keep the cursor on the same line as before the refresh, it is necessary to have a value in the primaryindex property. Otherwise this argument will not work.