Foreign Key in SQL – The Complete Guide

A foreign key in SQL is used to establish a relationship between two tables. It is used to establish and enforce relationships between tables, such as one-to-one, one-to-many or many-to-many relationships. 

A foreign key creates data integrity since it prevents records from being deleted if they are still related to other records. It also makes data retrieval easier by allowing the user to join two related tables together. Let’s look at the several functions we can do using the foreign key like referencing two tables, inserting new records, and adding multiple foreign keys to a table.

SQL Foreign Key Constraint

A foreign key in SQL is used to establish a relation between two tables. It is defined using the FOREIGN KEY and REFERENCES keywords.

Let’s have a look at an example.

-- Create a table for students

CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    first_name VARCHAR(55),
    last_name VARCHAR(55),
    grade_level INT,
    class_id INT,
    CONSTRAINT fk_class
        FOREIGN KEY (class_id)
        REFERENCES Classes(class_id)
);

-- Create a table for classes
CREATE TABLE Classes (
    class_id INT PRIMARY KEY,
    class_name VARCHAR(55),
    teacher_id INT,
    CONSTRAINT fk_teacher
        FOREIGN KEY (teacher_id)
        REFERENCES Teachers(teacher_id)
);

-- Create a table for teachers
CREATE TABLE Teachers (
    teacher_id INT PRIMARY KEY,
    first_name VARCHAR(55),
    last_name VARCHAR(55)
);

Syntax of Foreign Key

The syntax for SQL foreign key is:

CREATE TABLE tables_name (

    columnA data_type,

    columnB data_type,

    …,

    FOREIGN KEY (column_name)

    REFERENCES referenced_table_name (referenced_column_name)

);

In the above syntax-

• table_name- It is the table where the foreign key is to be defined.
• Column_name – It is the column where the foreign key is to be defined.
• referenced_table_name and referenced_column_name- They are the table and column that the foreign key references.

Learn SQL to get a better understanding of the foreign key.

Referencing Columns in Two Tables with Foreign Key

Let’s see how a foreign key links two different tables. Let’s consider two tables, Employees and Departments, where each employee is associated with a specific department using a foreign key.

-- Create the Departments table
CREATE TABLE Departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(55)
);

-- Create the Employees table with a foreign key
CREATE TABLE Employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(55),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES Departments(department_id)
);

In this example, we have two tables, Employees and Departments. The Departments table has two columns:

• department_id- It is the primary key for identifying each department.
• department_name – It is the name of the department.

The Employees table has three columns:

• employee_id – It is the primary key for identifying each employee.
• employee_name – It mentions the name of the employee.
• department_id – It is the foreign key that references the department_id column in the Departments table.

The foreign key can be referenced in any column. The foreign key is typically used to refer to the parent table’s primary key.

How to Create a Foreign Key

Let’s have a look at how we can create a foreign key.

-- Create the Authors table
CREATE TABLE Authors (
    author_id INT PRIMARY KEY,
    author_name VARCHAR(55)
);

-- Create the Books table with a foreign key
CREATE TABLE Books (
    book_id INT PRIMARY KEY,
    book_title VARCHAR(100),
    author_id INT,
    FOREIGN KEY (author_id) REFERENCES Authors(author_id)
);

Inserting New Records With Foreign Key

We can also insert new records in a table. Let’s have a look at how this can be done.

-- Insert a new department record
INSERT INTO Departments (department_id, department_name)
VALUES (1, 'Sales');

-- Insert a new employee record associated with department_id = 1
INSERT INTO Employees (employee_id, employee_name, department_id)
VALUES (1, 'John Doe', 1);

First, we insert a new department record into the “Departments” table. The “department_id” is set to 1, and the “department_name” is ‘Sales’.

Next, we insert a new employee record into the “Employees” table. The “employee_id” is set to 1, the “employee_name” is ‘John Doe’, and the “department_id” is set to 1. This associates the employee with the department whose “department_id” is 1 in the “Departments” table.

Foreign Key with Alter Table

The ALTER TABLE command can be used to add the FOREIGN KEY constraint to an existing table. Let’s have a look:

-- Create the Students table
CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(55),
    class_id INT
);

-- Create the Classes table
CREATE TABLE Classes (
    class_id INT PRIMARY KEY,
    class_name VARCHAR(55)
);

-- Add a foreign key constraint to the Students table
ALTER TABLE Students
ADD CONSTRAINT fk_class
FOREIGN KEY (class_id) REFERENCES Classes(class_id);

Adding Multiple Foreign Keys to a Table

There can be multiple foreign keys in a database. Let’s have a look at how this can be done.

-- Create the Employees table
CREATE TABLE Employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(55),
    department_id INT,
    manager_id INT
);

-- Add foreign key constraints to the Employees table
ALTER TABLE Employees
ADD CONSTRAINT fk_department
FOREIGN KEY (department_id) REFERENCES Departments(department_id),
ADD CONSTRAINT fk_manager
FOREIGN KEY (manager_id) REFERENCES Managers(manager_id);

Conclusion

Foreign keys in SQL can be a powerful tool in helping to ensure data integrity within your database structure. When used correctly they can prevent invalid references from being stored and help maintain the accuracy of related records between multiple tables. This can be especially useful when dealing with large datasets where performance is critical.