SQL Foreign Key

Introduction

In this chapter, we will learn about the FOREIGN KEY constraint in SQL. The FOREIGN KEY constraint is used to link two tables together. This chapter will cover the syntax, usage, and provide examples to help you understand how to use the FOREIGN KEY constraint effectively.

What is a Foreign Key?

A foreign key is a column or a set of columns in one table that refers to the primary key columns in another table. The table containing the foreign key is called the child table, and the table containing the primary key is called the parent table. The foreign key constraint ensures referential integrity of the data between the child and parent tables.

Syntax for Foreign Key

Basic Syntax

When creating a table, you can define a foreign key using the following syntax:

CREATE TABLE child_table (
    column1 datatype,
    column2 datatype,
    ...
    FOREIGN KEY (column_name) REFERENCES parent_table (primary_key_column)
);

Example

Assume we have two tables named employees and departments:

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    department_id INT,
    salary DECIMAL(10, 2),
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

In this example, the department_id column in the employees table is a foreign key that references the department_id column in the departments table.

Step-by-Step Example

1. Create Sample Tables

First, we will create the departments and employees tables with the foreign key relationship.

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    department_id INT,
    salary DECIMAL(10, 2),
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

2. Insert Sample Data into departments Table

INSERT INTO departments (department_id, department_name)
VALUES
(1, 'HR'),
(2, 'Finance'),
(3, 'IT');

3. Insert Sample Data into employees Table

INSERT INTO employees (first_name, last_name, email, department_id, salary)
VALUES
('Ramesh', 'Kumar', 'ramesh.kumar@example.com', 1, 50000),
('Sita', 'Patel', 'sita.patel@example.com', 2, 60000),
('Arjun', 'Singh', 'arjun.singh@example.com', 1, 55000),
('Priya', 'Sharma', 'priya.sharma@example.com', 2, 62000),
('Ramesh', 'Kumar', 'ramesh.kumar2@example.com', 3, 50000);

4. Verify the Foreign Key Constraint

To verify the foreign key constraint, try to insert a record into the employees table with a department_id that does not exist in the departments table:

INSERT INTO employees (first_name, last_name, email, department_id, salary)
VALUES ('Ravi', 'Verma', 'ravi.verma@example.com', 4, 70000);

This command will result in an error because the department_id 4 does not exist in the departments table, demonstrating that the foreign key constraint enforces referential integrity.

5. Cascading Actions with Foreign Key

You can define cascading actions for foreign keys to specify what happens when a referenced row is updated or deleted.

Example: ON DELETE CASCADE

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    department_id INT,
    salary DECIMAL(10, 2),
    FOREIGN KEY (department_id) REFERENCES departments(department_id) ON DELETE CASCADE
);

With the ON DELETE CASCADE option, deleting a row in the departments table will automatically delete all related rows in the employees table.

Example: ON UPDATE CASCADE

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    department_id INT,
    salary DECIMAL(10, 2),
    FOREIGN KEY (department_id) REFERENCES departments(department_id) ON UPDATE CASCADE
);

With the ON UPDATE CASCADE option, updating a department_id in the departments table will automatically update the corresponding department_id in the employees table.

Conclusion

The FOREIGN KEY constraint is essential for maintaining referential integrity between tables in a relational database. This chapter covered the basic syntax for defining foreign keys, inserting data while maintaining referential integrity, and using cascading actions. Understanding how to define and use foreign keys effectively will enhance your ability to design robust and reliable database schemas.