Primary key in DBMS

In this guide, you will learn about primary key in DBMS with the help of examples. We will discuss, what is a primary key, how it is different from other keys in DBMS such as foreign key and unique key.

What is a Primary Key

A primary key is a minimal set of attributes (columns) in a table that uniquely identifies tuples (rows) of that table.

For example, you want to store student data in a table “student”. The attributes of this table are: student_id, student_name, student_age, student_address. The primary key is a set of one or more of these attributes to uniquely identify a record in the table. In the case, since student_id is different for each student, this can be considered a primary key.

Characteristics of a primary key

Primary key has the following characteristics:

1. Minimal

The primary key should contain minimal number of attributes. The example we seen above, where student_id is able to uniquely identify a record, here combination of two attributes such as {student_id, student_name} can also uniquely identify record. However since we should choose minimal set of attribute thus student is chosen as primary key instead of {student_id, student_name}.

2. Unique

The value of primary key should be unique for each row of the table. The column(s) that makes the key cannot contain duplicate values. This is because non-unique value would not help us uniquely identify record. If two students have same student_id then updating a record of one student based on primary key can mistakenly update record of other student.

3. Non Null

The attribute(s) that is marked as primary key is not allowed to have null values.

4. Not dependent on Time

The primary key value should not change over time. It should remain as it is until explicitly updated by the user.

5. Easily accessible

The primary key of the record should be accessible to all the users who are performing any operations on the database.

6. Can have more than one attributes

It can be a set of more than one attributes (columns). For example {Stu_Id, Stu_Name} collectively can identify the tuple in the above table, but we do not choose it as primary key because Stu_Id alone is enough to uniquely identifies rows in a table and we always go for minimal set. Having that said, we should choose more than one columns as primary key only when there is no single column that can uniquely identify the tuple in table.

Syntax for Creating Primary key constraint:

While creating table you can define primary key like this:

CREATE TABLE table_name
(
  column_name1 datatype [ NULL | NOT NULL ],
  column_name2 datatype [ NULL | NOT NULL ],
  ...

  CONSTRAINT constraint_name PRIMARY KEY (column_nameX, column_nameY..)
);

For example: Here we are making stu_id primary key while creating the table STUDENTS.

CREATE TABLE STUDENTS
( stu_id int NOT NULL
  first_name VARCHAR(30) NOT NULL,
  last_name VARCHAR(25) NOT NULL,
  dob DATE,
  CONSTRAINT student_pk PRIMARY KEY (stu_id)
);

Properties of a Primary Key

It doesn’t not allow duplicates.
A table can have only one primary key
Primary key is denoted by underlining the attribute name (column name).
It uniquely identifies each record of the table
It doesn’t allow null values to be inserted for the primary key column.
A primary key can consists of more than one columns, such primary key is known as composite primary key.

What Are the Benefits of a Primary Key?

The following are the advantages of a primary key:

It uniquely identifies each row of a table. This is definitely useful to perform any operation on data such as update, delete, search etc.
It allows faster access of the record because it uses the concept indexing in DBMS.

Primary Key Example in DBMS

Let’s take an example to understand the concept of primary key. In the following table, there are three attributes: Stu_ID, Stu_Name & Stu_Age. Out of these three attributes, one attribute or a set of more than one attributes can be a primary key.

Attribute Stu_Name alone cannot be a primary key as more than one students can have same name.
Attribute Stu_Age alone cannot be a primary key as more than one students can have same age.
Attribute Stu_Id alone is a primary key as each student has a unique id that can identify the student record in the table.

Note: In some cases an attribute alone cannot uniquely identify a record in a table, in that case we try to find a set of attributes that can uniquely identify a row in table. We will see the example of it after this example.
Table Name: STUDENTS
Primary Key in DBMS

Another example: composite key with more than one attributes

Consider this table ORDER, this table keeps the daily record of the purchases made by the customer. This table has three attributes: Customer_ID, Product_ID & Order_Quantity.

Customer_ID alone cannot be a primary key as a single customer can place more than one order thus more than one rows of same Customer_ID value. As we see in the following example that customer id 1011 has placed two orders with product if 9023 and 9111.
Product_ID alone cannot be a primary key as more than one customers can place a order for the same product thus more than one rows with same product id. In the following table, customer id 1011 & 1122 placed an order for the same product (product id 9023).
Order_Quantity alone cannot be a primary key as more more than one customers can place the order for the same quantity.
Since none of the attributes alone were able to become a primary key, let’s try to make a set of attributes that plays the role of it. The set {Customer_ID, Product_ID} together can identify the rows uniquely in the table so this set is the primary key for this table.

Table Name: ORDER
Primary Key Example in DBMS

Note: While choosing a set of attributes for a primary key, we always choose the minimal set that has minimum number of attributes. For example, if there are two sets that can identify row in table, the set that has minimum number of attributes should be chosen as primary key.

How to define primary key in DBMS?

In the above example, we already had a table with data and we were trying to understand the purpose and meaning of primary key. However you should know that generally we define the primary key during table creation. We can define the primary key later as well but that rarely happens in the real world scenario.

Let’s say we want to create the table that we have discussed above with the customer id and product id set working as primary key. We can do that in SQL like this:

Create table ORDER
(
    Customer_ID int not null,
    Product_ID int not null,
    Order_Quantity int not null,
    Primary key (Customer_ID, Product_ID)
)

Suppose we didn’t define the primary key while creating table then we can define it later like this:

ALTER TABLE ORDER
ADD CONSTRAINT PK_Order PRIMARY KEY (Customer_ID, Product_ID);

Another way:
When we have only one attribute as primary key, like we see in the first example of STUDENT table. we can define the key like this as well:

Create table STUDENTS
(
    Stu_Id int primary key,
    Stu_Name varchar(255) not null,
    Stu_Age int not null
)

❮ Previous Next ❯