Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2008
    Posts
    24

    Question Unanswered: PRIMARY-KEY Vs UNIQUE+NOT-NULL

    What is the difference between Primary key and Unique key + Not Null....

    Which one is better to use and why.............

    Any Idea....


    Thanks,
    gohappy

  2. #2
    Join Date
    Apr 2008
    Posts
    28
    Hi gohappy,

    Their uses are different

    PK are used for referential integrity (pair with FK)
    UK are used for data consistency and validation

    For instance: you have an ORDERS and ORDERS_DETAIL tables,

    with PK you must delete ORDERS_DETAIL registers prior to delete ORDERS register
    with UK+NOT NULL .... whoops! you may have an order without detail , or details without an order
    Last edited by OracleDisected; 04-30-08 at 02:51.

  3. #3
    Join Date
    Apr 2008
    Posts
    24
    Thanks Ignaco....

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Quote Originally Posted by OracleDisected
    PK are used for referential integrity (pair with FK)
    Oh?
    Code:
    SQL> create table master
      2    (id_m number unique,     --> NOT primary key!
      3     name varchar2(20)
      4    );
    
    Table created.
    
    SQL>
    SQL> create table detail
      2    (id_d number primary key,
      3     id_m number constraint fk_dm
      4                   references master (id_m),  --> referencing UNIQUE key
      5     value number
      6    );
    
    Table created.
    
    SQL>
    Data Integrity chapter of the Oracle 10g Database Concepts book might be interesting reading.

  5. #5
    Join Date
    Apr 2008
    Posts
    28
    Yes, that is a hole in the implementation... I wonder if any reverse engineering tool ... or even Oracle BI will be capable of reconstructing relation using PK/FK, using that practice.

    The fact we can do things, does not imply they are 100% right.

Posting Permissions

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