DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Surrogate key

  1. #1
    Join Date
    Feb 2006
    Posts
    162

    Surrogate key

    Hi,

    Can anyone explain how to implement surrogate key concept in Oracle tables?

    Thanks...

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    I'm not a fan of surrogate keys but evidence shows they are here to stay.
    Just take numbers from an Oracle Sequence and populate your key with it.
    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.

  3. #3
    Join Date
    Jan 2001
    Posts
    2,828
    hi Pavb

    Whats wrong with surrogate keys

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    A surrogate key is nothing but an artificially created PK meaning, you should have a column or group of columns in the affected table already giving you the uniqueness you are looking for.

    If you cannot find an unique identifier -column or group of columns- in the affected table I would say that there is a serious issue with your design.

    Surrogate Keys are meaningless from the business perspective.
    Surrogate Keys are allocating additional space in your table.
    Surrogate Keys require you to create a specific process to populate them e.g.: we are adding a level of complexity.

    Why would you want to face all these issues when you can easily build a PK using the actual information already sitting in your affected table?

    That being said... they are here to stay. Sad, isn't it? : )
    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.

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Quote Originally Posted by PAVB
    That being said... they are here to stay. Sad, isn't it? : )
    Not really, if you have a natural key that is prone to change, then using a surrogate key will prevent you from having to cascade the update of that key. But sequences can certainly add overhead to large transactions.

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    I have to agree, you have a point there.

    #$%@ I'll have to stop my "de-support sequences campaign"

    Either way, I'll keep trying to avoid surrogate keys as much as I can.
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width