Results 1 to 13 of 13
  1. #1
    Join Date
    Aug 2007
    Posts
    28

    Arrow Unanswered: Surrogate key vs Primary key, questions

    Hello everyone.

    Whats the difference between a surrogate key and a primary key?

    Every time you insert a record into a table a the new record's primary key gets incremented, so every record has a unique number.

    To me, a surrogate key sounds exactly like a primary key, every time you insert a new record into a table the surrogate key is incremented.

    So whats the difference?



    Another question is:

    I was reading up on surrogate keys and I was confused on this line of code:
    Code:
    CREATE SEQUENCE EMPSERIAL
    AS BIGINT
    START WITH 1
    INCREMENT BY 1
    Where do you put this script? I realize you put the creation of tables in a .DDL file, so while your creating tables, do you just put this at the top of the file or what?


    he reason to use a surrogate key solution is to find an efficient way to generate unique sequential numbers. This article describes three implementations:

    * Using the traditional method
    * Using key manager
    * Using DB2 UDB features

    A surrogate key is also called an internal key. When you create a table, you can add an extra column to be the surrogate key. This column should be NOT NULL, and will have no business meaning. This surrogate column can be designated the primary key column. A simple example is to have a numeric surrogate column. The surrogate key values start with a number, for example "1", for the first row of the table, and is incremented by one for each row thereafter.

    SEQUENCE object

    Solution idea

    The SEQUENCE object is a feature that was introduced in DB2 UDB Version 7.2. Users can create a SEQUENCE object in the database, just like creating a table object or a view object, and then request the values from the SEQUENCE. DB2 guarantees that users get a unique sequence value every time.

    Example

    You can create a SEQUENCE object in the database:

    CREATE SEQUENCE EMPSERIAL
    AS BIGINT
    START WITH 1
    INCREMENT BY 1


    If you have the EMPLOYEE table as the following:

    CREATE TABLE EMPLOYEE ( SERIALNUMBER BIGINT NOT NULL,
    FIRSTNAME CHAR(64),
    LASTNAME CHAR(64),
    SALARY DECIMAL(10, 2),
    PRIMARY KEY (SERIALNUMBER))


    You can insert a row with the following statement:

    INSERT INTO EMPLOYEE ( SERIALNUMBER, FIRSTNAME, LASTNAME,
    SALARY) VALUES(NEXTVAL FOR EMPSERIAL, 'John', 'Smith', 99.99)


    Here you use the "NEXTVAL FOR EMPSERIAL" to obtain the unique value from the SEQUENCE.


    THanks!
    IBM: Tivoli zSeries Co-op

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you want DB2 to generate the primary key value for you, then it is easier to just define the column as "SERIALNUMBER integer not null gernerated by default as identity". You can also use some other data types such bigint or decimal.

    I prefer generated by default instead of generated always, because it makes it easier to load data back into the table with the generated key, if for example they get accidently deleted and you have to get the deleted rows from a backup. Of course, if you use generated by default, you must rely on your programmers to not put their own values in the column on an insert.

    To insert rows that will generate the key for you, use either of the following:

    INSERT INTO EMPLOYEE (SERIALNUMBER, FIRSTNAME, LASTNAME, SALARY) VALUES(DEFAULT, 'John', 'Smith', 99.99);

    INSERT INTO EMPLOYEE (FIRSTNAME, LASTNAME, SALARY) VALUES('John', 'Smith', 99.99);

    I would forget about using sequences.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Aug 2007
    Posts
    28
    Thanks for the response.

    Well from your reply when you use DEFAULT, doesn't this mean your not using a surrogate key at all but rather your just letting Serial number act as a primary key and increment 1 by 1 every time a new record is added?


    I'm not understanding the difference between a surrogate key and a primary key, from what you showed and from what I read they are the same exact thing.

    Even if you look at the code:
    Code:
    CREATE TABLE EMPLOYEE ( SERIALNUMBER BIGINT NOT NULL,
    FIRSTNAME CHAR(64),
    LASTNAME CHAR(64),
    SALARY DECIMAL(10, 2),
    PRIMARY KEY (SERIALNUMBER))
    Its using SERIALNUMBER as primary key, the only difference I saw between a regular primary key and a surrogate key was this code segment:

    Code:
    CREATE SEQUENCE EMPSERIAL
    AS BIGINT
    START WITH 1
    INCREMENT BY 1
    But if we use default value, you basically just have a primary key right?
    IBM: Tivoli zSeries Co-op

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    The two terms you are confusing are Natural Key, and Surrogate Key

    A natural key is a field or fields, which, by itself, will serve well as a primary key. Consider an inventory system, for instance. You might have a parts description table, where the natural key for the table is the Part Number. There's not a compelling reason to add a surrogate key to this table.

    A surrogate key is employed when there is either NO natural key available, or you wish to add a surrogate (replacement) key for performance or convenience. For instance, in an inventory system, you might have an inventory location table, with Part Number, Facility Code, Building, Aisle, Row, and Bin ALL forming a candidate composite primary key. However, if there are other tables which have a foreign key relationship to this table, you would probably find that requiring 6 fields to be used in both tables to be a bit of a headache. In this case, adding a surrogate primary key (identity) would be very practical.

    Please note that a surrogate key adds overhead when you have an existing natural key. Not only are you adding the storage space for the key, you add the overhead to create a unique key, and the index to support it. (And, you will often STILL need a unique key defined to support the natural key anyway.) As with many design issues, you must trade off memory, performance, and convenience.
    Last edited by loquin; 11-03-07 at 03:19.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mr_coffee
    To me, a surrogate key sounds exactly like a primary key, every time you insert a new record into a table the surrogate key is incremented.
    that's correct

    a surrogate key, when used, is almost always used as the primary key (in fact, i cannot think of any situation where you'd have a surrogate key that isn't used as the primary key)

    but the hint is in the name itself -- surrogate

    it is standing in for a "real" primary key, a natural key

    the important part of loquin's post is:
    you will often STILL need a unique key defined to support the natural key anyway
    when you design database tables, always start out by identifying the natural keys

    surrogate keys are often convenient, but they are seldom absolutely necessary
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    As mentioned above by others, you are confusing the terms surrogate key and primary key. Surrogate key is one type of primary key (the other is natural key).

    For an employee table, a natural key might be social security number. Even though this is supposed to be unique (unless you hire illegal aliens), it has some "meaning" and is not a random number.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Aug 2007
    Posts
    28
    Ahh thanks for the clear up guys!

    It seems I will be needing to use a surrogate key rather than a natural primary key becuase what I need to map are objects, every object must be unique and has no natural key so this looks like my best bet.

    Thanks again!
    IBM: Tivoli zSeries Co-op

  8. #8
    Join Date
    Oct 2007
    Posts
    6
    word of caution . social security number will be a bad primary key since some workers may not have one, but instead tax id number. This makes this example even more appropriated to illustrate the surrogate key. Instead of using social security number you can create a key , say employee_num. which will be unique for each employee. The employee_num becomes your surrogate key.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mr_coffee
    every object must be unique and has no natural key
    how will you know if you accidentally insert a duplicate row?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Aug 2007
    Posts
    28
    I'm new to SQL and db2 but how would that occur?

    If a program is inserting the values, not someone manually, and you iterate through the objects, inserting them into the database it shouldn't occur unless the programmer (me) did something incorrect in the iteration.

    I'm sure I could add some logic to look at the previous row before inserting and do a roll back if the primary keys are the same but that would def. slow down performance.
    IBM: Tivoli zSeries Co-op

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mr_coffee
    I'm sure I could add some logic to look at the previous row before inserting and do a roll back if the primary keys are the same
    but the primary key is a surrogate, which is assigned as each row is inserted, so of course it's going to be different on every row

    my point was, if there is "no natural key" then you cannot tell one row from the next and therefore you could easily have dupes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Aug 2007
    Posts
    28
    I do see what your saying....

    What do you do in a case like this?
    IBM: Tivoli zSeries Co-op

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mr_coffee
    What do you do in a case like this?
    me? i go back to the developer and tell her she can't have a row if she can't identify it uniquely based on its contents

    in other words, every table has at least one natural key, and if it doesn't, it doesn't get defined in my database

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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