Secondary Keys Question

infonote

Hi,

I have been reading posts for a while but never posted.

I have just started going over C/SIDE dev I chapters and have a newbie question regarding Secondary keys.

Are the Secondary keys in Navision unique?

E.g. primary key is Serial No which is obviously unique.
Must the secondary key also be unique?

Thanks in advance.

Belias

Secondary keys are generally crated for ordering, grouping and performance purposes, so secondary keys mustn't be uniques...(they CAN be by the way)...

infonote

Belias wrote:

Secondary keys are generally crated for ordering, grouping and performance purposes, so secondary keys mustn't be uniques...(they CAN be by the way)...

Thanks.

lubost

Navision always adds primary key at the end of all secondary keys.

kriki

lubost wrote:

Navision always adds primary key at the end of all secondary keys.

Also in SQL, Navision does that, but starting from NAV 4.00SP1, you can define the SQL-index for a Navision key or NOT maintain any SQL-index.

DenSter

infonote wrote:

Are the Secondary keys in Navision unique?

Yes (but not really) and no.

No because the doesn't not have a property to make the key unique. So even if you wanted to make a secondary key unique, you would not be able to do it.

Yes because NAV internally makes sure that all primary key fields are also included. So looking at the Sales Header table, which has DocType and DocNo as the primary key, when you add a key for SelltoCustomer, inside the object NAV will add both primary key fields to the key. You don't see this in the actual key design, but it is done behind the scenes. It is still technically not a unique key constraint though, because you can have the same combination of secondary key field combination more than once in the table.

On SQL Server, you can see this clearly by looking at the indexes. You'll see the primary key fields added to the corresponding index, and the index is defined to be a unique index. Now when you define the index that you want SQL Server to use in the SQLIndex property, then NAV does NOT add the primary key fields to the index, and the Unique constraint is not set.

kriki

DenSter wrote:

Now when you define the index that you want SQL Server to use in the SQLIndex property, then NAV does NOT add the primary key fields to the index, and the Unique constraint is not set.

I read that, internally, SQL adds the fields from the clustered index, EVEN if you change the key with the SQLIndex-property.

DenSter

Then that part gets hidden somehow, because I don't believe you can see those fields in the index designer (I'd have to verify that though). I'm pretty sure if you specifically set the SQLIndex without any/all of the primary key fields, it gets created into a non-unique index on SQL Server. If you leave the SQLIndex blank, the key gets created as a unique index, and THEN SQL Server adds the primary key fields to the index.