Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2003
    Location
    canada
    Posts
    230

    Unanswered: What is different between primary index and unique index

    Hi All,
    I am working with db2 v8.2 aix 5.2.

    Recently I be forced to change some unique index to primary index (I am using insert_update cluse in my import statement) , I want to know what is different between unique index and primary index. can we have null value in unique index? I know we can not have null value in primary index.

    thank you in advance for your help.

  2. #2
    Join Date
    Aug 2004
    Posts
    330
    If you want to have nulls in a unique index, use the "WHERE NOT NULL" clause in your create index statement.

  3. #3
    Join Date
    Jan 2005
    Posts
    191
    Technically there's no such thing as a "primary index". However, in DB2, you have to have a unique index to guarantee the uniqueness of a primary key. When you define a primary key, DB2 LUW checks to see if an appropriate unique index exists. If one doesn't then DB2 creates a suitable index. If a suitable unique index exists then DB2 uses it.


    James Campbell

  4. #4
    Join Date
    Feb 2004
    Posts
    25
    We only define a primary key if we are going to use the that table as the parent in an RI rule.

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by urquel
    If you want to have nulls in a unique index, use the "WHERE NOT NULL" clause in your create index statement.
    WHERE NOT NULL is an option for create index on DB2 for OS/390 and z/OS (maybe DB2 for I-series ??), but is not available on DB2 for LUW.

    You can have a null in a unique index on DB2 for LUW (assuming the column is nullable), but you can only have one of them.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Aug 2004
    Posts
    330
    Quote Originally Posted by Marcus_A
    WHERE NOT NULL is an option for create index on DB2 for OS/390 and z/OS (maybe DB2 for I-series ??), but is not available on DB2 for LUW.

    You can have a null in a unique index on DB2 for LUW (assuming the column is nullable), but you can only have one of them.

    Interesting! So if you want to enforce an optional, non-identifying, one-to-one foreign key relationship on LUW, would it require triggers?
    Last edited by urquel; 02-24-05 at 15:06.

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Triggers are one way to that (which I have seen done by one of my customers).

    Another way is to create a new parent table that contain the unique values that are not null and establish a RI constraint. RI constraints will allow nulls in the child table (foreign key).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by crisscross
    We only define a primary key if we are going to use the that table as the parent in an RI rule.
    I feel sorry for the organization that pays your salary.

  9. #9
    Join Date
    Aug 2004
    Posts
    330
    Quote Originally Posted by Marcus_A
    Triggers are one way to that (which I have seen done by one of my customers).

    Another way is to create a new parent table that contain the unique values that are not null and establish a RI constraint. RI constraints will allow nulls in the child table (foreign key).
    The creation of a parent table and RI constraint will enforce the optional one-to-many relationship but you would need a UNIQUE WHERE NOT NULL or a trigger to enforce the optional one-to-one.

Posting Permissions

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