Explain the concept of primary key with an example (DBMS)?

DBMSDatabaseBig Data Analytics

Key is a data item which is used to identify a record or a value used to identify a record in a database is called a key. It helps uniquely to identify an entity from an entity set.

Key allows us to identify a set of attributes that make them sufficient to distinguish entities from each other.

Example

Account number, employee number, customer number are used as key fields because they specifically identify a record stored in a database.

Different types of keys

The different types of keys in the database management system (DBMS) are as follows −

  • Primary key

  • Super key

  • Candidate key

  • Alternate key

  • Artificial key

  • Foreign key

Primary, super, candidate, alternate and artificial keys show the property of uniqueness whereas the foreign key shows referential integrity.

Primary key

From many candidate keys, the database designer selects one candidate key for his database called as primary key. The primary key uniquely identifies each record in a table and must never be the same for two records.

Primary key has some properties which are as follows −

  • Uniqueness (two different records cannot be identical).

  • NOT NULL (null value is not allowed).

  • Value in a primary key column can never be modified or updated, if any foreign key refers to that primary key.

Primary key is underlined in a table to clearly identify.

For Example −

stdNo

Consider an employee table given below −

EmpID
Empname
SSN
DeptID
DOB

Here,

  • Here,

  • In the column, we choose to maintain uniqueness in a table at row level.

  • In the column, we choose to maintain uniqueness in a table at row level.

  • EmpID is a preferable choice because SSN is a secure value.

  • Primary key is a candidate key which is chosen by a database designer to identify entities with an entity set.

  • Primary key is minimal super keys.

  • In the ER diagram primary keys are represented by underlining.

  • Primary key is composed of only a single attribute.

  • But it is possible to have a primary key composed of more than one attribute.

To identify the column field as primary key, it has to meet the following conditions −

  • No two rows have the same primary key value.

  • Every row must have a primary key value.

  • The primary key field cannot be NULL value.

  • Value in the primary key column can never be modified or updated, if any foreign key refers to that primary key.

In the above Entity Relationship Model (ER) diagram:

  • In the customer table customer-id is the primary key.

  • In the loan table loan-number is the primary key.

raja
Published on 03-Jul-2021 13:22:45
Advertisements