Types of Keys in Database

Types of Keys in Database

8 mins read5K Views Comment
clickHere
Updated on Jul 25, 2023 19:16 IST

In databases, keys are crucial for data organization and integrity. The primary key uniquely identifies each record, while foreign keys establish table relationships. Candidate keys meet uniqueness and minimality criteria, composite keys combine columns for a unique identifier, and super keys can uniquely identify records. These keys form the foundation of effective data management. Let’s understand about them in detail.

2022_04_Untitled-design-7.jpg

In databases, keys are fundamental in maintaining data integrity and organization. They serve as unique identifiers and establish relationships between tables, enabling efficient data retrieval and manipulation. This introduction will explore the various types of keys used in databases, including primary, foreign, candidate, composite, super, etc., shedding light on their significance in effective data management. Scroll your screen down and learn about types of keys in database,

To know more about, Database and SQL Online Courses and Certifications, click here.

Table of Content

What are the Keys in Database?

Keys in the database are used to identify tables present in the database uniquely. With the help of keys, users can relate tables.

Check out: Full form of DBMS

Types of Keys in Database

The following types of keys are used in a database. Let us understand each one of them one by one.

  • Super Key
  • Candidate Key
  • Primary Key
  • Alternate Key
  • Unique Key
  • Foreign Key
  • Composite Key

For our understanding I am going to consider the following tables.

Patients Table

Patient ID Patient Name Sex Age MedInsuranceNo AadharNo
01 Sheela F 23 Med0291 839292319012
02 Rehan M 21 Med8421 123456789012
03 Anay M 56 Med4203 848298469201
04 Mahira F 42 Med4792 724723021922
05 Nishant M 12 Med8419 472038311099

Checkup Details Table

Patient ID AnnualCheckupMonth Fees
03 Feb2022 6700
04 Apr2022 8900
03 Feb2022 6700
04 Apr2022 8900

Super Key

Super Key is a set of attributes that can uniquely identify a table. A single table can have multiple super keys. A candidate key, primary key and a unique key can be a super key, but the reverse does not hold true.

Example:

In our above example we have chosen the PatientID, MedInsuranceNo and Aadhar No to uniquely identify tuples. So the super key set will be as follows:

  • {PatientID}
  • {MedInsuranceNo}
  • {AadharNo}
  • {PatientID, MedInsuranceNo}
  • {PatientID, AadharNo}
  • {MedInsuranceNo, AadharNo}
  • {PatientID, MedInsuranceNo, AadharNo}
What is the Difference Between DBMS and RDBMS?
What is the Difference Between DBMS and RDBMS?
DBMS (Database Management System) is a software tool that manages databases, while RDBMS (Relational Database Management System) is a type of DBMS that organizes data into tables with predefined relationships....read more
What are the Advantages of DBMS?
What are the Advantages of DBMS?
The advantages of a DBMS (Database Management System) are numerous and impactful. It centralizes data storage, enhances data security and privacy, enforces data integrity, ensures efficient data access and retrieval,...read more
What are the Applications of DBMS?
What are the Applications of DBMS?
DBMS (Database Management System) serves as a versatile tool, revolutionizing data analytics. With its applications in data storage, retrieval, and manipulation, it empowers businesses to make informed decisions, streamline operations,...read more

Candidate Key

Candidate Key is a set of attributes that can uniquely identify a table. A single table can have multiple candidate keys. Out of all the chosen candidate keys, one of the keys is selected as the primary key.

Example:

PatientID, MedInsuranceNo, AadharNo can be chosen as the candidate keys from the Patients table.

Rules for Candidate Key

  • Unique values must be present in all columns, chosen as candidate key
  • A single table can have multiple candidate keys
  • Null values can be present in the column chosen as candidate key

Candidate Key Operations

In this section of the article let us understand the various operations of candidate keys.

  • Apply Candidate Key while creating table
  • Alter a Candidate Key of a table
  • Drop a Candidate Key of a table

Apply Candidate Key while creating table

[code]
CREATE TABLE Patients (
PatientID INT UNIQUE,
PatientName VARCHAR(255),
Sex VARCHAR(255),
Age VARCHAR(255),
MedInsuranceNo VARCHAR(255) UNIQUE,
AadharNo INT UNIQUE,
);
[/code]
 

Alter a Candidate Key of a table

[code]
–Single COLUMN
ALTER TABLE Patients
ADD UNIQUE (PatientID);
 
–Multiple COLUMNS
ALTER TABLE Patients
ADD UNIQUE CK_Patient(PatientID, MedInsuranceNo, AadharNo);
[/code]
 

Drop a Candidate Key of a table

[code]
ALTER TABLE Patients
DROP CONSTRAINT CK_Patient;
[/code]
 

Primary Key

Primary Key is a set of attributes that can uniquely identify a table. A single table can have only one primary key. Out of all the chosen candidate keys, one of the keys is selected as the primary key.

Example:

PatientID, MedInsuranceNo, AadharNo are chosen as the candidate keys from the Patients table. Either of them can be chosen as a Primary key.

Rules for Primary Key

  • Unique values must be present in all columns, chosen as primary key
  • A single table can have only one primary key
  • No NULL value must be present in the column chosen as primary key
  • A new row cannot be inserted with an existing primary key

Primary Key Operations

In this section of the article let us understand the various operations of primary keys.

  • Apply Primary Key while creating table
  • Apply Primary Key on multiple columns
  • Alter a Primary Key of a table
  • Drop a Primary Key of a table

Apply Primary Key while creating table

[code]
#SQL Server/ MS Access/ Oracle
CREATE TABLE Patients (
PatientID INT NOT NULL PRIMARY KEY,
PatientName VARCHAR(255) NOT NULL,
Sex VARCHAR(255),
Age VARCHAR(255),
MedInsuranceNo VARCHAR(255,
AadharNo INT
);
 
#MySQL
CREATE TABLE Patients (
PatientID INT,
PatientName VARCHAR(255),
Sex VARCHAR(255),
Age VARCHAR(255),
MedInsuranceNo VARCHAR(255),
AadharNo INT,
PRIMARY KEY (PatientID)
);
[/code]
 

Apply Primary Key on multiple columns

[code]
CREATE TABLE Patients (
PatientID INT,
PatientName VARCHAR(255),
Sex VARCHAR(255),
Age VARCHAR(255),
MedInsuranceNo VARCHAR(255),
AadharNo INT,
CONSTRAINT PK_Patient PRIMARY KEY (PatientID, MedInsuranceNo)
);
[/code]
 

Alter a Primary Key of a table

[code]
–Single COLUMN
ALTER TABLE Patients
ADD PRIMARY KEY (PatientID);
 
–Multiple COLUMNS
ALTER TABLE Patients
ADD CONSTRAINT PK_Patient PRIMARY KEY (PatientID, MedInsuranceNo, AadharNo);
[/code]
 

Drop a Primary Key of a table

[code]
#SQL Server/ MS Access/ Oracle
ALTER TABLE Patients
DROP CONSTRAINT PK_Patient;
 
#MySQL
ALTER TABLE Patients
DROP PRIMARY KEY;
[/code]
 

Alternate Key

As the name suggests, all the candidate keys which are not selected as primary keys are known as the Alternate Key.

Example:

If the PatientID is selected as the Primary Key, then the MedInsuranceNo and the AadharNo are known as the alternate keys.

Rules for Alternate Key

  • Unique values must be present in all columns, chosen as alternate key
  • The alternate key is a part of candidate key but is not connected to primary key
  • In case a table contains a single candidate key then it will be chosen as the primary key. In that case there won’t be any alternate key.
  • It is defined by the UNIQUE keyword

Apply Alternate Key while creating table

[code]
CREATE TABLE Patients (
PatientID INT PRIMARY KEY,
PatientName VARCHAR(255),
Sex VARCHAR(255),
Age VARCHAR(255),
MedInsuranceNo VARCHAR(255) UNIQUE,
AadharNo INT
);
[/code]
 

Unique Key

The Unique key is quite similar to primary keys in a database. The only difference is that the unique keys allow a single NULL value in the column and must not have any duplicate values. 

Example:

MedInsuranceNo can be considered as a unique key.

Apply Unique Key while creating table

[code]
CREATE TABLE Patients (
PatientID INT,
PatientName VARCHAR(255),
Sex VARCHAR(255),
Age VARCHAR(255),
MedInsuranceNo VARCHAR(255),
AadharNo INT,
UNIQUE (MedInsuranceNo)
);
[/code]
 

Alter a Unique Key of a table

[code]
–Single COLUMN
ALTER TABLE Patients
ADD UNIQUE (PatientID);
 
–Multiple COLUMNS
ALTER TABLE Patients
ADD CONSTRAINT UK_Patient UNIQUE (PatientID, MedInsuranceNo, AadharNo);
[/code]
 

Foreign Key

Primary Key is a set of attributes that can take values referenced to the values of another table. 

Example:

PatientID in the CheckupDetails table is referred to the PatientID in the Patients table.

Rules for Foreign Key

  • Relationship between both the tables is known as referential integrity.
  • A single table can have multiple foreign keys
  • A foreign key can have NULL values.
  • You can duplicate foreign keys
  • The table consisting of the foreign key is known as the CHILD table and the table that is referred by the foreign key is called the parent table.

Foreign Key Operations

In this section of the article let us understand the various operations of foreign keys.

  • Apply Foreign Key while creating table
  • Apply Foreign Key on multiple columns
  • Alter a Foreign Key of a table
  • Drop a Foreign Key of a table

Apply Foreign Key while creating table

[code]
CREATE TABLE CheckupDetails (
PatientID INT NOT NULL,
AnnualCheckUpMonth VARCHAR(255),
Price INT,
PRIMARY KEY (PatientID),
FOREIGN KEY (PatientID) REFERENCES Patients(PatientID) 
);
[/code]
 

Apply Foreign Key on multiple columns

[code]
CREATE TABLE CheckupDetails (
PatientID INT NOT NULL,
AnnualCheckUpMonth VARCHAR(255),
Price INT,
PRIMARY KEY (PatientID),
CONSTRAINT FK_Checkup FOREIGN KEY (PatientID) REFERENCES Patients(PatientID) 
);
[/code]
 

Alter a Foreign Key of a table

[code]
–Single COLUMN
ALTER TABLE CheckupDetails
ADD FOREIGN KEY (PatientID)REFERENCES Patients(PatientID);
 
–Multiple COLUMNS
ALTER TABLE CheckupDetails
ADD CONSTRAINT FK_Checkup FOREIGN KEY  Patients(PatientID);
[/code]
 

Drop a Foreign Key of a table

[code]
#SQL Server/ MS Access/ Oracle
ALTER TABLE CheckupDetails
DROP CONSTRAINT FK_Checkup;
 
#MySQL
ALTER TABLE CheckupDetails
DROP FOREIGN KEY;
[/code]
 

Composite Key

As the  name suggests a composite key is a combination of multiple columns that can uniquely identify tuples.

Example:

PaitentID and AnnualCheckupMonth can be considered together as a composite key.

Apply Composite Key on multiple columns

[code]
CREATE TABLE Patients (
PatientID INT,
PatientName VARCHAR(255),
Sex VARCHAR(255),
Age VARCHAR(255),
MedInsuranceNo VARCHAR(255),
AadharNo INT,
PRIMARY KEY (PatientID, MedInsuranceNo)
);
[/code]
 

Conclusion

With this, we end this article on the various keys used in the database. We hope you found it informative. You can refer to the article on MySQL Commands for a detailed walkthrough of all commands.

Top Trending Articles:

Data Analyst Interview Questions | Data Science Interview Questions | Machine Learning Applications | Big Data vs Machine Learning | Data Scientist vs Data Analyst | How to Become a Data Analyst | Data Science vs. Big Data vs. Data Analytics | What is Data Science | What is a Data Scientist | What is Data Analyst

FAQs

What is the full form of DBMS?

DBMS full form is Database Management System.

What is a Primary Key in a database?

A Primary Key is a unique identifier for each record in a table. It ensures data integrity by guaranteeing that no two records have the same key value.

What is the role of a Foreign Key in a database?

A Foreign Key establishes relationships between tables by referencing the Primary Key of another table. It ensures referential integrity and helps maintain data consistency across related tables.

What are Candidate Keys and their significance?

Candidate Keys are potential Primary Keys that meet the criteria of uniqueness and minimality. They offer alternative choices for Primary Keys and help maintain data integrity.

How do Composite Keys work in databases?

Composite Keys are formed by combining multiple columns to create a unique identifier for a record. They are useful when no single column can uniquely identify a record.

About the Author

This is a collection of insightful articles from domain experts in the fields of Cloud Computing, DevOps, AWS, Data Science, Machine Learning, AI, and Natural Language Processing. The range of topics caters to upski... Read Full Bio