Primary key in DBMS

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

Primary Key Example in DBMS

Lets 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: STUDENT

Stu_Id	Stu_Name	Stu_Age
101	Steve	23
102	John	24
103	Robert	28
104	Steve	29
105	Carl	29

Points to Note regarding Primary Key

• We denote usually denote it by underlining the attribute name (column name).
• 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.
• The attribute(s) that is marked as primary key is not allowed to have null values.
• Primary keys are not necessarily to be a single attribute (column). 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.

Another example of primary key – 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, lets try to make a set of attributes that plays the role of it.

{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

Customer_ID	Product_ID	Order_Quantity
1011	9023	10
1122	9023	15
1099	9031	20
1177	9031	18
1011	9111	50

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 RDBMS?

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.

Lets 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 STUDENT
(
    Stu_Id int primary key,
    Stu_Name varchar(255) not null,
    Stu_Age int not null
)