artificially generated primary key


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 7 of 7

Thread: artificially generated primary key

  1. #1
    Naveen Guest

    artificially generated primary key


    Can some one explain the use of an artificially generated primary key such
    as a sequential number which by itself has no meaning.

    The only use that I can think up for this is enforcing referential integrity.
    We cannot even use this for joins.

    I use an existing database column or columns to come up with a primary key.
    This serves me well for cases where I am inserting data into a database table
    and an attempt to insert duplicate data would get detected and cancelled.

    On the other hand if I were to use an artificially generated primary key
    the duplicate row would automatically have the next number in the sequence
    for primary key and the row would get inserted even though it already exists.

    Therefore can I conclude that an artificially generated primary key has very
    limited use.

    I welcome your comments on the two issues I have raised here.

    Thanks in advance

    Naveen

  2. #2
    JasonL Guest

    Re: artificially generated primary key

    Naveen <thalanki_naveen@rocketmail.com> wrote in message
    news:3946965f$1@news.devx.com...
    >
    > Can some one explain the use of an artificially generated primary key such
    > as a sequential number which by itself has no meaning.


    Using more than one column can slow down performance with joins, etc. Using
    an existing column can sometimes provide major problems if business rules
    change, etc.
    check out this article for more :
    http://www.sqlmag.com/Articles/Index...leID=5113&pg=2

    > On the other hand if I were to use an artificially generated primary key
    > the duplicate row would automatically have the next number in the sequence
    > for primary key and the row would get inserted even though it already

    exists.

    You can still use a unique constraint (in SQL server) or a unique index (in
    MS Access) on columns which need to stay unique. Then if business rules
    change you can drop/delete these constraints and any tables that reference
    your PK aren't affected.

    HTH
    -Jason



  3. #3
    D. Patrick Hoerter Guest

    Re: artificially generated primary key

    Naveen,

    An arbitrary value applied as a key is referred to as a "surrogate key".
    These values do indeed have tremendous usage in relational database systems.
    For example, let's say you have a customer table, and would like to do
    without some complex situation where LastName, FirstName, Street, City,
    State, and Zip make up a Primary Key for your joins. You'd also like a
    "CustomerID". This is a perfect candidate for a surrogate key, and can then
    be used in other tables as a foreign key.

    Other uses are in places like lookup or "code" tables, such as
    CustomerType, or Product. Instead of using CustomerType or ProductName all
    over the place, you give each value in the lookup table an arbitrary ID
    value, such as CustomerTypeID, or ProductID. This prevents update anomalies
    that can arise by having data values distributed all over the place, and
    also has the benefit of saving disk space. An integer is a four-byte column,
    where something like char, varchar, or nvarchar would waste a tremendous
    amount of space, both in table and index usage.

    Although not a "pure" relational database concept, the surrogate key has
    a very useful and necessary place in the scheme of things.

    Regards,
    D. Patrick Hoerter

    Naveen wrote in message <3946965f$1@news.devx.com>...
    >
    >Can some one explain the use of an artificially generated primary key such
    >as a sequential number which by itself has no meaning.
    >
    >The only use that I can think up for this is enforcing referential

    integrity.
    >We cannot even use this for joins.
    >
    >I use an existing database column or columns to come up with a primary key.
    >This serves me well for cases where I am inserting data into a database

    table
    >and an attempt to insert duplicate data would get detected and cancelled.
    >
    >On the other hand if I were to use an artificially generated primary key
    >the duplicate row would automatically have the next number in the sequence
    >for primary key and the row would get inserted even though it already

    exists.
    >
    >Therefore can I conclude that an artificially generated primary key has

    very
    >limited use.
    >
    >I welcome your comments on the two issues I have raised here.
    >
    >Thanks in advance
    >
    >Naveen



  4. #4
    Keith Guest

    Re: artificially generated primary key


    I agree 100%. Why use a stupid autogenerated number while you have perfectly
    good candidate columns for a primary key. May cause a tab more code to open
    forms etc... Just doesn't make any sense to me ???

    "Naveen" <thalanki_naveen@rocketmail.com> wrote:
    >
    >Can some one explain the use of an artificially generated primary key such
    >as a sequential number which by itself has no meaning.
    >
    >The only use that I can think up for this is enforcing referential integrity.
    >We cannot even use this for joins.
    >
    >I use an existing database column or columns to come up with a primary key.
    >This serves me well for cases where I am inserting data into a database

    table
    >and an attempt to insert duplicate data would get detected and cancelled.
    >
    >On the other hand if I were to use an artificially generated primary key
    >the duplicate row would automatically have the next number in the sequence
    >for primary key and the row would get inserted even though it already exists.
    >
    >Therefore can I conclude that an artificially generated primary key has

    very
    >limited use.
    >
    >I welcome your comments on the two issues I have raised here.
    >
    >Thanks in advance
    >
    >Naveen



  5. #5
    JasonL Guest

    Re: artificially generated primary key


    Keith <cournek@telusplanet.net> wrote :
    > I agree 100%. Why use a stupid autogenerated number while you have

    perfectly
    > good candidate columns for a primary key. May cause a tab more code to

    open
    > forms etc... Just doesn't make any sense to me ???
    >

    I'll reiterate and perhaps clarify my previous reply. First, often the
    'perfectly good' PK candidate for a table are multiple columns, which can
    cause performance to suffer (the engine has to do several scans on the
    tables to find matches, and do some reading on clustered vs. non-clustered
    indexes). Also the best candidate for a PK from the performance side is an
    integer (which usually 'perfectly good candidates' are not.).
    Also, what happens when business rules affecting a 'perfectly good
    candidate' change? I'll give you an example. I had a manufacturing database
    with 30 or 40 tables. The logical PK of the 'main' table was the work order
    number. Almost all of the tables referenced this 'main' table so I made the
    work order no the PK in main and a FK in all these others. To be efficient I
    stored that value in numeric datatype since at the time the business rule
    was numeric characters only. Two years later, that rule changed. The number
    of tables had grown and so I had to change the datatype of the column in
    about 50/60 tables. That was a pain. Since then I use what are called
    'surrogate' keys (or what you've called "stupid autogenerated number").
    -Jason




  6. #6
    D. Patrick Hoerter Guest

    Re: artificially generated primary key

    Keith,

    I can see that the number of books you've read exceeds the number of
    production databases you've built.

    >I agree 100%. Why use a stupid autogenerated number while you have

    perfectly
    >good candidate columns for a primary key. May cause a tab more code to

    open
    >forms etc... Just doesn't make any sense to me ???


    Oh, let's see...maintainability, extensibility, disk space consumption
    issues, tedious queries, poor performance.....

    There is nothing wrong with surrogate keys. Try writing a five-table
    join where all of the tables have 10-column keys. I'll be done with my app
    before you debug your join problems. Not to mention that a surrogate key as
    a foreign key only costs 4 bytes per row.

    Regards,
    D. Patrick Hoerter

    Keith wrote in message <394ade28$1@news.devx.com>...
    >
    >I agree 100%. Why use a stupid autogenerated number while you have

    perfectly
    >good candidate columns for a primary key. May cause a tab more code to

    open
    >forms etc... Just doesn't make any sense to me ???
    >
    >"Naveen" <thalanki_naveen@rocketmail.com> wrote:
    >>
    >>Can some one explain the use of an artificially generated primary key such
    >>as a sequential number which by itself has no meaning.
    >>
    >>The only use that I can think up for this is enforcing referential

    integrity.
    >>We cannot even use this for joins.
    >>
    >>I use an existing database column or columns to come up with a primary

    key.
    >>This serves me well for cases where I am inserting data into a database

    >table
    >>and an attempt to insert duplicate data would get detected and cancelled.
    >>
    >>On the other hand if I were to use an artificially generated primary key
    >>the duplicate row would automatically have the next number in the sequence
    >>for primary key and the row would get inserted even though it already

    exists.
    >>
    >>Therefore can I conclude that an artificially generated primary key has

    >very
    >>limited use.
    >>
    >>I welcome your comments on the two issues I have raised here.
    >>
    >>Thanks in advance
    >>
    >>Naveen

    >



  7. #7
    PP Guest

    Re: artificially generated primary key


    Both Natural & Surrogate keys have their respective places in database design.
    'ISBN','National Insurance', 'vehicle number plates' .... would probably
    be very good natural keys as they should not be duplicated. Surrogate keys
    tend to be a better choice for tables where the business rules are likely
    to change.

    I tend to use a combination of both when I design and generally use the following
    rules of thumb (with examples):

    For Natural Keys
    ----------------
    Batch Processing:
    If a lot of batch processing is going to occur then the clustering on the
    natural key tends to prove to be the fastest method. Especially if the rows
    are interlinked ie ORDER might have a surrogate key whilst the ORDER_ITEM
    would have the OrderID,OrderItem_LineNo as a clustered PKey as an intelligent
    key or a composite-surrogate key. This helps with most queries as scanning
    is virtually reduced to the necessary rows.

    Business Rules in Triggers:
    If there are going to be a lot of nested triggers being fired then the @@identity
    can prove to be too problematic. If you need to return the identity then
    you will need to requery the table looking for a max value defined ironically
    by a natural composite key. In OLTP you can't always rely on the max(identity
    column) as other users might have inserted a number of rows and transactions
    cause too much locking.


    Very deep relationships:
    With 9 levels of joins from the parent to the great great... grandchild then
    a composite natural key will enable a query with one set of joins to reach
    from
    great great... grandchild to the parent. I once was called in to troubleshoot
    a very slow database with simple surrogate keys on every single table. The
    locks needed by a business rule were held for 20s causing too many deadlocks,
    the change to natural composite key caused it to run in sub 1s. hmmm big
    headache updating the schema but virtually removed all deadlocks from that
    part of the database.

    Hot Spots
    A natural clustered key can remove the risk of hot-spots causing locking
    at the end of the table. Very useful if 400 users are hammering data in and
    their work load is naturally identified within the Primary key.



    For Surrogate Keys
    ------------------
    Uncertain Requirements:
    If I'm certain that the client isn't certain. A job number being numeric
    99.99% of the time but with a prefix on all the other occassions. Less impact
    analysis is needed when the customer informs you after all the development
    and testing has been signed off and a very tight deadline is looming.

    Fluid Business Rules:
    The comment of we will never have a product code over 4 characters this centuary.
    (HA!! Is this a new millinum!) Then suddenly sales and marketing have a brain
    wave.

    Intergration:
    Importing from third parties systems, especially where duplicates need to
    be intelligently removed. Use of an identity column will always make a record
    unique.



    Conclusion
    ----------
    Choosing a primary key to be:
    natural(intelligent) vs surrogate
    simple vs composite
    I tend to opt for the solution that increases the database performance ie
    speed and the lack of deadlocks and locks. There is no perfect database design
    and being too dogmatic about using one method alone will not always win out.

    Hope the above has helped you.






    "Naveen" <thalanki_naveen@rocketmail.com> wrote:
    >
    >Can some one explain the use of an artificially generated primary key such
    >as a sequential number which by itself has no meaning.
    >
    >The only use that I can think up for this is enforcing referential integrity.
    >We cannot even use this for joins.
    >
    >I use an existing database column or columns to come up with a primary key.
    >This serves me well for cases where I am inserting data into a database

    table
    >and an attempt to insert duplicate data would get detected and cancelled.
    >
    >On the other hand if I were to use an artificially generated primary key
    >the duplicate row would automatically have the next number in the sequence
    >for primary key and the row would get inserted even though it already exists.
    >
    >Therefore can I conclude that an artificially generated primary key has

    very
    >limited use.
    >
    >I welcome your comments on the two issues I have raised here.
    >
    >Thanks in advance
    >
    >Naveen



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center