Secondary Keys Question

infonoteinfonote Member Posts: 233
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.

Comments

  • BeliasBelias Member Posts: 2,998
    Secondary keys are generally crated for ordering, grouping and performance purposes, so secondary keys mustn't be uniques...(they CAN be by the way)...
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • infonoteinfonote Member Posts: 233
    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.
  • lubostlubost Member Posts: 611
    Navision always adds primary key at the end of all secondary keys.
  • krikikriki Member, Moderator Posts: 9,086
    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.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • DenSterDenSter Member Posts: 8,304
    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.
  • krikikriki Member, Moderator Posts: 9,086
    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.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • DenSterDenSter Member Posts: 8,304
    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.
Sign In or Register to comment.