×

Courses

Chapters

>
<

Aptitude

Primary Key in DBMS

Primary Key

Sometimes you need to maintain each and every row or record in a database table as unique. This purpose is served by using a primary key on that particular column

A primary key is a constraint in a table which uniquely identifies each row record in a database table by enabling one or more the column in the table as primary key.

Primary Key

  • A primary key column must contain unique values
  • Null values not allowed for the primary key column
  • Only one primary key is allowed for a table.

Examples for primary keys

  • Student ID in student table is a primary key because no two students will have the same Id
  • Employee ID in employee table is a primary key because no two employees to have the same Id.
  • Age, name, phone numbers will not make sense  to be used as primary key columns because more than one person can have same age name our phone numbers, etc.

Properties
Creating a primary key

A particular column is made as a primary key column by using the primary key keyword followed with the column name

  
CREATE TABLE EMP
(
   ID   INT              
   NAME VARCHAR (20)     
   AGE  INT             
   ADDRESS  CHAR (25) ,
   SALARY   DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

  • Here we have used the primary key on ID column then ID column must contain unique values i.e one ID cannot be used for another student.
  • If you try to enter  duplicate value while inserting in the  row you are displayed with an error
  • Hence primary key will restrict you to maintain unique values and not null values in that particular column.

Students Table

Stu_IdStu_NameStu_Age
101Steve23
102John24
103Robert28
104Steve29
105Carl29
Primary key with more than one attribute

  • Consider a table  with three  attitudes customer ID, product ID, product quantity
  • Customer ID needs to be entered for each time the customer purchases an  order hence customer ID appears more than once in the customer ID table hence it cannot be served as the primary key i.e it failed to uniquely identify a record
  • Example customer ID 66 has placed two orders hence customer ID appeared 66 two times in the customer ID column

Customer_IDProduct_IDOrder_Quantity
66902310
67902315
68903120
69903118
66911150

  • Product  ID and product quality cannot be declared as the primary key because of more than one customer purchase same product and the same quantity
  • In this situation all three attributes fail  to serve as a primary key .Hence  combination of these  attribute can be used as a primary key
  • For example [customer ID, product ID] can be used as the primary key table customers this combination helps to uniquely access records of customer

Example 

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

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

→
←