Prerequisite – Keys in Relational Model
A primary key is a column of table which uniquely identifies each tuple (row) in that table. Primary key enforces integrity constraints to the table. Only one primary key is allowed to use in a table. The primary key does not accept the any duplicate and NULL values. The primary key value in a table changes very rarely so it is chosen with care where the changes can occur in a seldom manner. A primary key of one table can be referenced by foreign key of another table.
For better understanding of primary key we take table named as Student table, having attributes such as Roll_number, Name, Batch, Phone_number, Citizen_ID.
The roll number attribute can never have identical and NULL value, because every student enrolled in a university can have unique Roll_number, therefore two students cannot have same Roll_number and each row in a table is uniquely identified with student’s roll number. So, we can make Roll_number attribute as a primary key in this case.
Unique key constraints also identifies an individual tuple uniquely in a relation or table. A table can have more than one unique key unlike primary key. Unique key constraints can accept only one NULL value for column. Unique constraints are also referenced by the foreign key of another table. It can be used when someone wants to enforce unique constraints on a column and a group of columns which is not a primary key.
For better understanding of unique key we take Student table with Roll_number, Name, Batch, Phone_number and Citizen_ID attributes.
Roll number attribute is already assigned with the primary key and Citizen_ID can have unique constraints where each entry in a Citizen_ID column should be unique because each citizen of a country must have his or her Unique identification number like Aadhaar Number. But if student is migrated to another country in that case, he or she would not have any Citizen_ID and the entry could have a NULL value as only one NULL is allowed in the unique constraint.
Key Differences Between Primary key and Unique key:
- Primary key will not accept NULL values whereas Unique key can accept one NULL value.
- A table can have only primary key whereas there can be multiple unique key on a table.
- A Clustered index automatically created when a primary key is defined whereas Unique key generates the non-clustered index.
|Paramenter||PRIMARY KEY||UNIQUE KEY|
|Basic||Used to serve as a unique identifier for each row in a table.||Uniquely determines a row which isn’t primary key.|
|NULL value acceptance||Cannot accept NULL values.||Can accept one NULL value.|
|Number of keys that can be defined in the table||Only one primary key||More than one unique key|
|Index||Creates clustered index||Creates non-clustered index|
Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.
- Difference between Primary Key and Foreign Key
- Difference between Primary key and Super key
- Difference between Primary and Candidate Key
- Primary key in MS SQL Server
- Difference between Primary and Secondary Memory
- Difference between Primary and Secondary Data
- Types of Keys in Relational Model (Candidate, Super, Primary, Alternate and Foreign)
- Primary Indexing in Databases
- Difference between Private key and Public key
- Difference between Super Key and Candidate Key
- Difference Between Symmetric and Asymmetric Key Encryption
- Partial, Unique, Secondary, Composite and Surrogate keys in DBMS
- Find unique elements in linked list
- Python program to check if a string contains all unique characters
- Get unique values from a column in Pandas DataFrame
- SQL query to find unique column values from table
- Unique Constraint in MS SQL Server
- Fibonacci Heap - Deletion, Extract min and Decrease key
- Domain Key Normal Form in DBMS
- How to create an array with key value pairs in PHP?
If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to email@example.com. See your article appearing on the GeeksforGeeks main page and help other Geeks.
Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.