RE: Surrogate keys

From: Christopher Spence <cspence_at_FuelSpot.com>
Date: Tue, 12 Jun 2001 05:51:35 -0700
Message-ID: <F001.00325315.20010612052601@fatcity.com>

Cons
Primary Key has no bearing on data
Primary involves a sequence

Pros
Primary key will never risk change
Primary key will never risk being null
Multiple columns will not migrate to other tables on relationships Very simple to implement
More efficient index use (as you don't have to use Primary key in order of index build)
Index size is smaller than concatenated primary keys

I generally use Surrogate keys more often than most dbas, I find them great. But when there is that "perfect primary key" I will actually opt for a more intelligent primary key. But when there is any doubt, what so ever how ever slim, I generally lean to surrogate keys.

Really good article on Surrogate keys:
http://www.dbpd.com/vault/9805xtra.htm

"Walking on water and developing software from a specification are easy if both are frozen."

Christopher R. Spence
Oracle DBA
Fuelspot

-----Original Message-----
Sent: Tuesday, June 12, 2001 9:00 AM
To: Multiple recipients of list ORACLE-L

Hi All,

Can someone explain pros/cons to using surrogate keys(i.e.,sequences) vs non-surrogate keys?

Thanks
Rick
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Cale, Rick T (Richard)
INET: RICHARD.T.CALE_at_saic.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Christopher Spence
INET: cspence_at_FuelSpot.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------