Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    2

    Unanswered: Difference bet unique index and primary key

    What is the difference between 'creating unique index to a column' and 'defining that column as primary key' ?

    Regards,
    Sam

  2. #2
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    Unique index allows NULL values in the column while Primary Key does not.
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  3. #3
    Join Date
    Mar 2004
    Posts
    2
    Dear Hings,

    I could understand the difference between 'unique' and 'primary key' constraint for s cloumn. But my question is when there is primary key .i.e

    create table test(sno integer primary key); // First way

    create table test(sno integer);

    and then executing :

    create unique index index1 on test(sno); // second way

    What is the diff between this first way and second way.

    Thanks,
    Sam

  4. #4
    Join Date
    Mar 2004
    Location
    Bahrain
    Posts
    3
    1st statement will create a primary key for given column and which will not store any null and duplicate.

    2nd statement will create a unique index for given column which can store nulls but not any duplicate.

    I hope this will clarify.

    Thanks,
    Vikrant

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    And there are other differences too:

    1) A primary (or unique) constraint can be used as the parent in a foreign key constraint. A unique index cannot.

    2) A primary (or unique) constraint may be supported by a non-unique index, e.g. when making the constraint deferrable:

    PHP Code:
    SQLcreate table t (id integer);

    Table created.

    SQLalter table t add constraint t_pk primary key (iddeferrable initially deferred;

    Table altered.

    SQLselect index_nameuniqueness from user_indexes where table_name='T';

    INDEX_NAME                     UNIQUENES
    ------------------------------ ---------
    T_PK                           NONUNIQUE

    SQL
    insert into t values (1);

    1 row created.

    SQLinsert into t values (1);

    1 row created.

    SQLcommit;
    commit
    *
    ERROR at line 1:
    ORA-02091transaction rolled back
    ORA
    -00001unique constraint (TANDREWS.T_PKviolated 

Posting Permissions

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