Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2004
    Posts
    33

    Arrow Unanswered: PK or unique index???

    Hi all,

    Is it proper to create an unique index to serve the purpose of
    a PK? If so, why is that so and what's the advantage/disadvantage between the 2 options?

    Thanks
    ME

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you create a PK, DB2 will see if there is a unique index on the key already created. If not, DB2 will create the unique index using its own naming convention. If the PK is dropped, the DB2 created unique index will also be dropped.

    If you create your own unique index before defining the PK (with alter table), then you can choose the index name. Such an index will not be dropped if the PK is dropped.

    If you want to create a foreign key relationship on another table that points to the primary key on the parent table, then you would probably want to explicitly define the primary key (with either a DB2 created unique index or a DBA created unique index). If no foreign keys will be defined, then a DBA created unique index without a PK serves the same purpose.
    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
    Mar 2004
    Posts
    33
    Hi Marc,

    So, is it appropreiate to say that PK in DB2 is actually implemented by
    an unique index?

    ME

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Yes. But I believe that if you try to insert a duplicate row, you will get a different error message depending on whether a PK or just a unique index is defined.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Feb 2004
    Location
    Copenhagen
    Posts
    220
    As I understand it, a Primary Key is simply a special case
    of a unique index, you can have many unique indexes
    on a table, if they specify different condition, but you can
    only have one (1!) primary key.

    Thus, when creating a primay key, you are implicitly
    creating a unique index, but not vice versa.

    BOW
    Kristian K. Hansen
    Project Supervisor
    National Board of Health

  6. #6
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Originally posted by Tank
    As I understand it, a Primary Key is simply a special case
    of a unique index, you can have many unique indexes
    on a table, if they specify different condition, but you can
    only have one (1!) primary key.

    Thus, when creating a primay key, you are implicitly
    creating a unique index, but not vice versa.

    BOW
    Correct... it's a database design thing more than anything. A table can have many candidate keys, the primary key is the one designated to identify a row in the table.

    For instance - when I was building my house, the builder had a unique lot number assigned to it, and then I also had a street address. Both uniquely identify my house, but only one can be the primary key. The other would certainly be a candidate key, and may even function in a very similar manner.
    --
    Jonathan Petruk
    DB2 Database Consultant

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    One thing to remember is that Referential Integrity was not in the original DB2 specification. It was introduced about 1988 for DB2 OS/390 (MVS back then). Prior to that, we just created a unique index on the parent table and had the application validate undeclared “referential constraints” via program code when updating the dependent table. This is still an option that performs quite well if you trust the application developers to do it correctly.

    More recently, UNIQUE constraints have been introduced.

    If all these concepts had been available from the beginning, it might have been implemented a little differently via DDL to make things more clear.
    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
    Mar 2004
    Posts
    448
    There is also a subtle difference.
    You can have one NULL value in the unique index column ,but no two NULLS,because two NULLs are not unique.

    If you want to define primary key on a column then thus column must be defined NOT NULL,so when you define a primary key it uses two constraints NOL NULL and UNIQUE.

    so if a column has unique index,but has one null value you can not define primary key on that one.

    How DB2 uses unique index to support primary key constraint is also a very interesting topic, totally depends upon the index structure.

    Infact in Oracle a non-unique index can be used as primary key constraint , interesting!!!!


    Regards,

    Mujeeb

Posting Permissions

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