Talk:Surrogate key

From Wikipedia, the free encyclopedia

This article was originally based on material from the Free On-line Dictionary of Computing, which is licensed under the GFDL.

The assertion that "the addition of a surrogate key will slow down access to the table, particularly if it is indexed" should be removed. It's not substantiated, incomplete, and misleading. It has no place in theoretical performance computations and in practice is almost always incorrect anyway. Finally, it's not a part of the definition.

Contents

[edit] Agreed

I agree. An index on a surrogate key will be smaller (and therefore could be faster) than an index on a natural key.

[edit] Hrmph!

Some Anonymous Hero's decided that it's better to throw out everybody else's work and dump in some essay they wrote themselves. I'm marking this for wikification, and I'm going to wikify and merge in some of the old content if it's relevant when I've more time. --Kgaughan 19:35, 13 December 2005 (UTC)

[edit] Needs Attention

This article needs attention. The current posting is correct, though quite poorly written. It's certainly relational-centric.

The old article seems to confuse system-assigned keys with surrogate keys, particularly in this paragraph: ""Surrogate key" may also be known as "System-generated key", "Database Sequence number", "Synthetic key", "Technical key" or an "Arbitrary, unique identifier"."

I don't think either version thinks of "surrogate keys" from the point of view of data warehousing, though.

-- 03:01, 31 December 2005 (UTC)

Huh? Surrogate Keys are, AFAIK, a relational database concept. How does the term even apply to other kinds of databases? Jberkus 19:05, 23 September 2006 (UTC)

[edit] Similarities

Surely a surrogate key and a primary key are identical? Is there really any difference?

Sure there is. A primary key can be surrogate, or it can be natural. A surrogate key is always a candidate key, but may or may not be designated primary key (as any other candidate key, really). Of course, in practice surrogate keys are pretty much always primary keys (but not vice versa, though surrogate primary keys seem to be more popular at the moment). -- int19h 08:12, 11 September 2006 (UTC)

[edit] Added Advantages/Disadvantages

I've added a summary of the main advantages and disadvantages of Surrogate Keys. I've also linked my own blog article; I realize that linking one's on blog is in poor taste, so if someone else has an article which extensively catalogs the disadvantages of surrogate keys, then please link it instead. Jberkus 19:07, 23 September 2006 (UTC)

[edit] Surrogate Keys in Data Warehouse

Isn't surrogate keys more applied in the data warehousing for relating fact and dimension tables. Surrogate keys are also one of the solution for the "Slowly Changing Dimension" problem. I strongly feel this has to be included along with the following external link [1]. —The preceding unsigned comment was added by 203.123.182.27 (talk) 05:19, 2 February 2007 (UTC).


[edit] Compatibility

"several database application development systems, drivers, and object-relational mapping systems, such as Ruby on Rails or Hibernate, depend on the use of integer or GUID surrogate keys in order to support database-system-agnostic operation and object-to-row mapping."

Hibernate works fine with non-surrogate key, even with composite keys, see [2]. I know little about Ruby on Rails, but I very much doubt that they require surrogate keys.

—The preceding unsigned comment was added by 85.158.137.195 (talk) 11:16, 20 April 2007 . (sig added by --FvdP (talk) 16:07, 9 May 2008 (UTC))

[edit] Random keys

"However, a randomly generated primary key must be queried before assigned to prevent duplication and cause an insert rejection." Really ? Isn't the point of some random generation schemes (random 128-bit UUIDs) to make the test redundant by making the collision probability definitely negligible ? Also can't the collision, if need be, be checked after rejection by the uniqueness constraint ? --FvdP (talk) 16:12, 9 May 2008 (UTC)