What is the difference between a Primary Key and a Unique key?
What is Primary Key?
Usually, a table contains both rows and columns. The primary key is represented in the format of columns, where it identifies the individual identification of each row. The primary key performs various operations such as insert, delete, update, alter and modify. For example, the Customer Id number in a table denotes the primary key. The Id number helps to identify and alter the details of that particular row.
Customer ID
NAME
CITY
DESIGNATION
001
RIYA
Coimbatore
Architect
004
Gayu
Delhi
Software Engineer
005
Pink
Mumbai
Doctor
From the above table, the customer ID is used to identify the separate identification of the customer.
Uses of Primary Key
The primary key is used to establish a relationship between the table contents. It helps to check whether the data present in the row is unique.
Types of Primary Key
The primary keys are subdivided into the following types.
1. Candidate Key
2. Alternate Key
3. Super Key
4. Composite Key
5. Foreign Key
6. Unique Key
1. Candidate Key
Candidate Key usually identifies the entire row with single or multiple keys. Usually, it denotes the whole data of the entire row. For example, in the below-mentioned table, Customer Id and Designation are some of the examples of a candidate key.
Customer ID
NAME
CITY
DESIGNATION
001
RIYA
Coimbatore
Architect
004
Gayu
Delhi
Software Engineer
005
Pink
Mumbai
Doctor
It maintains the relationship between the data in the table. It helps to identify the data uniquely.
2. Alternate Key
The alternate key is also called a Secondary Key. Usually, a candidate key is not selected as the Primary Key, but the candidate key is called an Alternate Key.
Student ID
Student Email
City
Student Course
001
Abc_gmail.com
Coimbatore
ECE
004
Def_gmail.com
Chennai
EEE
005
Ghi_gmail.com
Bangalore
Mechanical
In the table mentioned above, Student ID is the primary key, where Student email is an alternate key.
3. Super Key
Super Key is meant to be the combination of columns which represents the attributes uniquely.
Roll no
Age
Car
City
001
15
Honda City
Chennai
004
18
Maruti
Pune
005
19
Benz
Mumbai
In the table mentioned above, Roll no, Age and City are Super Keys which denote the whole attributes in the column format. Among various attributes, the tuples are identified uniquely. Usually, the super keys are tough to find the tuple among different attributes, whereas it is easy to find when it is grouped with keys.
4. Composite Key
The composite key acts by combining one or two columns to identify the tuples in the table. Finding the record of the row in a single column is tough compared to combining two columns. By combining one or more columns, the table record is easily found.
Roll no
Customer Name
Designation
Age
001
AAA
Engineer
41
004
BBB
Doctor
54
005
CCC
Professor
61
In the above table, name and age are composite keys. It uniquely identifies the record of the row by merging two or more columns.
5. Foreign Key
The foreign key represents the whole column at one table, while the other acts as a primary key. Usually, it represents the attributes of the table, while other the same acts as the primary key. It maintains the relationship between the two data present in the table. The relationship between the foreign key and the primary key is demonstrated in a relational database.
Roll no
Customer Name
Designation
Age
001
AAA
Engineer
41
004
BBB
Doctor
54
005
CCC
Professor
61
Roll no
001
Abc_gmail.com
004
Def_gmail.com
005
Ghi_gmail.com
In the table mentioned above, the Roll no acts as a foreign key in the first table, while it acts as a primary key in the second table.
6. Unique key
Usually, unique key accepts only a null value which uniquely identifies a record in the table. It is similar to a primary key. As it accepts the null value, it helps identify the duplicate value.
Roll no
Name
Gmail
001
Rohit
Abc_gmail.com
004
Mohit
Def_gmail.com
005
Ravi
In the above table, the one null value is represented in the Gmail column.
The difference between the primary key and the unique key is primary key does not accept a null value, while the unique key accepts a null value.
Summary
The above tutorial explains the different types of keys with examples. Each key has its unique features and functions. Based on their functionality, the keys are used in different conditions.
