Home Forums Database Design Relational Theory Surogate Keys are not always the answer but are freqently used RE: Surogate Keys are not always the answer but are freqently used

  • Welsh Corgi (3/22/2010)


    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://www.dbdebunk.com/page/page/626995.htm

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

    🙂

    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