6 Types of Keys in Database
Learn about the different types of keys used in databases, including primary keys, foreign keys, composite keys, and more. Understand their function and how they help maintain data integrity and relationships between tables.
There are different types of keys in a database. This table shows the name of keys and definition.
| Key Type | Definition |
|---|---|
| Primary key | A unique identifier for each record in a table. It cannot have a null value and must be unique across the entire table. |
| Foreign key | A field in a table that matches the primary key of another table. It creates a link between the two tables and is used to establish a relationship between them. |
| Composite key | A combination of two or more columns used as a primary key. It is used when a single column is not sufficient to uniquely identify a record. |
| Alternate key | A unique identifier for a record that is not the primary key. It can be used as a secondary way to look up data in the table. |
| Candidate key | A unique identifier for a record in a table that could potentially be used as a primary key. |
| Surrogate key | A unique identifier signed to a record in a table, typically by the database itself, rather than by the user. |
Primary Key
A primary key is a unique identifier for each record in a table. It is used to ensure that each record can be uniquely identified and retrieved from the table. The primary key is a constraint that ensures that the key cannot have a null value and must be unique across the entire table.
For example, in a table of customers, the customer ID could be the primary key. Each customer in the table would have a unique ID, and this ID could be used to identify and retrieve specific customer records.
Another example, in a library system, the ISBN number of a book can be the primary key, that way, each book can be uniquely identified and retrieved by its ISBN number.
In general, primary key is the main way to identify and retrieve specific records, and it is used as a reference point for other tables in a relational database.
Foreign Key
A foreign key is a field in a table that matches the primary key of another table. It creates a link between the two tables and is used to establish a relationship between them. A foreign key is used to ensure referential integrity, which means that the data in the related tables is consistent and accurate.
For example, in a database of orders, the customer ID field could be a foreign key that references the primary key of the customers table. This would allow the database to link each order to the corresponding customer. This way the database can ensure that each order is associated with a valid customer and that any updates or deletions to a customer record are automatically propagated to the related orders.
Another example, in a school database, a foreign key can be used to relate a student ID to a class ID. This way, the database can ensure that each student is associated with a valid class and that any updates or deletions to a class record are automatically propagated to the related students.
The foreign keys are used to establish relationships between tables, and they help to ensure the consistency and accuracy of the data in a relational database.
Composite Key
A composite key, also known as a compound key or a concatenated key, is a primary key that is made up of two or more columns in a database table. The combination of values in these columns must be unique for each row in the table.
For example, in a database of students at a school, the primary key for the students table may be made up of a combination of the student’s first name and last name, since each student’s first and last name combination is unique. Or in case of Employee table, Employee_id and Employee_code both can be composite keys.
Alternate Key
An alternate key, also known as a secondary key or a candidate key, is a key other than the primary key that can be used to uniquely identify a row in a database table. Alternate keys are used as a backup in case the primary key is not available or is not suitable for a particular query or operation.
For example, in a database of employees, the primary key for the employees table may be the employee ID, but an alternate key could be the employee’s social security number. This would allow for employees to be identified and queried using either their employee ID or social security number.
It’s important to note that alternate keys can be used as primary key if needed. They are not restricted to be used only as a backup key.
Candidate Key
A candidate key is a column or set of columns in a database table that can be used as the primary key. A table can have one or more candidate keys, but it must have at least one. A candidate key is a column or set of columns that contains unique values and has the ability to uniquely identify each row in a table.
For example, in a table of customers, a candidate key could be the customer ID, since each customer will have a unique ID and it can be used to identify a specific customer. Another example of a candidate key in the same table could be a combination of the customer’s first name and last name, since each combination of first and last name would be unique for each customer.
It’s important to note that a table can have multiple candidate keys, but only one primary key can be chosen among them. The Primary key is chosen from the candidate keys based on the best fit for the database and the data it holds.
Surrogate Key
A surrogate key is a unique identifier, typically a number, that is used as the primary key in a database table. Unlike natural keys, which are based on the real-world properties of the entities being represented in the table, surrogate keys are artificial keys that are created solely to serve as a unique identifier for each row in the table.
The main advantage of using a surrogate key is that it is independent of the data and does not change. For example, if a customer changes their name, their natural key (e.g. first name and last name) will change as well, making it difficult to update the data in the table and maintain the relationships between tables. However, the surrogate key would remain the same, making it easy to update the data and maintain the relationships.
Surrogate keys are often used in conjunction with a unique, non-nullable natural key, which is known as a natural key. The natural key is used to perform operations on the table, while the surrogate key is used to maintain relationships between tables.
A common example of surrogate key is auto-incrementing integer as primary key in a table.
More to Read
- Relational Database Benefits and Limitations
- Relational Vs Non Relational Database
- Data Warehouse vs Database
- Dataset vs Database
- Database vs DataFrame
- Database Metadata
- Postgres Schema vs Database
- Relational Database vs Flat File
- Primary Key vs Foreign Key
- Primary Key vs Candidate Key
- Document Database Vs. Key Value Store
- Document Database Vs. Relational Database
- 13 Examples of Relational Database
- Relational Database Vs. Object-Oriented Database
- 9 Types of Databases
- Distributed Database
- Operational Database
- Personal Database
- Graph Databases
- Document Database
- Centralized Database
