Hi All,

Greetings of the day!

Today we will explore what is difference between the primary key & the unique key in the database, so let's start.

Primary Key

  • Each table can have only one primary key.
  • The primary key will create a clustered index.
  • The primary key can be related to other tables as a Foreign Key.
  • The primary key can not have null values
Unique Key 
  • Each table can have multiple unique keys.
  • The unique key will create a non clustered unique index.
  • A unique key can have one null value.
  • A unique key cannot be related to other tables as a foreign key.
Now let's see how we can declare the primary key & unique key in oracle.

CREATE TABLE STUDENT (
    STUDNET_ID NUMBER,
    STUDENT_NAME VARCHAR(20),
    STUDENT_MOBILE_NO NUMBER,
    
    CONSTRAINT "STUDNET_ID_PK" PRIMARY KEY(STUDNET_ID),
    CONSTRAINT "STUDENT_MOBILE_NO" UNIQUE(STUDENT_MOBILE_NO)
);

Now let's see the difference between clustered & non clustered indexes.

Clustered Index
  • The clustered index defines orders in which data is physically sorted in a table.
  • There can be only one clustered index in the table since data in the table can be sorted in only one order.
Non-Clustered Index
  • Non clustered index is a separate index used for maintaining uniqueness or improving performance.
  • There can be multiple non clustered indexes in a table.
Thank you
Happy Learning!