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.
- 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. A primary key index is automatically created for each table with a primary key column.
- DBMSs typically create primary index when a primary key is made which makes records to be stored according to values of primary key. Hence accessing data using a primary key becomes fast.
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 a 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.
- Composite Keys (if used): If a composite key (primary key with multiple attributes) is used, ensure that the combination of attributes uniquely identifies a record and remains minimal (no unnecessary attributes included).
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
} ;
Read more about SQL Primary Key Constraint.
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
- Uniqueness: The primary key ensures that each record in the table is uniquely identifiable. This prevents duplication and helps maintain data integrity.
- Efficient Data Retrieval: DBMS typically creates primary index when a primary key is made which makes records to be stored according to values of primary key. Hence accessing data using a primary key becomes fast.
- Data Consistency: By ensuring that each row is uniquely identified, the primary key guarantees consistency in the data.
- Referential Integrity: Primary keys are often referenced by foreign keys in other tables, establishing relationships between tables and ensuring that data remains consistent across the database.
- Prevents Null Values: Since primary keys cannot contain null values, they ensure that every record has a valid and complete identifier.
- Facilitates Relationships: The primary key serves as the foundation for defining relationships between tables in a relational database, enabling structured data organization.
- Supports Query Optimization: Using primary keys helps optimize queries, as the database can use the primary key index for faster search and retrieval.
- Avoids Redundancy: By uniquely identifying each record, primary keys reduce data redundancy, ensuring that no duplicate rows exist.
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.
Similar Reads
Super Key in DBMS
Keys in DBMS play an important role in maintaining the integrity and uniqueness of data. Among these, the super key is one of the fundamental concept. It helps in designing database and keeping it well-structured. A Super Key in DBMS is a group of one or more attributes in a table that can uniquely
6 min read
Unique Key in DBMS
A column or set of columns in a database system that uniquely identifies each tuple in the table is called a unique key. A unique key ensures that no two rows in the table have the same combination of values, so it enforces data integrity by preventing duplicity of the values in the table. In this a
5 min read
Surrogate Key in DBMS
A key is a column, or group of columns, in a database management system (DBMS) that uniquely identifies every row in a table. Natural keys and surrogate keys are the two categories of keys. Natural Key: A column, or group of columns, that is generated from the table's data is known as a natural key.
4 min read
Primary Indexing in Databases
Indexing is a technique used to reduce access cost or I/O cost, now the question arrives what is access cost? Access cost is defined as the number of secondary memory blocks which is transferred from secondary memory to main memory in order to access required data. In this article, we are going to d
5 min read
Serializability in DBMS
In this article, we are going to explain the serializability concept and how this concept affects the DBMS deeply, we also understand the concept of serializability with some examples, and we will finally conclude this topic with an example of the importance of serializability. The DBMS form is the
9 min read
MySQL Primary Key
MySQL is an open-source relational database management system that uses Structured Query Language (SQL) to manipulate databases. It stores data in a table format and to uniquely identify each record in a table, we require a Primary Key. In this article, we will learn how to add, modify, and remove t
4 min read
Primary key in MS SQL Server
A table has a particular number of columns and each column has n number of rows. At times, there might be a probability of repeated rows in a column. For example, a column named identification number has repeated rows. To avoid the duplication of rows, the concept of a key has been introduced. A key
2 min read
Relational Model in DBMS
The Relational Model represents data and their relationships through a collection of tables. Each table also known as a relation consists of rows and columns. Every column has a unique name and corresponds to a specific attribute, while each row contains a set of related data values representing a r
11 min read
MariaDB Primary Key
Primary keys are important in the field of relational databaseÂs. They keep the data unique and aid in easy data fetching. This article is about understanding the primary keys in MariaDB. MariaDB is a wideÂly used open-source systeÂm for managing relational databases. The MariaDB database is used
5 min read
What is a Query in DBMS?
In the field of Database Management Systems (DBMS), a query serves as a fundamental tool for retrieving, manipulating, and managing data stored within a database. Queries act as the bridge between users and databases, enabling them to communicate with the system to extract specific information or pe
5 min read