Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2003
    Location
    Canada
    Posts
    35

    Question Unanswered: Differences between Unique key and Primary Key

    I would like to known the difference between a unique key and a primary key (as a constraint e.i. not the indexes)?

    I am using DB2 v8 and I don't see the difference ... except the fact that their can be only one primary key per table.

    Thanks,

    Dany

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The original implementation of Referential Integrity only allowed for Primary Keys and Foreign Keys. As you mentioned, only one Primary key was allowed per table. A Foreign Key must always reference a Primary Key on another Table. This is in keeping with standard 3rd normal form design. Referential Integrity was first implemented in DB2 (mainframe) with version 2.1 (If I recall it was about 1988).

    It was later recognized that someone might want a Foreign Key to reference a column on another table that was not the Primary Key. This might occur if the database was slightly de-normalized and the Unique Key had no table or its own. As you mentioned, multiple Unique Keys may be defined.

    In addition, someone might want to guarantee the uniqueness of a column. In DB2, a unique index is automatically created for each Unique Key to ensure uniqueness. Simply creating a unique index with explicit DDL would basically accomplish the same thing. But the Unique Key is a more standard approach to defining such a constraint.
    Last edited by Marcus_A; 07-01-03 at 17:00.

  3. #3
    Join Date
    Nov 2002
    Posts
    14

    Re: Differences between Unique key and Primary Key

    The other difference is that a primary key cannot be NULL, whereas a Unique index may allow NULLs

    Originally posted by dlafreni
    I would like to known the difference between a unique key and a primary key (as a constraint e.i. not the indexes)?

    I am using DB2 v8 and I don't see the difference ... except the fact that their can be only one primary key per table.

    Thanks,

    Dany

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    However, a Unique Constraint (or Unique Key) does not allow nulls, just like a Primary Key does not allow nulls.

  5. #5
    Join Date
    Mar 2003
    Location
    Singapore
    Posts
    200
    A Primary key that allows Null is a Unique Key

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I think we are confusing a UNIQUE constraint (which is shorthand in DB2 for a UNIQUE Key) with a Unique Index.

    When a UNIQUE constraint is created, the identified columns must be defined as NOT NULL.

    When a UNIQUE constraint is created, it may be used by a Referential Constraint on another table (when a FOREIGN KEY refers back to the UNIQUE constraint on the Parent Table). Thus, for referential integrity purposes, an UNIQUE constraint can function like a PRIMARY KEY.

    When a UNIQUE constraint is defined, DB2 automatically creates a Unique Index on the columns defined in the UNIQUE constraint.

    A Unique Index that is explicitly created by a DBA with DDL (CREATE INDEX), may be created on columns which allow Nulls. This is not the same as a UNIQUE Key.

Posting Permissions

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