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
Welsh Corgi
Welsh Corgi
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10248 Visits: 4894
I read an article today on this site today about Surrogate Keys.

I attended my first relational database theory & design training in Princeton NJ.
in 1988 and I'm familiar with the E. F. Codd & C.F Date Theories.

I was very fortunate to have attended the class & my instructor was extremely intelligent and I will never forget her for I learned a lot from her. She was very interesting and as a result I developed a strong interest in Data Modeling & Database Design.

But when Data Modeling I took what learned from the training and customized to make it work.

I learned that something may look good on paper when implement it is often flawed and needs rework for it is not a solution to the problem.

Despite the criticism of their theory, I learned a lot and I adapted their theory to deliver practical functional Database Applications.

I attended the Oracle Data Modeling Course in 1997 and it was not so interesting because I had been performing Data Modeling on the Job for so long.

It many cases it is appropriate to use a surrogate key when you do not have a candidate key or the key would induce performance problems etc.

It has been my experience that it depends on the situation.

I use Surrogate keys a lot but there are pros & cons.

The decision should be based on the Business requirements, situation and a number of other factors.

Listed below are some articles on why you should consider using a surrogate or an natural key.:-)

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

http://www.agiledata.org/essays/keys.html

http://www.dbdebunk.com/page/page/626995.htm

http://en.wikipedia.org/wiki/Surrogate_key

:-)

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/
vidya_pande
vidya_pande
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1107 Visits: 242
Gr8 !!! can you please post the summary of your learnings about surogate keys.



PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5437 Visits: 4639
Welsh Corgi (3/22/2010)
It many cases it is appropriate to use a surrogate key when you do not have a candidate key or the key would induce performance problems etc.

Being one of the "etc" the situation where doing dimensional modeling you want to take advantage of Oracle's star-transformation feature.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Raunak Jhawar
Raunak Jhawar
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1649 Visits: 1944
Surrogate keys are always the answer if you are dealing with any successful Data Warehouse implementation. For instance take the case of any SCD transformation.

Regards/Raunak
Now a member of Linkedin

Please visit the all new Performance Point Forum
Please visit the all new Data Mining and Business Analytics Forum
Tom Thomson
Tom Thomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14402 Visits: 12213

The first URL quoted soesn't mention surrogate keys at all, the second returns an error (sometimes 404, but sometimes 500 so there may be something there sometimes), the last (wikipedia) is remarkably unenlightening (needs a rewrite: if I remember and have the time and the energy I may either edit it or put some suggestions into the talk page); that leaves the other URL (of which there are, for some reason, two copies in the list) as the only useful one (two?). I don't always agree with FP, but here I have to: there's only one sensible reaon for using a surrogate key, and that is that using the natural key is too messy (because it has too many colums, is too long) and makes things which are actually simple look complicated. There are of course two nonsensical reasons, which are often quoted as sensible reasons: (a) some of the columns in the natural key change quite often and (b) some relations have no natural key; (a) and (b) are nonsensical for the simple reason that if either occurs this is a clear indication that the entity modelling has been done incorrectly - the solution isn't to introduce spurious surrogate keys, but to redo the model so that every relation has a stable natural key (that of course doesn't mean that you have to use the natural key for an entity as the foreign key when referencing the entity: it may be more convenient to use a surrogate key to reduce apparent complexity).

edit: spelling

Tom

sqlvogel
sqlvogel
SSC Eights!
SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)

Group: General Forum Members
Points: 800 Visits: 3706
Tom, I agree with everything you wrote except your suggestion that keys should be stable:

redo the model so that every relation has a stable natural key


Stability is usually a desirable property for a key and is certainly an important consideration but it isn't an absolute requirement. Sometimes it may be quite reasonable to implement keys that are unstable by design. One example is a login name attribute. The user may be allowed to change his login name at any time. The login name must remain unique though, so it's perfectly sensible to make it a key in the database.
Evil Kraig F
Evil Kraig F
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8631 Visits: 7660
David Portas (10/13/2010)

Stability is usually a desirable property for a key and is certainly an important consideration but it isn't an absolute requirement. Sometimes it may be quite reasonable to implement keys that are unstable by design. One example is a login name attribute. The user may be allowed to change his login name at any time. The login name must remain unique though, so it's perfectly sensible to make it a key in the database.


David, I would agree with you in one way and disagree in another. I would say the login name is stable. Yes, it *could* be altered daily. Noone would though. Now, password, on the other hand, is an 'unstable' key. In theory it should be being altered by every user every 30 days. Not quite as unstable as, say, a LastUpdate field, but still unstable.

So, while the ability to be unstable is there, I would say a login-name would be a stable field.

To the other part of your statement (before I get shot for symantics about 'login-name'), Unique fields can be controlled very easily with a unique, non-clustered, non-keyed index and get out of the way of the real ones.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
sqlvogel
sqlvogel
SSC Eights!
SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)

Group: General Forum Members
Points: 800 Visits: 3706
I doubt that a password would be a key. Most systems that I know of don't require passwords to be unique.

In SQL Server a unique index on non-nullable columns is just a key by another name. Unique indexes and uniqueness constraints are simply two different ways to create keys. I would not recommend using a unique index instead of a uniqueness constraint. Constraints, not indexes, are the conventional, SQL standard and most obvious way to implement keys. As far as I know unique indexes alone have no particular advantages over uniqueness constraints. Was there a reason why you mentioned unique indexes specifically? What do you mean by a "non-keyed" index?
Evil Kraig F
Evil Kraig F
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8631 Visits: 7660
David Portas (10/13/2010)
I doubt that a password would be a key. Most systems that I know of don't require passwords to be unique.


You're right, it was a bad example of the point regarding uniqueness.


In SQL Server a unique index on non-nullable columns is just a key by another name. Unique indexes and uniqueness constraints are simply two different ways to create keys. I would not recommend using a unique index instead of a uniqueness constraint. Constraints, not indexes, are the conventional, SQL standard and most obvious way to implement keys.


Same difference. From: http://msdn.microsoft.com/en-us/library/ms177420.aspx

The Database Engine automatically creates a UNIQUE index to enforce the uniqueness requirement of the UNIQUE constraint. Therefore, if an attempt to insert a duplicate row is made, the Database Engine returns an error message that states the UNIQUE constraint has been violated and does not add the row to the table. Unless a clustered index is explicitly specified, a unique, nonclustered index is created by default to enforce the UNIQUE constraint.


What do you mean by a "non-keyed" index?


I meant non primary keyed and typed too fast and didn't re-read what I wrote to translate internal mental lingo into what would be definitive instead of vague. Yes, Unique columns/indexes/constraints can be used as 'alternate keys'. They're just rarely used as them, because usually the need for the constraint means that means the key can be changed, thus possibly destroying the key associations.

Thus, while a unique can be an alternate key, an alternate key is always unique. The difference is not inconsequential, thus I separate the concept. The fact that some data is usually mobile in a 'natural key', say, like the company's name, is why I like surrogate keys, like identity fields. This means that if a user edits the company's name (which is unique, at least by state), we still haven't lost our unique locator that everything else hooks to it with.

Of course, if you have the business' FedTaxID, or a SSN, that never changes, this is a proper natural key. Stability is incredibly important to a key, if not a primary factor in deciding to use it.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
sqlvogel
sqlvogel
SSC Eights!
SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)

Group: General Forum Members
Points: 800 Visits: 3706
Craig Farrell (10/13/2010)

In SQL Server a unique index on non-nullable columns is just a key by another name. Unique indexes and uniqueness constraints are simply two different ways to create keys. I would not recommend using a unique index instead of a uniqueness constraint. Constraints, not indexes, are the conventional, SQL standard and most obvious way to implement keys.


Same difference.


No it's not the same for the reasons I already stated: Constraints are standard SQL, unique indexes are not; Constraints are understood and used by more people; Some software tools will recognise keys declared as constraints but will not recognise those created using the CREATE INDEX syntax. In my view those are all very good reasons to use uniquness constraints and avoid creating unique indexes directly.

Thus, while a unique can be an alternate key, an alternate key is always unique. The difference is not inconsequential, thus I separate the concept. The fact that some data is usually mobile in a 'natural key', say, like the company's name, is why I like surrogate keys, like identity fields. This means that if a user edits the company's name (which is unique, at least by state), we still haven't lost our unique locator that everything else hooks to it with.


That's more or less what I thought you meant, but it's not technically correct. Two things make a key (aka "candidate key") and they are 1) uniqueness and 2) irreducibility. Those are the two fundamental qualifications for a key. Stability is usually desirable as well but the fact that a key changes does not make it any less of a key than some other key that doesn't change. Of course that's just terminology I suppose, the effect is exactly the same whether you choose to call it a key or not - the same considerations apply. But I think it's worth saying because a lot of people do seem to get very confused about what keys are and why they are important.

I'm avoiding the term "alternate key" because primary and alternate keys are really the same thing. All are candidate keys.
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