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.
what will happen if u give unique key and not null as a constraint?
ReplyDeleteIf you give unique key + not null, then the difference will be in the way the non-clustered index is maintained.
ReplyDeleteCan you please show me the difference between primary key and unique key with example?
ReplyDeleteFirst create a table which will be used to test Primary key:
ReplyDeleteCREATE 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.
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.
ReplyDeleteThere will be performance improvements if:
ReplyDelete1. 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.
Given in a very clear and concise way.
ReplyDeleteThanks
You can have Unique key with clustered index. So there is no difference in Primary and Unique key. You answers are not satisfactory
ReplyDeleteHi Anantha,
ReplyDeleteIf 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
Hope this article lists out difference of both and this is what you are looking for:
ReplyDeletehttp://www.dba-oracle.com/t_iot_index_organized_table.htm
Refer this for the wonderful information on UK and PK
ReplyDeletehttp://tsqltips.blogspot.com/
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