Primary Key in DBMS

A primary key is a unique identifier assigned to each record within a database table.

• A primary key is an attribute (or column) or a set of attributes that uniquely identifies each row (or record) in the table.
• Since the primary key is used to identify the tuples of a relation none of its attribute values can be null.
• In many relational DBMSs, when you declare a primary key constraint, the system will create a unique index on that key.

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.

Rules For Defining a Primary Key

Since a relation may have more than one candidate key (such as EMP_ Id, PAN no in EMPLOYEE table). One of these candidate keys should be chosen as 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: None of the attributes in Primary Key can contain a null value.

• Time Invariant: The values of the primary key must not change or become null during the lifetime of the relation

• Unique: The primary key in which value can not be duplicated in any of the rows or tuples of the relation.

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.

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.

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.

Types of Keys in DBMS

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.

Primary Keys in DBMS

Visit Course