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.

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.

Primary Key in DBMS

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:

Primary Key in DBMS

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.

Next TopicSuper Key in DBMS

Contact US

Email:[email protected]

Primary Key in DBMS