Types of Keys in Database

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.

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 Keys in Database
• Types of eys
  • Super Key
  • Candidate Key
  • Primary Key
  • Alternate Key
  • Unique Key
  • Foreign Key
  • Composite Key

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}

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