Understanding Primary Keys and Foreign Keys in Databases

What is a Primary Key?

A Primary Key is a crucial concept in relational databases. It is a column or a combination of columns in a table that uniquely identifies each row in that table. The primary key ensures data integrity by guaranteeing that no two rows have the same key value.

Key Characteristics of a Primary Key:

• Uniqueness: Each value in the primary key column(s) must be unique across the entire table. This ensures that each record can be individually identified.
• Not Null: Primary key columns cannot contain NULL values. Every row must have a valid key.
• Single Primary Key per Table: A table can have only one primary key, which can be composed of one or multiple columns (called a composite primary key).
• Automatic Index: When you define a primary key on a table, the database automatically creates an index to optimize query performance on that key.

Example:

In an Employees table, the column DepNo can serve as the primary key because:

• No two employees will share the same DepNo
• It uniquely identifies each employee record.
• Even if employees share the same name or other attributes, they are still uniquely distinguishable by DepNo.

What is a Foreign Key?

A Foreign Key is a column or set of columns in one table that references the Primary Key column(s) in another table. This creates a relationship between the two tables, enforcing referential integrity.

Key Characteristics of a Foreign Key:

• References Primary Key: It links to a primary key in another table.
• Allows Nulls: Foreign key columns can contain NULL values, which means the relationship is optional.
• Duplicates Allowed: Multiple rows in the referencing table can have the same foreign key value, representing multiple entities related to the same referenced row.
• Multiple Foreign Keys Allowed: A table can have multiple foreign keys, referencing different tables.

Example:

In an Employee table, the column DepNo can be a foreign key referencing the DepNo in the Department table. This relationship means each employee is assigned to a department. However, the DepNo in the employee table can be NULL if an employee isn't assigned to any department yet.

How Do Primary Keys and Foreign Keys Relate Tables?

The combination of primary and foreign keys allows databases to enforce relationships between tables. This enables operations such as joining tables to retrieve related data.

For example:

To find the department name for a particular employee, you join the Employee table with the Department table on the DepNo column.

Introduction to the MySQL primary key

In MySQL, a primary key is a column or a set of columns that uniquely identifies each row in the table. A primary key column must contain unique values.

If the primary key consists of multiple columns, the combination of values in these columns must be unique. Additionally, a primary key column cannot contain NULL.

A table can have either zero or one primary key, but not more than one.

MySQL PRIMARY KEY examples