What is difference between UNIQUE and PRIMARY KEY constraints

Both UNIQUE and PRIMARY KEY constraints enforce uniqueness in the column. But there are two major differences:

Primary Key
------------
1. It does not allow NULL value to be inserted in the column.
2. Oracle creates a clustered index by default for the column.

Unique Key
-----------
1. It allows one NULL value to be inserted in the column.
2. Oracle creates a non-clustered index by default in the column.


Moreover a table can have more than one UNIQUE key but not more than one Primary key.

12 comments :

  1. what will happen if u give unique key and not null as a constraint?

    ReplyDelete
  2. If you give unique key + not null, then the difference will be in the way the non-clustered index is maintained.

    ReplyDelete
  3. Can you please show me the difference between primary key and unique key with example?

    ReplyDelete
  4. First create a table which will be used to test Primary key:

    CREATE TABLE test_pkey (primary_key_field NUMBER PRIMARY KEY);

    Secondly create a table which will be used to test Unique key:

    CREATE TABLE test_ukey (unique_key_field NUMBER UNIQUE);

    Now query the constraints which are created:

    SELECT TABLE_NAME,CONSTRAINT_TYPE,CONSTRAINT_NAME FROM user_constraints WHERE table_name IN ('TEST_UKEY','TEST_PKEY');

    TABLE_NAME CONSTRAINT_TYPE CONSTRAINT_NAME
    ---------- --------------- ---------------
    TEST_PKEY P SYS_C005147
    TEST_UKEY U SYS_C005148

    Now to test the Primary key concepts, insert the following records:

    INSERT INTO TEST_PKEY VALUES(1);

    The record is successfully inserted.

    INSERT INTO TEST_PKEY VALUES(1);

    The second insert statement should fail resulting in constraint SYS_C005147 violation.

    Now try to insert this record:

    INSERT INTO TEST_PKEY VALUES(NULL);

    The error is returned as ORA-01400:cannot insert NULL into("TEST_PKEY"."PRIMARY_KEY_FIELD")

    Now you know that Primary key does not allow duplicate records or NULLs to be inserted.

    Now to test the Unique key concepts, insert the following records:

    INSERT INTO TEST_UKEY VALUES(1);

    The record is successfully inserted.

    Now once again try the same record:

    INSERT INTO TEST_UKEY VALUES(1);

    The error is returned as ORA-00001:unique constraint (SYS_C005148) violated.

    Now try to insert NULL record into the table:

    INSERT INTO TEST_UKEY VALUES(NULL);

    The record is succesfully inserted. This is the first differernce between Primary key and Unique key constraint fields.

    Hope the example is clear for you.

    ReplyDelete
  5. Would there be any performance improvements if we have both Primary and Unique Key? Primary Key can serve the purpose of both the Keys. Please clarify on this.

    ReplyDelete
  6. There will be performance improvements if:
    1. The table has columns which are unique but already has a primary key

    2. The table has not millions of records (If this were the case, the DML operations would take more time)

    3. Though Primary key can be used for both keys (UNIQUE, NOT NULL), if already there is a Primary key in the table then it is not possible to create one more. In this situation where you will go for Unique key.

    ReplyDelete
  7. Given in a very clear and concise way.

    Thanks

    ReplyDelete
  8. You can have Unique key with clustered index. So there is no difference in Primary and Unique key. You answers are not satisfactory

    ReplyDelete
  9. Hi Anantha,
    If I create a Primary key it will create cluster index. we can define IOT while define Primary key constraints on particular columns. Kindly let me know the difference between the both indexes.
    Thanks,
    Gireesh

    ReplyDelete
  10. Hope this article lists out difference of both and this is what you are looking for:

    http://www.dba-oracle.com/t_iot_index_organized_table.htm

    ReplyDelete
  11. Refer this for the wonderful information on UK and PK
    http://tsqltips.blogspot.com/

    ReplyDelete
  12. That's add value to any one's knowledge of PK and UK. worth including in my list of differences between primary and unique key.

    ReplyDelete