What are DBMS-Keys

What is a Key in DBMS?

A key is an attribute (or a set of attributes) that helps us to identify a tuple (row) uniquely in a relation (table). Keys play a major role in ensuring that each record in a table remains unique, organized, and easily retrievable.

Types of Keys in DBMS (9 Total)

1. Primary Key

   • Definition: A column (or combination of columns) that uniquely identifies each row in a table.

   • Rules: Cannot be NULL; must be unique. –> Mandatory

   • If two or more attributes are eligible as primary keys then we can consider some good-to-go criteria:

     • Numerical would be great and easy to deal.
     • Should be as small as possible.

   • Example:

     • Table: Student
     • Attributes: StudentID, Name, Email, DOB
     • Primary Key: StudentID (Each student has a unique ID)

     StudentID	Name	Email	DOB
     101	Arjun	arjun@mail.com	2005-08-17
     102	Kavya	kavya@mail.com	2004-06-13

2. Candidate Key

   • Definition: All those attributes that can qualify as a primary key.
   • Example:
     • Table: Student
     • Candidate Keys: StudentID, Email (both uniquely identify a student)
     • Primary Key can be chosen from among them.
   • Note: Every Primary Key is a Candidate Key, but not every Candidate Key becomes Primary Key.

3. Super/Power Key

   • Definition: A set of one or more attributes that can uniquely identify a row.
   • Example: {StudentID}, {Aadhar}, {Email}, {StudentID, Email}, {StudentID, Aadhar}, {Aadhar, Email}, {StudentID, Aadhar, Email}.
   • All Candidate Keys are Super Keys, but not all Super Keys are Candidate Keys (because Super Keys can have extra attributes).

4. Alternate Key

   • Definition: The Candidate Keys which are NOT selected as the Primary Key.
   • Example: If StudentID is the Primary Key, then Email becomes an Alternate Key.

   \[ Alternate Key = Candidate Key - Primary Key \]

5. Composite Key

   • Definition: A key that consists of two or more attributes that together uniquely identify a record.

   • Example:

     • Table: Enrollment
     • Attributes: StudentID, CourseID, EnrollmentDate
     • Composite Key: {StudentID, CourseID}, {StudentID, EnrollmentDate}, {StudentID, EnrollmentDate, CourseID}
     • This is because a student can enroll in many courses and each course can have many students.

     StudentID	CourseID	EnrollmentDate
     101	CSE101	2023-08-01
     101	MTH202	2023-08-01

6. Compound Key

   • Definition: This is same as composite key but atleast one of the key must be a Foreign Key.
   • Example:
     • Let’s consider the same composite key as above but the compound key is the on which contains atleast one foreign key i.e, we can consider {StudentID, CourseID} as one of the compound key since we mostly select the StudentID as primary key which serves as a foreign key to another table.

7. Foreign Key

   • Definition: A field in one table that uniquely identifies a row of another table.
   • Used to: Establish a link between the data in two tables - Referential Integrity Maintenance.
   • You can say that one table’s primary key is the foreign key of other table
   • Example:
     • Table: Enrollment
     • Foreign Key: StudentID refers to Student(StudentID)
     • Ensures that no invalid StudentID is entered in the Enrollment table.

8. Unique Key

   • Definition: Ensures all values in a column are different. Unlike Primary Key, it can accept NULL (only one NULL allowed).
   • Example:
     • Table: Employee
     • Attributes: EmpID, Email, PANNumber
     • Email can be UNIQUE (but not primary), allowing for NULLs.

9. Surrogate Key

   • Definition: An artificial or synthetic key that has no business meaning. Just used to uniquely identify each row.

   • Often auto-generated by DBMS (like auto-increment ID)

   • Example:

     • Table: Orders
     • Attributes: OrderName, OrderDate -> From this we cannot choose anything as primary key, then surrogate comes in handy which is a synthetic key like OrderID which can be uniquely identified.
     • OrderID is a Surrogate Key; it’s just a unique number with no meaning.

     OrderID	OrderName	OrderDate
     1	Coffee	2023-12-03
     2	Tea	2023-12-03

Summary Table of Keys:

Tips to Remember:

• Primary is the main identifier.
• Candidate = Can be Primary.
• Alternate = Candidate not selected.
• Super = Extra attributes allowed.
• Composite = Multiple columns together.
• Compound = Filtration of Composite key by saying that a set must contain atleast one foreign key.
• Foreign = Refers to another table.
• Unique = Like Primary but allows NULL.
• Surrogate = Fake ID, system generated.