Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2009
    Posts
    42

    Unanswered: keys and indexes

    How do you decide when to use primary key vs. unique key and primary index vs. unique index?

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    A primary key must have a unique index on the same columns. If one doesn't already exist, DB2 will create one when then PK is created.

    Functionly within DB2 there is usually not much difference between a Uniquie Constraint and a Primary Key Constraint (with some minor exceptions). The difference has more to do with the theory of data modeling and Third Normal Form design. A table can have only one PK, but multiple Unique Constraints.
    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
    Nov 2011
    Posts
    334
    one difference is pk's column must be defined as not null。
    And I also remember ( but not sure ), DB2 tries to organize table's data with the same sequence of pk, that means db2 always try to keep a relative higher clustor ratio of PK when there is not any other cluster index on the table.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by fengsun2 View Post
    one difference is pk's column must be defined as not null。
    And I also remember ( but not sure ), DB2 tries to organize table's data with the same sequence of pk, that means db2 always try to keep a relative higher clustor ratio of PK when there is not any other cluster index on the table.
    Not in DB2 LUW. If no clustering index is defined, then DB2 does not order the rows for inserts or reorg (unless a specific index is named during a reorg).

    On DB2 z/OS the first index defined is used as the clustering index, unless another index is specifically chosen by the DBA. Usually the PK index is the first one defined, so maybe that is what you were thinking. There may be an override available in newer versions of DB2 z/OS so there is not any default clustering index, but not sure about that.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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