6.1 Primary and Foreign Keys

In relational databases, Primary Keys and Foreign Keys are fundamental concepts used to define relationships between tables and ensure data integrity. They play a crucial role in structuring the database and enforcing rules for data consistency.

6.1.1 Primary Key

A Primary Key is a field (or a combination of fields) in a table that uniquely identifies each record (or row) in that table. It must contain unique values, and it cannot contain NULL values. Each table in a relational database should ideally have a primary key to ensure that each record can be identified uniquely.

Characteristics of a Primary Key:

• Uniqueness: Each value in the primary key column(s) must be unique. No two rows can have the same primary key value.
• Non-null: A primary key cannot have NULL values. Every record must have a valid primary key.
• Single Primary Key per Table: Each table can have only one primary key, though the primary key can consist of multiple columns (composite key).
• Automatic Indexing: In most DBMS systems, a primary key automatically creates an index on the column(s) to speed up query performance.

Syntax to Define a Primary Key:

CREATE TABLE table_name (
    column1 datatype PRIMARY KEY,
    column2 datatype,
    ...
);

Or if the primary key involves multiple columns:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    PRIMARY KEY (column1, column2)
);

Example:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

In this example, the employee_id is the primary key, which uniquely identifies each employee.

6.1.2 Foreign Key

A Foreign Key is a field (or combination of fields) in one table that links to the primary key in another table. It establishes and enforces a relationship between the data in the two tables. The foreign key ensures referential integrity by ensuring that the value in the foreign key column(s) matches an existing value in the primary key column of the referenced table.

Characteristics of a Foreign Key:

• Relationship: A foreign key establishes a relationship between two tables. Typically, the foreign key in the child table references the primary key in the parent table.
• Referential Integrity: The foreign key ensures that a record in the child table cannot reference a non-existent record in the parent table. It maintains data consistency by preventing orphaned records.
• Nullable: Foreign keys can be NULL, meaning that a row does not necessarily have to reference another row in the parent table.
• Multiple Foreign Keys: A table can have more than one foreign key if it needs to reference multiple tables.

Syntax to Define a Foreign Key:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    FOREIGN KEY (column2) REFERENCES parent_table (primary_key_column)
);

Example:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    employee_id INT,
    FOREIGN KEY (employee_id) REFERENCES employees (employee_id)
);

In this example, the orders table has a foreign key (employee_id) that references the employee_id primary key in the employees table. This ensures that each order is associated with a valid employee.

6.1.3 Primary Key vs. Foreign Key

6.1.4 Benefits of Primary and Foreign Keys

• Data Integrity: Primary and foreign keys ensure that the data in the database remains consistent. The primary key guarantees unique records, while the foreign key ensures that related records exist in the parent table.
• Referential Integrity: Foreign keys enforce the relationships between tables, preventing invalid data and orphaned records.
• Optimized Queries: Primary keys automatically create indexes, improving query performance when filtering or searching for records based on the primary key.
• Structured Data Relationships: These keys allow you to create a normalized database schema, minimizing data redundancy and making the database more maintainable.

6.1.5 Cascade Actions for Foreign Keys

Foreign keys can have cascade actions that define what happens to the related rows in the child table when a record in the parent table is updated or deleted. Common actions include:

1. ON DELETE CASCADE

When a record in the parent table is deleted, all related records in the child table are also deleted.

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    employee_id INT,
    FOREIGN KEY (employee_id) REFERENCES employees (employee_id) ON DELETE CASCADE
);

In this case, if an employee is deleted from the employees table, all related orders in the orders table will be deleted.

2. ON UPDATE CASCADE

When the primary key value in the parent table is updated, the corresponding foreign key values in the child table are also updated.

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    employee_id INT,
    FOREIGN KEY (employee_id) REFERENCES employees (employee_id) ON UPDATE CASCADE
);

If an employee_id in the employees table is updated, the corresponding employee_id in the orders table will also be updated.

3. ON DELETE SET NULL

When a record in the parent table is deleted, the foreign key values in the child table are set to NULL.

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    employee_id INT,
    FOREIGN KEY (employee_id) REFERENCES employees (employee_id) ON DELETE SET NULL
);

If an employee is deleted, the employee_id in the orders table will be set to NULL.

4. ON DELETE RESTRICT

Prevents deletion of a record in the parent table if it has related records in the child table.

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    employee_id INT,
    FOREIGN KEY (employee_id) REFERENCES employees (employee_id) ON DELETE RESTRICT
);

If there are any orders associated with an employee, that employee cannot be deleted.

6.1.6 Conclusion

Primary and foreign keys are essential in relational databases for ensuring data consistency and establishing relationships between tables. The primary key uniquely identifies records in a table, while the foreign key links tables together and ensures referential integrity. Proper use of primary and foreign keys helps maintain the database’s structure, reduces redundancy, and improves data accuracy and reliability.