Keys in DBMS
Keys in DBMS
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:
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:
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:
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:
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:
Departments Table:
Dept_ID Dept_Name
D001 HR
D002 Finance
Employees Table:
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
Example:
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:
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
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 is a surrogate key created by the DBMS, with no meaning outside the
database.