Primary Key in DBMS

Primary Key in DBMS

In DBMS, there are a number of keys that are used for different purposes, and the primary key is one of the most commonly known.

We will examine the primary key in this section - what it is, what is the use of a primary key, and we will also implement some examples to understand how it works.

What is a Primary Key

Basically, a Primary Key is a set of attributes of a table that uniquely identifies the rows, or we could say the tuples, of the table.

A primary key is one of the possible candidate keys that the database designer considers to be primary. There are many possible reasons to choose a primary key. From the candidate keys, the primary key will be selected based on the following criteria.

Minimal: The primary key should be composed of the minimum number of attributes that satisfy unique occurrences of the tuples. Thus, if one key is formed with two attributes and another with a single attribute, the one with the single attribute key should be selected as the primary key.

All users should be able to access the primary key. Using it, the user must be able to insert, access, or delete a tuple easily.

NON NULL Value: The primary key must contain a non-null value for each tuple in the relation. This allows the identification of each tuple.

Time Variant: A primary key cannot be null or change during a relation.

Unique: The primary key value must not be repeated in any of the tuples of the relationship.

Syntax for creating primary key constraint:

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

At column level:

"<column_name><datatype> Primary key; " 

At table level:

"Primary key(<column_name1>[,column_name>]....); "

Properties of a Primary Key:

  • A relation can contain only one primary key.
  • A primary key can consist of a single attribute, referred to as a single primary key, or it may include multiple attributes, referred to as a composite key.
  • Primary keys are the minimum super keys.
  • Data values for primary key attributes should not be null.
  • A prime attribute is an attribute that is part of a primary key.
  • The primary key is always chosen from the possible candidate keys.
  • When the primary key contains more than one attribute, then those attributes are irreducible.
  • Our convention is to underline the attribute that forms the primary key of a relation.
  • There cannot be a duplicate primary key.
  • Columns that are defined as LONG or LONG RAW cannot be part of a primary key.

Use of Primary Key

A primary key identifies a row in a table uniquely. In order to uniquely identify a row, the key constraint is set as the Primary key for that field. A primary key cannot contain a NULL value as the primary key is used to uniquely identify a value, and if there is no value, how can it continue to exist. This means that the field set with the primary key constraint cannot be NULL. Additionally, if the key is applied, it is up to the user to add or delete it.

"One table can have only one primary key regardless of whether it has one or more columns."

Understanding Primary Key

Let's examine some examples to better understand the role and use of a Primary key. In a database, a primary key identifies tuples or columns through which other fields can be uniquely identified.

Likewise, when storing the student registration details in the database, the registration number field is unique, so we assign it a primary key. Additionally, we set the primary key for an employee table based on the employee Id.

Let's understand it practically:

STUDENT_DETAILS, in which Roll_no, Name, and Marks are the specified attributes, is shown below.


As we know that all three of these attributes can be uniquely identified by the Roll_no attribute, because each student is given a unique roll number in every organization. In this case, we can constrain the Roll_no column with a primary key.

What if we set Name as Primary Key?

If we set the primary key on the Name attribute, it will not be valid because there can be more than one student with the same name. As a result, if we set the primary key to Name and try to enter the same name for two students, an error will appear. 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 would be an inappropriate approach because two or more students may receive similar marks in a subject. If we set the Marks attribute as the primary key, we will not be able to enter the same score for another entity. Therefore, we cannot set the primary key for the Marks attribute.

Here is a table to help us understand it:

Miscellaneous Example

Another example is a table named PRODUCT_DETAILS that has 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:

Because we can purchase the same product from two different companies, we cannot set the primary key for Prod_Name.

The PROD_Id and COMP_Id can be used to uniquely identify a company and a product, respectively. In other words, we need to decide which attribute should be the primary key, or should we set the primary key to both attributes, i.e., Prod_Id and Comp_Id.

This would be wrong since we can purchase more than one type of product from the same company if we use COMP_Id as the primary key. As a result, if we set the primary key for COMP_Id, then we won't be able to use that company to purchase more different products.

Additionally, we cannot set the primary key for both Prod_Id and COMP_Id, as there can be only one primary key in each table.

The approach will be correct if we set Prod_Id as the primary key, since each product we purchase will have a unique identifier. Accordingly, the primary key should be Prod_Id.

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

Defining the Primary Key

Let's talk about how to set a primary key on an attribute of the STUDENT_DETAILS table:

The creation of a primary key

"CREATE TABLE STUDENT_DETAIL (  

Roll_no int NOT NULL PRIMARY KEY,  

Name varchar (200) NOT NULL,  

Marks int NOT NULL  

} ;  "

Here's how to create a primary key on the Roll_no attribute of STUDENT_DETAILS:

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.

"ALTER TABLE STUDENT_DETAIL DROP PRIMARY KEY ; " 

Adding Primary Key after creating the table

You can set the primary key after creating a table by using the ALTER command and adding the primary key constraint. For example:

We have taken the Name attribute just to understand the syntax.

In this way, we can use the primary key of a table. Depending on the type of database, the syntax for defining the primary key may vary.