📙 Keys in DBMS
Keys in DBMS (Database Management System) are attributes or fields in a database table that are used to establish relationships between different tables or records. They are used to uniquely identify a record or a row in a table and are essential for maintaining the integrity and consistency of the database. There are several types of keys in DBMS, including primary keys, foreign keys, candidate keys, and superkeys. Primary keys are unique identifiers for a table and are used to link records from one table to another. Foreign keys are used to establish a relationship between tables, while candidate keys are alternate keys that can be used as primary keys. Superkeys are a combination of attributes that can uniquely identify a record or row in a table.
📙 Why Keys are important ?
Here are some key points about why keys are important in a database management system (DBMS):
Keys in a DBMS are used to identify and access specific rows or records in a database table.
They ensure the uniqueness of data in a table and prevent duplicate entries.
Keys can also be used to enforce data integrity and relationships between tables.
Primary keys are used to uniquely identify each row in a table and are typically indexed for faster access.
Foreign keys are used to establish relationships between tables and ensure referential integrity.
Indexes, which are often based on keys, can significantly improve query performance by allowing the DBMS to quickly locate specific rows or records.
Without keys, accessing and manipulating data in a database would be inefficient and error-prone.
Keys play a critical role in ensuring the accuracy and consistency of data in a DBMS.
In summary, keys are an essential component of any DBMS and are crucial for efficient and accurate data management.
📙 Types of Keys
In a DBMS (Database Management System), there are several types of keys that are used to identify and access specific rows or records in a database table. Here are the main types of keys:
✔ Primary Key
A primary key is a unique identifier for each row in a table. It ensures that each row in a table is uniquely identified and that no two rows can have the same primary key. Primary keys can be made up of one or more columns in a table. Here's an example of a primary key:
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(50),
customer_email VARCHAR(50)
);
In this example, the primary key is the customer_id column, which is a unique identifier for each customer in the table.
Important points to be remembered for primary keys are -
Uniqueness: Primary keys are unique identifiers that ensure that each row in a table is uniquely identified. This means that no two rows in the table can have the same values for the primary key column(s).
Null Values: Primary keys cannot contain null values. This means that each row in the table must have a value for the primary key column(s).
Indexing: Primary keys are automatically indexed by the DBMS to ensure faster data retrieval. This allows for efficient searching and sorting of data based on the primary key column(s).
Constraints: Primary keys can be used to enforce constraints on the data in the table. For example, if a table has a primary key on a column called "employee_id", then no two rows in the table can have the same "employee_id" value.
Relationship: Primary keys are used to create relationships between tables. For example, if a table called "orders" has a primary key on a column called "order_id", and another table called "order_details" has a foreign key on the "order_id" column, then the two tables are related to each other based on the "order_id" column.
Alteration: Primary keys cannot be altered or deleted if they have dependent foreign keys. This means that if a table has a primary key on a column, and another table has a foreign key on the same column, then the primary key cannot be changed or deleted without also changing or deleting the foreign key.
✔ Foreign Key
A foreign key is a column or a set of columns that refer to the primary key of another table. It establishes a relationship between two tables, allowing you to retrieve data from multiple tables at once. Here's an example of a foreign key:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
In this example, the customer_id column in the orders table is a foreign key that references the customer_id column in the customers table. This establishes a relationship between the two tables, allowing you to retrieve data from both tables at once.
✔ Unique Key
A unique key is a constraint that ensures that each value in a column is unique. Unlike a primary key, a unique key can have NULL values. Here's an example of a unique key:
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(50),
employee_email VARCHAR(50) UNIQUE
);
In this example, the employee_email column is a unique key that ensures that each email address is unique. It allows for NULL values, meaning that multiple employees can have no email address.
✔ Composite Key
A composite key is a primary key made up of two or more columns. It ensures that each combination of values in the columns is unique. Here's an example of a composite key:
CREATE TABLE orders (
order_id INT,
customer_id INT,
order_date DATE,
PRIMARY KEY (order_id, customer_id)
);
In this example, the primary key is a composite key made up of the order_id and customer_id columns. It ensures that each combination of order ID and customer ID is unique.
✔ Candidate Key
A candidate key is a column or set of columns in a table that can uniquely identify each row in the table. It can be used as a primary key, but a table can have multiple candidate keys. Here's an example of a candidate key:
CREATE TABLE books (
book_id INT,
isbn VARCHAR(13) UNIQUE,
title VARCHAR(50),
author VARCHAR(50),
PRIMARY KEY (book_id)
);
In this example, the isbn column is a candidate key, as each book has a unique ISBN number. However, the book_id column is used as the primary key.
✔ Super Key
A super key is a combination of columns that can uniquely identify each row in a table. It may contain more columns than necessary to create a unique identifier. Here's an example of a super key:
CREATE TABLE products (
product_id INT,
product_name VARCHAR(50),
category VARCHAR(50),
price DECIMAL(10, 2),
UNIQUE (product_id, product_name)
);
In this example, the combination of product_id and product_name is a super key, as it can uniquely identify each product in the table. However, it contains more columns than necessary, as product_id alone is sufficient to create a unique identifier.
✔ Alternate Key
An alternate key is a column or set of columns that can be used as a primary key but are not currently being used as such. Here's an example of an alternate key:
CREATE TABLE students (
student_id INT,
student_name VARCHAR(50),
email VARCHAR(50) UNIQUE,
phone VARCHAR(20) UNIQUE,
PRIMARY KEY (student_id)
);
In this example, the email and phone columns are alternate keys, as they can be used as primary keys but are not currently being used as such.
✔ Surrogate Key
A surrogate key is an artificially created column used as the primary key when no natural key exists or when the natural key is not suitable. It is usually a unique, auto-generated value assigned by the system. Here's an example of a surrogate key:
CREATE TABLE employees (
employee_id INT PRIMARY KEY AUTO_INCREMENT,
employee_name VARCHAR(50),
employee_email VARCHAR(50) UNIQUE
);
In this example, the employee_id column is a surrogate key, as it is an artificially created value assigned by the system and used as the primary key for the table.
✔ Non-Clustered Index Key
A non-clustered index key is a column or set of columns that are used to create a non-clustered index on a table. It is used to speed up queries that require sorting or searching data based on specific columns. Here's an example of a non-clustered index key:
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(50),
customer_email VARCHAR(50),
INDEX email_idx (customer_email)
);
In this example, the customer_email column is used to create a non-clustered index on the customers table, which speeds up queries that require searching or sorting data based on email addresses.
✔ Clustered Index Key
A clustered index key is a column or set of columns that are used to create a clustered index on a table. It determines the physical order of the data in the table and is used to speed up queries that require retrieving ranges of data. Here's an example of a clustered index key:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
INDEX customer_idx (customer_id)
) CLUSTERED BY (customer_id);
In this example, the customer_id column is used to create a clustered index on the orders table, which determines the physical order of the data in the table based on the customer ID. This speeds up queries that require retrieving data for specific customers or ranges of customers.
📙 Primary Key Vs. Foreign Key
Differences between primary key and foreign key are -
I hope the information provided was helpful. If you have any further questions or need additional assistance, feel free to ask in comments!
Thanks for reading, and happy coding!
Exploring the Database Keys: Understanding Their Significance -> Understanding Functional Dependency in DBMS: Definition, Types, and Examples