Results 1 to 13 of 13
  1. #1
    Join Date
    Apr 2007
    Posts
    3

    Unanswered: Unique Index vs. Primary Key

    On our data warehouse tables we typically (if not always) create a unique index over the column(s) that make up the Primary Key in a table in addition to altering the table and adding the Primary Key.

    I wonder if this is redundantly taking up disk space or not?

    The reason I ask is these unique indexes that are identical to the PK appear to be never used. The PK is always chosen as an index by the queries over the unique index. Perhaps an exception to this may be if a reorg is done and the unique index is directly specified.

    If storage is redundant and the PK is always used over the identical index, is there any reason to have both?

  2. #2
    Join Date
    Jun 2006
    Posts
    471
    a primary key implicitly creates a unique index
    if you create the unique index first and then the pk it will indicate it uses the existing unique index
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    When you create the PK after a index has been already created on the same columns, then DB2 will give you a warning message that it is using the existing index and converting it to a PK index. So there should only be one index that exists if the columns are the same.

    This is sometimes useful, since it is the only way to indicate that an index is clustered, or to specify the perecent free (other than default) on an index for the PK columns. This is because you cannot alter an index in DB2 LUW (unlike DB2 for z/OS).
    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
    Jan 2003
    Posts
    1,605
    I use the method Marcus_A has specified to include additional columns in index. For example create unique index db2admin.index_name on db2admin.table (col1_pk, col2_pk) include (col3). After then I create pk on columns col1_pk, col2_pk. DB2 uses the unique index. I have used this "include" command to increase performance - I remember I have reduced SQL execution time from 15 minutes to 5 seconds.

    Back to the main topic:
    - unique index: null value possible (only one row in table), referential integrity not possible (foreign keys can't be assigned)
    - unique value (create table db2admin.tab1 (col1 int not null unique): null not possible, referential integrity possible, multiple unique values in one table possible (which can have multiple columns)
    - primary key: null not possible, referential integrity possible, only one primary key per table (which can have multiple columns)

    Hope this helps,
    Grofaty

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by grofaty
    Back to the main topic:
    - unique index: null value possible (only one row in table), referential integrity not possible (foreign keys can't be assigned)
    Correct: foreign keys can only refer to primary keys or unique constraints. Those are all logical constructs whereas an index is a physical thing

    - unique value (create table db2admin.tab1 (col1 int not null unique): null not possible, referential integrity possible, multiple unique values in one table possible (which can have multiple columns)
    These are "unique constraints", which can also be defined like this (which is cleaner IMHO):
    Code:
    CREATE TABLE t (
       col1 INT NOT NULL,
       col2 VARCHAR(20) NOT NULL,
       ...,
       CONSTRAINT col1_unique UNIQUE ( col1, col2 )
    - primary key: null not possible, referential integrity possible, only one primary key per table (which can have multiple columns)
    A primary key is the same as any other unique constraint, except that it has a different name.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by stolze
    A primary key is the same as any other unique constraint, except that it has a different name.
    And the other difference being that you can refer to it from a foreign key by just specifying the table (without column specification).
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by stolze
    A primary key is the same as any other unique constraint, except that it has a different name.
    wait a sec -- does DB2 allow unique constraints on nullable columns, or not?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jan 2003
    Posts
    1,605
    Quote Originally Posted by stolze
    A primary key is the same as any other unique constraint, except that it has a different name.
    Not entirely true. You can have only one primary key on table but multiple unique constraints. Main purpose of primary key and foreign keys are to have a referencial integrity. Main purpose of unique constraint is to have unique value in table. Although they seams to be functioning equally they have different purpose.
    Last edited by grofaty; 04-24-07 at 02:30.

  9. #9
    Join Date
    Jan 2003
    Posts
    1,605
    Quote Originally Posted by r937
    wait a sec -- does DB2 allow unique constraints on nullable columns, or not?
    Unique constraints can't be assigned to nullable columns. But if you apply unique index (instead of unique constraints) nulls are allowed only for one record. So there is a different between unique constraint and unique index.

    If we return to main question in this topic (performance unique index vs. primary key) I would suggest primary key used instead of unique index because of:
    - if nulls are created on columns and the nulls are not needed there can be some disk space preserved, because null column definition requires more disk space for each column defined as null,
    - if primary key is defined you have ability to assign foreign keys - foreign keys can't be assigned only on unique indexes - it is suggested to apply foreign keys on warehouse too, to have a referencial integrity - don't know for your case, but I have been working on warehouse projects that have used software tools that referencial integrity was mandatory
    Last edited by grofaty; 04-24-07 at 02:32.

  10. #10
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by grofaty
    Main purpose of unique constraint is to have unique value in table.
    Not entirely true. You can refer to any so-called "alternate" key from a foreign key; in DB2 alternate keys are nothing else than unique constraints.
    So, in order to define a foreign key pointing to anything other than the primary key of a table, that column (combination) must be defined as being a "unique constraint", i.e., alternate key.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  11. #11
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by Peter.Vanroose
    And the other difference being that you can refer to it from a foreign key by just specifying the table (without column specification).
    Right, but that's just syntactic sugar...
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  12. #12
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by r937
    wait a sec -- does DB2 allow unique constraints on nullable columns, or not?
    No, columns in a unique constraint must be declared as NOT NULL. (That's a difference to unique indexes. But then, a unique index is an index and not a constraint.)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  13. #13
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by grofaty
    Not entirely true. You can have only one primary key on table but multiple unique constraints. Main purpose of primary key and foreign keys are to have a referencial integrity. Main purpose of unique constraint is to have unique value in table. Although they seams to be functioning equally they have different purpose.
    No, they are the same functionality-wise. A FK can refer to a unique constraint, too. So the only differences are:
    • PK has a different name,
    • they are the Highlander in each table (there can be only one), and
    • FK can refer to PKs w/o the need that columns are specified.

    So for all practical purposes, we are only talking about syntactical differences and semantics are the same.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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