2-3 Keys in Relational Databases Explained

Key Concepts

• Primary Key
• Foreign Key
• Composite Key

Primary Key

A Primary Key is a unique identifier for each record in a table. It ensures that each row in the table is uniquely identifiable. A Primary Key cannot be null and must be unique across all rows in the table. It is often an integer or a UUID (Universally Unique Identifier).

Example: In a table named "Employees," the column "EmployeeID" could be designated as the Primary Key. Each employee would have a unique EmployeeID, ensuring that no two employees share the same ID.

Foreign Key

A Foreign Key is a field in one table that refers to the Primary Key in another table. It establishes a relationship between the two tables, ensuring referential integrity. Foreign Keys are used to create links between related data across different tables.

Example: In a table named "Orders," the column "CustomerID" could be a Foreign Key that references the "CustomerID" Primary Key in the "Customers" table. This relationship ensures that each order is linked to a valid customer.

Composite Key

A Composite Key is a Primary Key that consists of two or more columns. Together, these columns uniquely identify each record in the table. Composite Keys are useful when a single column cannot uniquely identify a record, but a combination of columns can.

Example: In a table named "Enrollments," the combination of "StudentID" and "CourseID" could form a Composite Key. Each student can enroll in multiple courses, and each course can have multiple students, but the combination of StudentID and CourseID uniquely identifies each enrollment record.

Examples and Analogies

Primary Key: Library Card

Think of a Primary Key as a library card number. Each library card is unique to a single person, ensuring that the library can identify and manage each patron's records without confusion.

Foreign Key: Book Checkout

Consider a Foreign Key as a book checkout record. When a book is checked out, the checkout record includes the library card number (Foreign Key) of the patron who checked out the book. This links the book to the correct patron, ensuring that the library knows who has borrowed each book.

Composite Key: Seat Reservation

Imagine a Composite Key as a seat reservation in a theater. The combination of the show date and seat number uniquely identifies each reservation. A single seat can be reserved for multiple shows, and a single show can have multiple seats reserved, but the combination of show date and seat number ensures that each reservation is unique.