Keys in DBMS

Download as docx, pdf, or txt
Download as docx, pdf, or txt
You are on page 1of 5

Keys in DBMS

In Database Management Systems (DBMS), different types of keys are used to uniquely
identify records, establish relationships between tables, and ensure data integrity. Each key
serves a specific role in the database design.

1. Primary Key

A Primary Key is a unique identifier for a record in a table. It ensures that each row in the table
is unique and non-null. A table can have only one primary key, which can consist of one or more
columns (a composite primary key).

Example:

Consider a Students table:

Student_ID Student_Name Age


101 Alice 20
102 Bob 22
103 Charlie 19

 Here, Student_ID is the primary key, as it uniquely identifies each student and cannot
be null.

2. Candidate Key

A Candidate Key is a column or a set of columns that can uniquely identify a record in a table.
There can be multiple candidate keys in a table, but one of them is chosen as the Primary Key.
All candidate keys should have unique and non-null values.

Example:

Consider the Employees table:

Employee_ID Email Name


201 alice@company.com Alice
202 bob@company.com Bob
203 charlie@company.com Charlie

Faculty of computer science and digital innovation 1


 Employee_ID and Email are both Candidate Keys, as both uniquely identify each
employee.
 We may choose Employee_ID as the primary key, and Email would remain a candidate
key.

3. Super Key

A Super Key is any combination of columns that can uniquely identify a record in a table. Every
Primary Key and Candidate Key is a super key, but not every super key is minimal (i.e., it may
contain extra attributes).

Example:

In the Students table:

Student_ID Student_Name Age


101 Alice 20
102 Bob 22
103 Charlie 19

 Student_ID alone is a super key.


 A combination of Student_ID and Student_Name is also a super key, but it is not
minimal because Student_ID alone can uniquely identify the record.

4. Composite Key

A Composite Key is a primary key made up of two or more columns that together uniquely
identify a record. A composite key is used when a single column is not sufficient to ensure
uniqueness.

Example:

Consider a Student_Course table:

Faculty of computer science and digital innovation 2


Student_ID Course_ID Grade
101 C001 A
101 C002 B
102 C001 A

 Here, the combination of Student_ID and Course_ID forms a Composite Key that
uniquely identifies each record, as neither of them alone is sufficient.

5. Foreign Key

A Foreign Key is a column (or set of columns) in one table that refers to the Primary Key of
another table. It creates a relationship between two tables, enforcing referential integrity. The
foreign key ensures that the value in one table corresponds to a valid record in another table.

Example:

Consider a Departments table and an Employees table:

Departments Table:

Dept_ID Dept_Name
D001 HR
D002 Finance

Employees Table:

Employee_ID Name Dept_ID


101 Alice D001
102 Bob D002

 Dept_ID in the Employees table is a Foreign Key that references the Primary Key
Dept_ID in the Departments table, creating a relationship between employees and
departments.

6. Alternate Key

Faculty of computer science and digital innovation 3


An Alternate Key is any Candidate Key that is not chosen as the Primary Key. There can be
multiple candidate keys, but only one is selected as the primary key, while the rest are alternate
keys.

Example:

In the Employees table:

Employee_ID Email Name


201 alice@company.com Alice
202 bob@company.com Bob
203 charlie@company.com Charlie

 Here, both Employee_ID and Email are candidate keys.


 If Employee_ID is chosen as the primary key, then Email becomes an Alternate Key.

7. Unique Key

A Unique Key is a constraint that ensures all values in a column or a set of columns are unique
across rows. Unlike a primary key, a unique key can accept a null value.

Example:

In the Students table:

Student_ID Email Name


101 alice@mail.com Alice
102 NULL Bob
103 charlie@mail.com Charlie

 Here, Email is a Unique Key because no two rows can have the same email, but it can
accept NULL for one or more rows.

8. Null Key

Faculty of computer science and digital innovation 4


A Null Key refers to a key that can have NULL values in a database column. Only Unique Keys
(but not Primary Keys) allow null values, but if a column is designated as a Primary Key, it
cannot have null values.

9. Surrogate Key

A Surrogate Key is an artificially generated key, usually an integer or unique identifier (e.g.,
UUID), which is used as a unique identifier when no suitable natural key exists. Surrogate keys
are often automatically generated by the DBMS.

Example:

Consider a Customer table where no existing columns provide a good unique identifier. A
Surrogate Key like Customer_ID can be created:

Customer_ID Name Phone_Number


1 Alice 123-4567
2 Bob 987-6543

 Customer_ID is a surrogate key created by the DBMS, with no meaning outside the
database.

Summary of Keys in DBMS:

 Primary Key: Uniquely identifies a record in a table (no duplicates, no nulls).


 Candidate Key: A column (or set of columns) that can uniquely identify records; a
candidate for the primary key.
 Super Key: Any combination of columns that can uniquely identify a record.
 Composite Key: A key formed by the combination of two or more columns to uniquely
identify a record.
 Foreign Key: Establishes a relationship between two tables, referring to the primary key
of another table.
 Alternate Key: A candidate key that is not chosen as the primary key.
 Unique Key: Ensures that all values in a column are unique, allowing nulls.
 Surrogate Key: A system-generated unique identifier, often used when no natural key
exists.

Faculty of computer science and digital innovation 5

You might also like