Primary key vs Unique Key
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(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:
Customers Tables have five attributes:
- Customer ID
- Customer Name
- Phone Number
- Email Address
- Item Id
Item Tables have four attributes:
- Item ID
- Item Name
- Item Price
- 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.
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.
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:
- Phone Number
- Roll Number
- Batch Number
- 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.
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
|Parameter||Primary Key||Unique Key|
|Basic||It 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.|
|NULL||In the primary key, there should be no NULL values.||In the unique key, there can be NULL values in the column.|
|Default Index||Default index is clustered.||Default Index is not clustered.|
|Number of Key||Only one primary key in the table is allowed.||There can be multiple Unique keys in the table.|
|Purpose||The purpose of the primary key is to enforce entity integrity.||The purpose of the unique key is to enforce unique data.|
- 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.