SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Surogate Keys are not always the answer but are freqently used


Surogate Keys are not always the answer but are freqently used

Author
Message
Michael Valentine Jones
Michael Valentine Jones
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13911 Visits: 11848
Always use Surrogate keys for the primary key. Add unique constraints on all natural keys (alternate keys).

Any other approach will eventually get you in trouble.
Welsh Corgi
Welsh Corgi
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25714 Visits: 4895
So you are suggesting that you should never consider a Natural or Candidate Key as the primary and always use a surrogate key regardless of the circumstances?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
hallidayd
hallidayd
SSC-Addicted
SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)

Group: General Forum Members
Points: 446 Visits: 297
I know Michael from another forum and have the greatest of respect for him, but I will respectfully disagree. The requirements of some systems necessarily require natural keys, for others they are preferable to surrogates.
sqlvogel
sqlvogel
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2026 Visits: 3706
Michael Valentine Jones (10/14/2010)
Always use Surrogate keys for the primary key. Add unique constraints on all natural keys (alternate keys).


A primary key means exactly the same thing as an alternate key so as you've written it this advice doesn't make much sense. I expect you mean "avoid using business data in any foreign key references" but that's different. Anyway, using natural keys for foreign key references can be very useful, advantageous and sometimes necessary.
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25360 Visits: 12489
Michael Valentine Jones (10/14/2010)
Always use Surrogate keys for the primary key. Add unique constraints on all natural keys (alternate keys).

Any other approach will eventually get you in trouble.


Do you really believe that or are you just trolling?

Tom

Michael Valentine Jones
Michael Valentine Jones
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13911 Visits: 11848
Tom.Thomson (10/15/2010)
Michael Valentine Jones (10/14/2010)
Always use Surrogate keys for the primary key. Add unique constraints on all natural keys (alternate keys).

Any other approach will eventually get you in trouble.


Do you really believe that or are you just trolling?



Of course I believe what I say.

I didn’t want to get into a big debate because it never changes anyone’s mind, but I just thought this thread needed to have a counterpoint to what seemed to be a “use natural keys” lovefest so that new people are aware that is not a universal sentiment, and that the always use surrogate keys position is a valid approach.

To briefly state my position:
I always use surrogate primary keys for the simple reason that I have never had reason to regret that, but have regretted the use of natural keys (usually be other people) on many, many occasions when those natural keys turned out to be not so immutable or to even be unique.
Michael Valentine Jones
Michael Valentine Jones
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13911 Visits: 11848
David Portas (10/15/2010)
Michael Valentine Jones (10/14/2010)
Always use Surrogate keys for the primary key. Add unique constraints on all natural keys (alternate keys).


A primary key means exactly the same thing as an alternate key so as you've written it this advice doesn't make much sense. I expect you mean "avoid using business data in any foreign key references" but that's different. Anyway, using natural keys for foreign key references can be very useful, advantageous and sometimes necessary.



That seems like a bit of nit picking. I think it's obvious in the context that what I meant is to make the surrogate key the primary key constraint and to put unique constraints on the remaining keys.

I do have to disagree with the statement that "using natural keys for foreign key references can be very useful, advantageous and sometimes necessary", especially the necessary part.
sqlvogel
sqlvogel
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2026 Visits: 3706
Michael Valentine Jones (10/15/2010)
David Portas (10/15/2010)
Michael Valentine Jones (10/14/2010)
Always use Surrogate keys for the primary key. Add unique constraints on all natural keys (alternate keys).


A primary key means exactly the same thing as an alternate key so as you've written it this advice doesn't make much sense. I expect you mean "avoid using business data in any foreign key references" but that's different. Anyway, using natural keys for foreign key references can be very useful, advantageous and sometimes necessary.



That seems like a bit of nit picking. I think it's obvious in the context that what I meant is to make the surrogate key the primary key constraint and to put unique constraints on the remaining keys.


In light of your later comments I think it's far from obvious what you mean. You say:

I have regretted the use of natural keys (usually be other people) on many, many occasions when those natural keys turned out to be not so immutable or to even be unique.


But if a "key" is not unique then it isn't suitable as a natural key whether referenced by a foreign key or not! So how can you say that you are in favour of such natural keys but opposed to their use as foreign keys? Similarly for immutability - especially since immutability of keys is anyway an illusion or at least a rather arbitrary point of view. What matters is not whether key values can change but whether they accurately identify the things they are supposed to identify.
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25360 Visits: 12489
Michael Valentine Jones (10/15/2010)
Tom.Thomson (10/15/2010)
Michael Valentine Jones (10/14/2010)
Always use Surrogate keys for the primary key. Add unique constraints on all natural keys (alternate keys).

Any other approach will eventually get you in trouble.


Do you really believe that or are you just trolling?



Of course I believe what I say.


I'm somewhat surprised, because it seems a very extreme position. It makes exactly as much sense to me as saying "always use natural keys" (ie none at all).

I didn’t want to get into a big debate because it never changes anyone’s mind, but I just thought this thread needed to have a counterpoint to what seemed to be a “use natural keys” lovefest so that new people are aware that is not a universal sentiment, and that the always use surrogate keys position is a valid approach.

To briefly state my position:
I always use surrogate primary keys for the simple reason that I have never had reason to regret that, but have regretted the use of natural keys (usually be other people) on many, many occasions when those natural keys turned out to be not so immutable or to even be unique.


I will stick to my own position, which is to use a surrogate key when it it not sensible to use a natural key, use a natural key when it is not sensible to use a surrogate key, and where each will work use whichever will work better. I've been badly bitten by a design (not mine - I just had to clear up the mess when the system fell in a heap) that used surrogate keys for everything, and equally badly bitten by a design (again not mine, I just ended up doing cleanup) that used natural keys for everything (someone used an 800 byte collection of columns as the clustering key for one table, as well as as a forign key in several tables referncing that one). In my experience it has often been useful to use a natural key than not, but not anything like 100% of the time.

Tom

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search