7 Types of Database Keys and Their Functions
There are several types of database keys in a database, generally such as primary key, foreign key, and unique key. Each key plays an important role in operating the database.
Each of these keys has a different function in data structure and management. Understanding the various types of database keys is essential for ensuring data integrity and facilitating relationships between tables.
Understanding the types of database keys in a database is essential for developers and system administrators. This article provides complete information about all types of database keys and their functions.
1. Primary Key
A primary key is a type of main key in a database that is responsible for uniquely identifying each row in a table. Its main function is to ensure that there is no duplication of data entered into the table.
Each table can only have one primary key consisting of one or more columns. When setting a primary key, there must be no null values to ensure that each row has a consistent unique identifier.
You can find a prime example of the application of a primary key in a school management system. In this system, the Student Identification Number (SNI) provides unique identification for each student in the database.
Also Read: Import MySQL database by command line and phpMyAdmin
2. Super Key
A super key is a collection of attributes in a table that can uniquely identify a row. There is no limit to the number of attributes a super key can have, but the combination of attributes must be capable of unique identification.
Super keys can be simple or complex and can have only one column or even combine several columns. In the context of an employee table, for example, the combination of “NIK,” “Nama,” and “Email” can function as a super key, providing more flexibility when determining candidate keys.
3. Candidate Key
A candidate key is a super key without redundant attributes, meaning every attribute in the candidate key is necessary to ensure the entity’s uniqueness. A table can have more than one candidate key, providing more alternatives when determining the primary key.
If there are two strong candidates for the primary key in the student table, for example between “NIM” and “Email.” Both can serve as candidate keys, but typically, only one gets chosen as the primary key based on system needs.
4. Alternate Key
Alternate keys appear when there is more than one candidate key in a table, and one candidate key has been selected as the primary key. The function of alternate keys is to provide an alternative way of referencing or accessing data when access through the primary key is not ideal.
An example occurs in the employee table, where “NIK” serves as the primary key, and the “Email” attribute can act as an alternate key for identification.
Also Read: How to export MySQL database using Command Prompt or batch file
5. Foreign Key
A foreign key acts as a bridge connecting two different tables. In practice, a foreign key refers to the primary key of another table. Its function is to maintain the alignment and referential integrity between the two.
In a relational database context such as a purchasing system, the “Orders” table might have a “Customer ID” foreign key that references the primary key in the “Customers” table, associating each order with the relevant customer data.
6. Composite Key
We use a composite key when no single attribute is sufficient to perform unique identification of a row. So, we use a combination of several columns together to form a composite key.
An example is a database situation that records student evaluations of courses. The combination of “Student ID” and “Course ID” is used together as a composite key to make each entry unique.
7. Unique Key
A unique key ensures no duplication of data in the column where it is applied. Unlike a primary key, a unique key can contain null values but not more than one null value in different rows.
When implementing a unique feature that is not as important as a primary key, we use a unique key. For example, the phone number in the member table should be unique for each member.
Also Read: How to connect MySQL database in PHP – PHP MySQL
Do You Know All Types of Keys in a Database?
Those are the types of database keys in the database along with their functions and examples of their use. The primary key, super key, and foreign key perform the most crucial functions for database operations.
We use Primary keys when we need a unique identification, while foreign keys link tables. Understanding the different types of database keys and their applications will provide significant benefits in managing your database