Primary Key in DBMS

In DBMS there are different types of keys available that are used for various purposes, for which the most common key is known as a primary key. A primary key is a unique identifier assigned to each record within a database table.

What is a Primary Key?

A primary key in a table that uniquely identifies each row and column or set of columns in the table. The primary key is an attribute or a set of attributes that help to uniquely identify the tuples(records) in the relational table. The primary key provides the means to distinguish one tuple from all the others in the relation. It helps the user to identify the location and also the database system to identify, locate, and refer to one particular tuple in the relation.

Since the primary key is used to identify the tuples of a relation none of its attribute values can be null. Since a relation may have more than one candidate key (such as EMP_ Id, PAN no in EMPLOYEE table). So one of these candidate keys should be chosen as the primary key.

Rules For Defining the Primary Key

• Minimal: The primary key is composed of a minimum number of attributes that satisfy the unique occurrence of the tuples or records. So if one candidate key is formed using two attributes and the other using a single attribute then the one with a single attribute key should be chosen as the primary key.
• Accessible: The primary key is used to check the ability to access and interact with the database. The user must easily create, read or delete a tuple using it.
• NON NULL Value: The primary key which refers to a nonnull value that is required for the identification of the tuple (record) means that any attribute can not contain the non-null value in DBMS.
• Time Invariant: The values of the primary key must not change or become null during the lifetime of the relation. For example, we cannot choose the name attribute of the EMPLOYEE Relation as a primary key because there is no guarantee that two employees with the same name will never exist.
• Unique: The primary key in which value can not be duplicated in any of the rows or tuples of the relation.

Syntax For Creating and Deleting Primary Key

Let’s see what syntax is used for creating a table.

Syntax

For Creation

ALTER TABLE table_name

ADD CONSTRAINT constraint_name PRIMARY KEY (column_name);

For Deletion

ALTER TABLE table_name

DROP CONSTRAINT constraint_name;

Consider an example of the table EMPLOYEE Relation. Since for each employee, there exists a unique EMP_ID so it acts as a primary key. Now consider the same EMPLOYEE Relation table with EMP_ID as the Primary Key.

Understanding Primary Key

For Example, a table consists of columns EMP_ID, Name, PAN_NO, Salary, Dept. Below is the syntax for creating the Primary Key on the EMP_ID attribute of the EMPLOYEE table:

CREATE TABLE EMPLOYEE (

Employee_Id int NOT NULL PRIMARY KEY,

Name varchar (200) NOT NULL,

PAN_NO int NOT NULL

Salary int NOT NULL

Dept varchar (200) NOT NULL

} ;

The table is created as shown below.

EMP_ID	Name	PAN_NO	Salary	Dept
E1	Ram	Null	23000	D1
E2	Sita	PSJ1234	Null	D2
E3	Sham	DJF2324	12000	D3
Null	Sapna	WCD232	13000	D4

Here, we find that Employee E1 has not been yet allotted a PAN number i.e. it does not exist for him so it has been allotted a value null and will be allotted a value later on. Employee E2’s Salary has not been fixed so it is unknown. Therefore we have allotted it a Null Value. But it will not cause a problem since it is not a primary key But if we allot a null value to the primary key EMP_ID then it may cause a problem as per the condition a key to be a primary key must not have a null value.

Now suppose that every employee has been allotted a PAN _NO.

EMP_ID	Name	PAN_NO	Salary
E1	Ram	Null	23000
E2	Sita	PSJ1234	15000
E3	Sham	DJF2324	12000
E4	Sapna	WCD232	13000

Here the attributes EMP_ID, PAN_NO, and Name (where no names repeat) can act as candidate keys for the EMPLOYEE Relation.

The Name is a time-variant attribute as when the database grows two employee may have the same names. So it may act as a possible candidate key when databases are very small. Since we know that the primary key is formed from one of the candidate keys both EMP_ID and PAN_NO can act as a primary key, but one at a time. We assume EMP_ID as the primary key in most of the cases.

A Primary Key can be formed from the combination of more than one attribute.

Terminology Related to Primary Key

When it comes to primary keys, there are several related terminologies you may come across as follows:

• Primary Key: A primary key is a type of key that contains a column or set of columns in a table that uniquely identifies each row in the table. The primary key is an attribute or a set of attributes that help to uniquely identify the tuples(records) in the relational table.
• Candidate Key: A candidate key is any attribute or combination of attributes that uniquely identifies rows in the table and the attribute that forms the key can not be further reduced.
• Composite Key: A composite key is a primary key that consists of two or more columns in a database table. It is used when a single column cannot uniquely identify a record, but the combination of multiple columns can.
• Surrogate Key: A manufactured unique identifier created in each database table record. When a natural primary key is not available, this value, which is usually an integer or a system-generated number, is utilized.
• Foreign Key: A foreign key is an attribute of a relationship or group of attributes that could serve as the primary key of another relationship to which it is connected through a relationship.
• Referential Integrity: Reference Consistency A database constraint known as referential integrity ensures that relationships between tables are correct and consistent. A foreign key in one table must match a primary key value in another table, or it must be NULL, according to this rule.
• Unique Key: The primary key in which value can not be duplicated in any of the rows or tuples of the Relation table. Although similar to a primary key, a unique key does not necessarily serve as the primary identifier for the table.
• Natural Key: A natural key is a column or a set of columns in a database table that has inherent meaning and can uniquely identify a record. It is derived from the data itself and is not an artificially generated value.

Advantages of Primary Key

• Each line of a table is uniquely identified. This is very helpful when performing any operation on the data such as updating, deleting, searching, etc.
• Due to Primary key the access of records become faster.

Properties of Primary Key

• A relation can contain only one primary key.
• A primary key may be composed of a single attribute known as a single primary key or more than one attribute known as the composite primary key.
• A primary key is also called a minimum super key.
• The attributes are not null in the primary key.
• Attributes that are part of the primary key are known as “prime attributes”.
• The primary key is always chosen from the candidate keys.
• The primary key attributes are irreducible.
• We use the convention that the attributes that form the primary key of the relation are underlined.
• The primary key cannot contain duplicate values.

Conclusion

In conclusion, in a database management system (DBMS), the primary key is the unique identifier for every record in the table, which guarantees data integrity and facilitates efficient data retrieval by uniquely identifying each record.

FAQs on Primary Key

Q.1: What is the primary key in SQL Server?

Answer:

The primary key should contain the UNIQUE Values, but can not contain NULL values. A table can have only one primary key. A primary key in sql server can be created as shown below.

Syntax:

ALTER TABLE table_name

ADD CONSTRAINT constraint_name PRIMARY KEY (column_name);

Q.2: What is time-invariant in the primary key?

Answer:

The values of the primary key must not change or become null during life time of relation. For example we cannot choose the Name attribute of the EMPLOYEE Relation as a primary key because there is no guarantee that two employees with the same name will never exist.

Q.3: Explain the characteristics of the primary key.

Answer:

• A relation can contain only one primary key.
• A primary key may be composed of a single attribute known as single primary key or more than one attribute know as composite primary key.
• A primary key is also called as minimum super key.
• The data values for the primary key attributes should not be null.