Primary Key in DBMS

There are certain keys in DBMS that are used for different purposes, from which the most commonly known is the Primary Key.

Here, in this section, we will look at the Primary key - What it is, what is the use of a primary key, and we will also implement some examples to understand that how a primary key works.

What is a Primary Key

A Primary Key is the minimal set of attributes of a table that has the task to uniquely identify the rows, or we can say the tuples of the given particular table.

A primary key of a relation is one of the possible candidate keys which the database designer thinks it's primary. It may be selected for convenience, performance and many other reasons. The choice of the possible primary key from the candidate keys depend upon the following conditions.

• Minimal: The primary key should be composed of the minimum number of attributes that satisfy unique occurrences of the tuples. So if one candidate key is formed using two attributes and another using a single attribute then the one with the single attribute key should be chosen as the primary key.
• Accessible: The primary key used should be accessible by anyone who wants to use it. The user must easily insert, access or delete a tuple using it.
• NON NULL Value: The primary key must have a non-null value for each tuple of the relation, which is required for the identification of the tuple.
• Time Variant: The values of the primary key must not change or become null during the time of a relation.
• Unique: The value of the primary key must not be duplicated in any of the tuples of a relation.

Syntax for creating primary key constraint:

The primary key constraint can be defined at the column level or table level.

At column level:

At table level:

Properties of a Primary Key:

• A relation can contain only one primary key.
• A primary key may be composed of a single attribute known as single primary key or more than one attribute known as composite key.
• A primary key is the minimum super key.
• The data values for the primary key attribute should not be null.
• Attributes which are part of a primary key are known as Prime attributes.
• Primary key is always chosen from the possible candidate keys.
• If the primary key is made of more than one attribute then those attributes are irreducible.
• We use the convention that the attribute that form primary key of relation is underlined.
• Primary key cannot contain duplicate values.
• Columns that are defined as LONG or LONG RAW cannot be part of a primary key.

Use of Primary Key

As defined above, a primary key is used to uniquely identify the rows of a table. Thus, a row that needs to be uniquely identified, the key constraint is set as the Primary key to that particular field. A primary key can never have a NULL value because the use of the primary key is to identify a value uniquely, but if no value will be there, how could it sustain. Thus, the field set with the primary key constraint cannot be NULL. Also, it all depends on the user that the user can add or delete the key if applied.

Note: One table can have only one primary key where it does not matter the table having one or more columns in it.

Understanding Primary Key

Let's discover some examples through which we can understand the role and use of a Primary key. Generally, in a database, we apply the primary key on those tuples or columns through which we need to uniquely identify the other database fields.

For example: When we store the registration details of the students in the database, we find the registration number field unique and assign the primary key to the field. Also, for an employee table, we set the primary key on the employee Id of the table.

Let's understand it practically:

Below is the table named STUDENT_DETAILS, where Roll_no, Name, and Marks are the specified attributes of it.

As we know that from these three attributes, the Roll_no attribute is the one that can uniquely identify other two attributes of the table as each student is provided with a unique roll number in every organization. So, we can set the primary key constraint on the Roll_no column.

What if we set Name as Primary Key?

If we set the primary key on the Name attribute, it will be invalid because two or more students can have the same name. So, if we set the primary key on Name and try to enter the same name for two students, it will display an error. That's why we cannot set the Name attribute as the primary key.

What if we set Marks as Primary Key?

If we set the primary key on the Marks attribute, then it will be an inappropriate approach because two or more students can score similar marks in a subject. Thus, if we set the Marks attribute as the primary key, we will not be able to enter the same score for other entity. That's why we cannot set the primary key for the Marks attribute.

We can see the given below table to understand it:

Miscellaneous Example

Consider another example where we have a table named PRODUCT_DETAILSwhere we have the following attributes:

COMP_Id: The company id from where we may purchase different products.

Prod_Name: It intakes the products that we purchased.

Prod_Id: Each product is given an identification number.

Points to be noted:

• We cannot set the primary key for Prod_Name because we can purchase the same product from two different companies.
• On the other hand, both COMP_Id and Prod_Id can uniquely identify a company and a product. So, the question is on which attribute we should set the primary key, or we should set the primary key to both attributes, i.e., Prod_Id and Comp_Id.
• If we set the primary key on COMP_Id, then it will be wrong because we can purchase more than one type of product from the same company. So, if we will set the primary key for COMP_Id, then we will be unable to use that company for purchasing more different products.
• Also, we cannot set the primary key for both Prod_Id and COMP_Id as there can be only one primary key in one table.
• If we set the primary key for Prod_Id, the approach will be correct because each product we purchase will have a unique identification. So, we should set the primary key for Prod_Id.

We can understand it better by looking at the below table:

Defining the Primary Key

Let's discuss how we can set a primary key on a STUDENT_DETAILS table attribute:

Creating a Primary Key

Below is the syntax for creating Primary Key on Roll_no attribute of STUDENT_DETAILS table:

It is the basic syntax used in SQL and Oracle servers.

Removing Primary Key

It is also possible to delete the set primary key from an attribute using ALTER and DROP commands.

Adding Primary Key after creating the table

In order to set the primary key after creating a table, use the ALTER command and add the primary key constraint to do so. The syntax is shown below:

We have taken the Name attribute just for understanding the syntax.

So, in this way, we can use and set the primary key on a table. However, the syntax for defining the primary key may vary for different types of databases.