Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2008
    Posts
    1

    Question Unanswered: unique id for rows across a database

    Can we have a unique identifier for each and every row of data across a database? Is it a good practice to have one? (i dont think it is, but would like opinions).
    I am referring to DB2 database, if that makes any difference
    Last edited by vradhik; 11-13-08 at 02:05.

  2. #2
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    for every row of data??? you mean for every row in a user created table in the database??? Now why would you require that...
    IBM Certified Database Associate, DB2 9 for LUW

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You can use a sequence, which is like an indentity column, but is not tied to any one table.

    create sequence <sequence-name>; (run only once)

    When you insert data into tables, it looks like this:

    Insert into table_A values (nextval for <sequence-name>, current_timestamp, etc, etc,);

    Insert into table_B values (nextval for <sequence-name>, 'Y', 24, current_date, etc, etc,);

    Whether or not this is a good idea has nothing to do with DB2, it is more related to your database design. It is not considered to be 3rd normal form, but there may be some reason to use it in some limited cases.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    The result of the generate_unique() function is guaranteed to be unique within the DB2 instance, so if all of your databases belong to the same instance you could use that function.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    To answer your other question, I do not think it is a good practice. The problem most times is that you use this unique number as the primary unique key and the application really doesn't know what it is. So, you end up with an index to support the uniqueness and it is never used in a query, because your users do not know the numbers as input values. I, personally, stay away from this practice of creating some dumb number and use the natural key of the table.

Posting Permissions

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