Multiple column primary key or guid

  • All,

    I would appreciate views on the following:

    When the unique combination for table involves several columns is it best to:

    a) Make the primary key include these three columns

    b) Make a unique reference (such as a guid) as the primary key and use a constraint, on the original columns, to enforce the uniqueness?

    If I use option a) then if another table references this one then it has to include all the extra columns which uses extra space and someone could miss a column from the join. However if I use option b) then I'm adding an extra column and kind of moving away from pure normalisation?

    I've always used option b) and it's never caused me any issues but I would interested in other opinions. From memory I used b) as each table required a guid for replication but that could be incorrect? I tend to use availability groups now and, as far as I know, that restriction doesn't apply.

    I'm not sure if there is a clear answer, which I've forgotten, or whether it is a case of "it depends"?

    Thanks

     

  • It's fine to create a primary key constraint on multiple columns.  You'll want to choose your clustered index key more carefully, though.  The clustered index is by default (in most circumstances) created on the primary key, but you don't have to do it like that, and indeed in many cases it's better to have your primary key non-clustered.  Be careful with GUIDs, especially if you're thinking of using one as your clustering key - you may find yourself with a lot of expensive page splits.

    There's a lot more to it than what I've said above - far too much to put in a forum post.  And yes, it does depend, not least on personal preferences.  If you're (un)lucky, you'll spark a debate on natural versus surrogate keys, GUIDs versus integers and all sorts of other things that people hold quasi-religious views on.  The best thing to do is read about it and make up your own mind.

    John

  • Thank you for your help.

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

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