In Relational Database Management Systems (RDBMS), keys play a crucial role in defining and ensuring the integrity of data. Keys are used to identify and establish relationships between tables. Here are the key types of keys in RDBMS:

1. Primary Key (PK):

A primary key is a unique identifier for each record (tuple) within a table. It ensures that each row in the table is uniquely identifiable. Primary keys have the following characteristics:

- Uniqueness: No two rows within a table can have the same primary key value.
- Non-nullability: A primary key value cannot be null (empty).
- Single-valued: Each row has a unique primary key value.
- Stability: The primary key value should be stable and not subject to frequent changes.

By specifying a primary key, you enforce data integrity and provide a means to identify and retrieve individual rows from a table efficiently.

This makes it far easier to search for any particular record in any given table. It also makes it much easier to identify a particular record that may have certain data in common with other records.

For instance, let’s say an administrator at a doctor’s office wants to find your medical records based on your name. This would prove to be difficult if you had the same name as another patient, perhaps a parent.

The solution to this problem would be to give the administrator additional data such as date of birth to further differentiate these records.

However, a better solution would be to give them a Social Security Number (SSN) as that would certainly be unique for each record.

That’s why we designate the primary key as something that’s unique for each record.

Primary Key Example

Let’s take a look inside the database for a University. One of the tables in that database would certainly be dedicated to maintaining the records of its students.

The fields within the table would probably vary slightly, but would certainly contain at least the names of each student, their unique student ID, and some contact information such as an email.

Employees Table:

EMPLOYEE ID	FIRST NAME	LAST NAME	EMAIL
1	John	smith	John.smith@example.com
2	Lisa	Johnson	lisa.johnson@example.com
3	LiMichael	Davis	Michael.davis@example.com

Now that we have our fields, we can designate one of the fields as the primary key. I’ve already mentioned why it’s not a good idea to choose a name as a primary key because as you can see, names aren’t always unique.

Additionally, it’s also not a good idea to choose email as the primary key, although it’s likely to be unique. That leaves us with only one option to choose: the StudentID.

Why Not Use Email As The Primary Key?

There are several reasons why it’s a bad idea to use an email address as a primary key.

1. A primary key should be static. This means something that shouldn’t change. Email addresses can be changed so they are not static.
2. Email addresses can be shared by multiple users.
3. String comparisons are slower than integer comparisons. Thus, it will take slightly longer to search for records within large databases.

2. Foreign Key (FK):

In a relational database management system (RDBMS), a foreign key is a column or set of columns in a table that refers to the primary key of another table. It establishes a relationship between two tables by enforcing referential integrity and maintaining consistency between related data.

Here's an example to illustrate the concept of a foreign key:

Let's consider two tables: "Orders" and "Customers." The "Orders" table has columns such as OrderID, OrderDate, CustomerID, and OrderTotal. The "Customers" table has columns like CustomerID, FirstName, LastName, and Email.

Orders Table:

ORDER ID	ORDER DATE	CUSTOMER ID	ORDER TOTAL
1	2023-07-100	101	$150
2	2023-07-12	102	$200
3	2023-07-14	101	$80

Customers Table:

CUSTOMER ID	FIRST NAME	LAST NAME	EMAIL
101	JOHN	SMITH	John.smith@example.com
102	LISA	JOHNSON	Lisa.johnson@example.com

In this example, the CustomerID column in the "Orders" table is a foreign key that references the CustomerID column in the "Customers" table. It establishes a relationship between the two tables, indicating that the CustomerID in the "Orders" table corresponds to a specific customer in the "Customers" table.

The foreign key constraint ensures that the values in the CustomerID column of the "Orders" table exist in the CustomerID column of the "Customers" table. This constraint maintains referential integrity, preventing the creation of orphaned records or invalid references.

For example, when inserting a new order into the "Orders" table, if you attempt to specify a CustomerID that does not exist in the "Customers" table, the RDBMS will raise an error or reject the operation, ensuring data consistency.

Foreign keys allow you to establish relationships between tables and enable powerful operations like joining tables to retrieve related data. You can use foreign keys to perform queries that involve multiple tables, such as retrieving all orders along with their corresponding customer information.

Overall, foreign keys play a vital role in maintaining data integrity and enforcing relationships between tables in an RDBMS.

3. Unique Key:

A unique key ensures that the values in a specific column or set of columns are unique within a table. Unlike primary keys, unique keys allow null values. Unique keys are used to enforce uniqueness but do not serve as the primary means of identifying individual rows.

In a relational database management system (RDBMS), a unique key is a column or set of columns that ensures the uniqueness of values within a table. It allows for the identification of individual records based on their unique characteristics, similar to a primary key. However, unlike a primary key, a unique key does not necessarily serve as the primary means of identifying records or enforcing referential integrity.

Here's an example to illustrate the concept of a unique key:

Let's consider a table called "Students" with columns such as StudentID, FirstName, LastName, and Email. In this case, we want to ensure that the email addresses of all students are unique.

Students Table:

STUDENT ID	FIRST NAME	LAST NAME	EMAIL
1	John	Smith	John.smith@example.com
2	Lisa	Johnson	lisa.johnson@example.com
3	Michael	Davis	Michael.davis@example.com

To enforce uniqueness on the "Email" column, you can define a unique key constraint on that column. This constraint ensures that each email address can only appear once in the table.

For example, if you try to insert a new record with an email address that already exists in the table, the RDBMS will raise an error or reject the operation, maintaining the uniqueness of the "Email" column.

Unique keys have the following properties:

1. Uniqueness: Each value in the unique key column (or set of columns) must be unique within the table. It ensures that no two records have the same value(s) in the specified column(s).

2. Nullability: Unique keys can allow null values. However, if a column with a unique key constraint allows nulls, multiple null values are typically allowed, as nulls are not considered equal to each other.

Unique keys provide a way to identify and retrieve specific records based on unique attributes other than the primary key. They can also be used to enforce business rules or restrictions on specific columns, such as ensuring the uniqueness of email addresses or identification numbers.

In the "Students" table example, the unique key constraint on the "Email" column allows you to query for specific students using their email addresses efficiently. However, it's important to note that a unique key does not provide the same level of identification and relationship enforcement as a primary key.

Overall, unique keys serve to maintain data integrity by ensuring the uniqueness of values within a table, while allowing for efficient retrieval and querying based on those unique attributes.

4. Candidate Key:

In a relational database management system (RDBMS), a candidate key is a column or set of columns in a table that can uniquely identify each record (row) within that table. Candidate keys are potential choices for the primary key of a table.

Here's an example to illustrate the concept of a candidate key:

Consider a table called "Students" with columns such as StudentID, SocialSecurityNumber (SSN), and Email. In this scenario, both StudentID and SSN can uniquely identify each student in the table.

Students Table:

STUDENT ID	SSN	EMAIL	FIRST NAME	LAST NAME
1 123-45-6789	John.smith@example.com	john	smith
2 987-65-4321	Lisa.johnson@example.com	lisa	johnson
3 456-78-9012	Michael.davis@example.com	Michael	Davis

In this example, both StudentID and SSN can serve as candidate keys for the "Students" table. Each of these columns uniquely identifies each student, meaning no two students can have the same StudentID or SSN.

Candidate keys have the following properties:

1. Uniqueness: Each value in a candidate key column (or set of columns) must be unique within the table. It ensures that no two records have the same value(s) in the specified column(s).

2. Minimality: A candidate key should be minimal, meaning no subset of the candidate key columns can also uniquely identify records. It ensures that no extra columns are included in the key.

In the "Students" table example, both StudentID and SSN satisfy the properties of candidate keys. However, it's important to note that while multiple candidate keys may exist in a table, only one of them is typically chosen as the primary key.

The primary key is selected from the available candidate keys and serves as the primary means of identifying and accessing individual records. The choice of the primary key depends on various factors, such as uniqueness, stability, and simplicity.

In some cases, it may be preferable to use an artificial primary key, such as an auto-incrementing ID, even if there are natural candidate keys available. This can provide additional benefits like simplicity and independence from potentially sensitive or changing data.

Candidate keys play a crucial role in database design by providing options for uniquely identifying records within a table. They help ensure data integrity and serve as the basis for establishing relationships with other tables through foreign keys.

5. Composite Key:

In a relational database management system (RDBMS), a composite key is a key that consists of two or more columns in a table. Unlike a simple or single-column key, a composite key uses multiple columns to uniquely identify records within a table.

Here's an example to illustrate the concept of a composite key:

Consider a table called "Sales" with columns such as OrderID, ProductID, and SaleDate. In this scenario, the combination of OrderID and ProductID uniquely identifies each sale.

Sales Table:

ORDER ID	PRODUCT ID	SALE DATE	QUANTITY
1	101	2023-07-10	5
1	102	2023-07-10	3
2	101	2023-O7-12	2
3	103	2023-07-14	1

In this example, the composite key is formed by combining the OrderID and ProductID columns. The combination of these two columns must be unique for each record in the table. This means that no two records can have the same OrderID and ProductID values.

Using a composite key allows for more precise identification and retrieval of records based on multiple attributes. In this case, it ensures that each sale, identified by its OrderID and ProductID combination, can be uniquely distinguished from others.

Queries involving the composite key can include conditions based on both OrderID and ProductID. For example, to retrieve the details of the sale with OrderID 1 and ProductID 101, you would use a query like:
Composite keys are useful when a single column cannot provide the necessary uniqueness or when multiple attributes are needed to identify records. They help maintain data integrity by ensuring that no duplicate combinations of the key attributes exist within the table.

It's important to note that while composite keys provide a higher level of uniqueness, they can also introduce increased complexity and potential performance considerations. Therefore, it's crucial to carefully consider the design and usage of composite keys based on specific requirements and constraints within your database.

About the Author

Silan Software is one of the India's leading provider of offline & online training for Java, Python, AI (Machine Learning, Deep Learning), Data Science, Software Development & many more emerging Technologies.

We provide Academic Training || Industrial Training || Corporate Training || Internship || Java || Python || AI using Python || Data Science etc