Diff B/W PK and Unique Index.
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Diff B/W PK and Unique Index.

  1. #1
    Join Date
    Dec 2000
    Location
    Virginia, USA
    Posts
    455
    Simple Question...

    What is the difference between Unique Index and Primary Key. Primary Key also creates the Index.

    Examples would be appreciated.

    Thanks In Advance.

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    A Pk is often supported by a unique index.

    The theoretical difference is slight. A table can must have at least 1 candidate key. A candidate key is a column or set of columns that can be used to uniquely identify a row. Of course, a table can have multiple candidate keys as well. Each candidate key can and probably should be enforced via a unique index. Only 1 of the candidate keys can be the Primary (candidate) Key, hence the name. When relationships are made from that table, it is the primary key that is used to enforce the relationship.

    For example, in a table Dept, both the department ID and the department name may be candidate keys. The Department ID will likely be chosen as the Primary Key with the department name then simply becoming an Alternate (candidate) Key. Both would be enforced via unique indexes.

    In the Emp table, we store the department that the emp works in. There is, therefore, a relationship between emp and Dept. This relationship is many-1 (m:1), and such a relationship is enforced by migrateing the primary key from the parent (1) table to the child (m) table. Therefore, the emp table will then contain the primary key of the dept table - the department id column.

    For more info, you might want to find a good book on relational theory.


    HTH,

    - Chris

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Just to add my $0.02 to Chris's thoughts...

    Ther is one additional fundamental difference between PK and UKs. All columns that comprise PK must also be defined as NOT NULL, while this is not necessary for UKs. UKs still enforce uniqueness even if they contain NULLs, but prevent same NOT NULL + NULL combinations. For example, you have two collumns (C1 and C2) in UK, where C2 can have NULL values. So you can have the following pairs of values stored in those two collumns: (1, 2), (1,3), (2,1), (2, NULL). Now if you want to insert (1, NULL), UK will let you do so, while it will prevent you to insert (2, NULL), as this combination is allready contained in the table.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Nov 2000
    Location
    New Delhi, India
    Posts
    81
    But what if the unique key comprises of just one column?

    This allows multiple inserts into the unique column with nulls.

    I read some where that when there is a NULL in unique column then the unique key is not enforced.

  5. #5
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    Jmodic is correct. PK=Unique + Not Null, while Unique=Unique+(can be Null)

  6. #6
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Basically, I have rarely heard of a good reason to allow a NULL into a unique key. It definitely presents the issues raised here and IMHO should be avoided if at all possible, IMHO

    - Chris

  7. #7
    Join Date
    Apr 2001
    Posts
    37
    BTW, the original question was about unique index, not unique key.

    "What is the difference between Unique Index and Primary Key.
    Primary Key also creates the Index."

    Primary key is the data integrity constraint, Unique index itself is not
    a data integrity constraint but only the instrument to validate uniqueness;

    Both Primary Key and Unique Key constraints create Unique Indexes
    transparently, also Unique Indexes are dropped automatically and created
    again if someone disables/enables these constraints.

    You also can use Unique Index independently of any constraints
    (Oracle, however, does not recommend to do this too often
    due to conceptual reasons).

    In Oracle8i, for example, you can create Function-Based Unique Index
    which will validate quite complicated constraint, which can not be created
    using standard data integrity constraints.

    Example: if you need that the first 2 letters of ename be always different,
    create Unique Index like this:

    connect scott/tiger
    create unique index emp_ind on emp(substr(ename,1,2));

    (If you get error ORA-1031 here, grant query rewrite to scott
    inosov
    Brainbench MVP for Oracle DBA

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by ShrutiM
    But what if the unique key comprises of just one column?

    This allows multiple inserts into the unique column with nulls.

    I read some where that when there is a NULL in unique column then the unique key is not enforced.
    No, it will not allow multiple rows with NULL value in the unique index column. In single column unique index you can have at most one row with NULL in the indexed column
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  9. #9
    Join Date
    Apr 2001
    Posts
    37
    I don't agree with jmodic. See the following example:

    SQL> connect scott/tiger
    SQL> create unique index comm_idx on emp(comm);

    Index created.

    SQL> select count(*)
    2 from emp
    3 where comm is null;

    COUNT(*)
    ----------
    10
    inosov
    Brainbench MVP for Oracle DBA

  10. #10
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    I stand corrected...
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

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



Click Here to Expand Forum to Full Width