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.
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:
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:
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 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.