Post reply

correct use of primary keys

  • May 11, 2009 at 9:37 am

    #93560

    I've read fragment of advise on the correct use of primary keys but I never see anybody complete the though. I've read that the primary key shouldnt be set on a "monotonic" col-- an identity column would qualify as that and I also get the impress that the correct PK columns are sometime along the lines of "columns of real-world data that uniquely identifies the row. Like for a inventory system, a SKU would be good. yes?

    Also, in the above situation, is there any issues will still using the identity col as the target of foreign keys? EM seems to show a preference for pointing to the PK and I wonder how strong that preference should be.

    tks.

  • May 11, 2009 at 9:51 am

    #992249

    kevinH (5/11/2009)

    I've read that the primary key shouldnt be set on a "monotonic" col-- an identity column would qualify as that and I also get the impress that the correct PK columns are sometime along the lines of "columns of real-world data that uniquely identifies the row. Like for a inventory system, a SKU would be good.

    There's a big difference between a primary key and a clustered index. The above advice is what's usually given for placing the clustered index on a table, though a cluster on an identity is often a good idea. A Primary Key is the row's 'identifier'. It's what till be used in other tables to reference rows in this one, and it's a business/design decision as to where it should go. The primary key must be unique and not nullable.

    A primary key is by default enforced by a clustered index, but that's not a requirement. If the cluster would be better elsewhere, the primary key can be made nonclustered.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 11, 2009 at 9:56 am

    #992255

    I think you're confusing clustered/nonclustered index v a PK.

    A PK is a logical structure, not a physical one. It uniquely identifies a row. It is implemented as a unique index, and defaults to clustered.

    Clustered indexes should be on a range type field, something where you will query and retrieve rows of data that will be stored near each other.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply