Understanding Table Relationships: Primary Keys and Foreign Keys

In relational databases, table relationships are established using primary keys and foreign keys. These concepts help maintain data integrity and establish connections between tables and allows you to combine data from multiple tables.

Table of Contents

Primary Key

A primary key is a column or a combination of columns that uniquely identifies each row in a table. It provides a way to ensure that each record in a table is unique and can be used to reference specific rows from other tables.

Key characteristics of a primary key include:

Uniqueness: Each value in the primary key column(s) must be unique.
Non-nullability: The primary key column(s) cannot contain null values.
Stability: The values in the primary key column(s) should be static and rarely change.

Consider a table named “employees” with a column “employee_id” as the primary key. Each employee in the table has a unique employee ID, and this column serves as a unique identifier for each record.

Foreign Key

A foreign key is a column (or a set of columns) in one table that refers to the primary key in another table. It establishes a relationship between the two tables based on the values stored in the foreign key column(s).

Key characteristics of a foreign key include:

Referential Integrity: A foreign key enforces referential integrity, ensuring that values in the foreign key column(s) match values in the primary key column(s) of the referenced table.
Relationship: A foreign key represents a relationship between two tables, typically indicating that the values in the foreign key column(s) relate to the primary key values in another table.

Assume we have another table named “departments” with a primary key column “department_id.” In the “employees” table, we can have a foreign key column “department_id” that references the “department_id” column in the “departments” table. This establishes a relationship between the “employees” and “departments” tables based on the department ID.

Establishing Relationships

Ok, and how to establish a relationship between two tables?

To establish a relationship between tables, the foreign key column(s) in one table should refer to the primary key column(s) in another table. This relationship can be defined during table creation or altered later using SQL statements.

To create a foreign key relationship during creation of table mention in the example above, you can use the following syntax:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    department_id INT,
    -- Other columns
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

This creates the “employees” table with a foreign key “department_id” that references the “department_id” column in the “departments” table.

Table relationships, established using primary keys and foreign keys, allow you to connect data across tables, maintain data integrity, and perform meaningful queries that involve multiple tables. Understanding these concepts is essential for designing well-structured databases and retrieving related data effectively.