Primary key vs Unique Key

quiz
Challenge Inside! : Find out where you stand! Try quiz, solve problems & win rewards!

Overview

Keys in Relational Database Management System(RDBMS) or SQL are the columns that help us to identify the relationship between two or more two tables. Keys are used for accessing data from the tables. Primary keys are used to identify each record(not duplicate) in the table, whereas a unique Key constraint is a column or combination of columns in a table that uniquely identifies a record.

Scope of Article

  • This article defines Primary Keys and Unique Keys, their features and examples.
  • We will also see the differences between Primary keys and Unique keys
  • This article also shows how to implement a Primary key and a Unique key.

Primary Key

Primary Key(non-null key) is a column of the table that identifies every record should be unique in the table. The primary Key column should not contain any duplicate values(every column value in the primary key column should be unique), therefore there should only one primary key column in the table. Primary keys are generally used as Foreign keys in the other table.

Reasons to use Primary Key

  • According to the definition of Primary Key, primary key column should contain unique values, so primary key is used to identify every record in the table.
  • We can use the Primary key when we don't want any duplicate or NULL values in the particular column.
  • Data is organized in a sequence of clustered indexes whenever you physically organize the DBMS table.

Features of Primary Key

  • Values in the primary key column are NOT NULL.
  • We can create only one primary key for a table.
  • Primary Key column should not contain duplicate values.
  • It implements entity integration of the table.

Example of Primary Key

The following example describes that there are database sales, and there are two tables in that database:

  1. Customers
  2. Item

Customers Tables have five attributes:

  1. Customer ID
  2. Customer Name
  3. Phone Number
  4. Email Address
  5. Item Id

Item Tables have four attributes:

  1. Item ID
  2. Item Name
  3. Item Price
  4. Items Left

In Customer Table, Customer Id can never contain a duplicate or null value because when the new customer data is entered, it will generate a new customer_id for that customer. So, the customer_id column contains unique values. So can be considered as a primary key.

Customer Table

In Items Table, Item_Id never contains a duplicate or null value because every item has its unique item id. You can easily identify each row of a table with item id. So it is considered as a primary key.

Items Table

Unique Key

Unique Key constraint is a column or combination of columns in a table that uniquely identifies a record. Unique constraints are also referenced by the foreign key of another table. Unique Key is the same as primary key, but Unique key column can store NULL values in it.

Reasons to use Unique Key

  • Unique key can be used when you have to keep null values in the column.
  • Unique key is to make sure that the information in the column for each table record is unique.
  • Unique Key allows us to enter the NULL values in it.

Features of Unique Key

  • We can construct the unique key from one or more table fields.
  • We can define more than one unique key in the table.
  • A unique key can be easily referenced by a foreign key.
  • We can store NULL values in the Unique Key, but only one NULL is allowed null per column.

Example of Unique Key

The following example describes that there is a table called Students, in that Students table there are 6 attributes:

  1. Student_ID
  2. Student_Name
  3. Phone Number
  4. Roll Number
  5. Batch Number
  6. Email Address

In this Student Table, Student_Id can have unique constraints where entries are in the column. So, we can consider Student_ID as a Unique Key.

Student Tables

Key Differences Between Primary Keys and Unique Keys

  • Primary Key column will not accept NULL values, whereas the Unique Key column accepts the NULL value, but only one NULL value per column.
  • A table can contain only one Primary key column in a table, whereas there can be multiple unique key columns in the table.
  • A primary key should be unique, but a unique key cannot necessarily be the primary key.
  • Primary key implements entity integrity, whereas the unique key enforces unique data.
  • In the Primary Key, the default index is clustered, whereas in the unique key the default index is not clustered.

Tabular Difference Between Primary Key and Unique Key

ParameterPrimary KeyUnique Key
BasicIt is used as a unique identifier for each record in the table.It is also a unique identifier, but only when the primary key is not present in the table.
NULLIn the primary key, there should be no NULL values.In the unique key, there can be NULL values in the column.
Default IndexDefault index is clustered.Default Index is not clustered.
Number of KeyOnly one primary key in the table is allowed.There can be multiple Unique keys in the table.
PurposeThe purpose of the primary key is to enforce entity integrity.The purpose of the unique key is to enforce unique data.
Syntax
CREATE TABLE CUSTOMER
(
    Customer_ID INT PRIMARY KEY,
    Customer_Name VARCHAR(200),  
    Phone_Number VARCHAR(16), 
    Email Address VARCHAR(200), 
    Item Id VARCHAR(100) 
)
CREATE TABLE STUDENTS
(
    Student_ID INT UNIQUE,
    Student_Name VARCHAR(200)
    Phone_Number VARCHAR(15)
    Roll_Number INT 
    Batch_Number VARCHAR(4)
    Email_Address VARCHAR(200)
)

Conclusion

  • In this article, we have learned about primary keys and unique keys.
  • In primary key column, we can't add any NULL value in the column whereas, in the unique key column, we can add one NULL value in the column.
  • In primary keys, the default index is clustered whereas, in the unique key, the default index is non-clustered.
  • At last, we have defined the differences between primary and unique keys, along with the syntax of each.
Challenge Time!
quiz
quiz
Time to test your skills and win rewards! Note: Rewards will be credited after the next product update.
Free Courses by top Scaler instructors
rcbGet a Free personalized Career Roadmap from