MySQL FOREIGN KEY

A FOREIGN KEY is a column or group of columns that refers to a key in another table.

For example, a students table may contain student_id as its PRIMARY KEY, while a fees table can use student_id as a FOREIGN KEY.

students
student_id

fees
student_id

FOREIGN KEY constraints are used to:

• Create relationships between tables
• Maintain referential integrity
• Prevent invalid references
• Connect related records
• Build relational database systems

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE fees (
    fee_id INT PRIMARY KEY,
    student_id INT,
    FOREIGN KEY (student_id)
    REFERENCES students(student_id)
);

Here, fees.student_id refers to students.student_id.

The table containing the referenced key is commonly called the parent table.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100)
);

Here, the students table is the parent table.

The table containing the FOREIGN KEY is commonly called the child table.

CREATE TABLE fees (
    fee_id INT PRIMARY KEY,
    student_id INT,
    FOREIGN KEY (student_id)
    REFERENCES students(student_id)
);

Here, fees is the child table.

The basic syntax is:

FOREIGN KEY (column_name)
REFERENCES parent_table(parent_column)

Example:

FOREIGN KEY (student_id)
REFERENCES students(student_id)

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100)
);

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100),
    course_id INT,
    FOREIGN KEY (course_id)
    REFERENCES courses(course_id)
);

Now students.course_id can reference an existing course.

When using a FOREIGN KEY, the referenced parent record should exist before inserting a child record that refers to it.

INSERT INTO courses
(course_id, course_name)
VALUES
(101, 'Python');

Now a student can reference course_id 101.

INSERT INTO students
(student_id, name, course_id)
VALUES
(1, 'Amit', 101);

This works because course_id 101 exists in the parent table.

Suppose course_id 999 does not exist in the courses table.

INSERT INTO students
(student_id, name, course_id)
VALUES
(2, 'Rahul', 999);

With the FOREIGN KEY constraint enabled, MySQL rejects the insert because the referenced parent record does not exist.

You can give a FOREIGN KEY constraint a custom name.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100),
    course_id INT,

    CONSTRAINT fk_student_course
    FOREIGN KEY (course_id)
    REFERENCES courses(course_id)
);

The name fk_student_course makes the constraint easier to identify.

A table can have multiple FOREIGN KEY constraints.

CREATE TABLE enrollments (
    enrollment_id INT PRIMARY KEY,
    student_id INT,
    course_id INT,

    FOREIGN KEY (student_id)
    REFERENCES students(student_id),

    FOREIGN KEY (course_id)
    REFERENCES courses(course_id)
);

Here, one table is related to both students and courses.

A common relationship looks like this:

students
---------
student_id  ← PRIMARY KEY

fees
---------
student_id  ← FOREIGN KEY

The FOREIGN KEY references the PRIMARY KEY of the parent table.

A FOREIGN KEY can reference a suitable indexed key in the parent table. In modern MySQL, the referenced columns are typically indexed, and a referenced key is commonly a PRIMARY KEY or UNIQUE key.

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_code VARCHAR(10) UNIQUE
);

The exact referenced-key rules depend on the MySQL version and storage engine.

ON DELETE CASCADE automatically deletes related child records when the referenced parent record is deleted.

CREATE TABLE fees (
    fee_id INT PRIMARY KEY,
    student_id INT,

    FOREIGN KEY (student_id)
    REFERENCES students(student_id)
    ON DELETE CASCADE
);

Use this carefully because deleting a parent record can delete related child records automatically.

ON UPDATE CASCADE allows a change to the referenced parent key to propagate to matching child key values.

FOREIGN KEY (course_id)
REFERENCES courses(course_id)
ON UPDATE CASCADE

This is useful when a referenced key value is changed.

ON DELETE SET NULL changes the child FOREIGN KEY value to NULL when the referenced parent record is deleted.

FOREIGN KEY (course_id)
REFERENCES courses(course_id)
ON DELETE SET NULL

The child column must allow NULL for this action to work.

RESTRICT prevents deletion of a parent row when matching child rows exist.

FOREIGN KEY (student_id)
REFERENCES students(student_id)
ON DELETE RESTRICT

This can protect related child data from being deleted accidentally.

NO ACTION means MySQL does not perform an automatic action to remove or change child rows. Referential integrity must still be maintained.

FOREIGN KEY (student_id)
REFERENCES students(student_id)
ON DELETE NO ACTION

For many common MySQL use cases, this behaves similarly to RESTRICT for immediate constraint checking.

A FOREIGN KEY can be added after the table has been created.

ALTER TABLE students
ADD CONSTRAINT fk_course
FOREIGN KEY (course_id)
REFERENCES courses(course_id);

The existing data must satisfy the relationship before the constraint can be added successfully.

You can remove a FOREIGN KEY using its constraint name.

ALTER TABLE students
DROP FOREIGN KEY fk_course;

The constraint name can be found using SHOW CREATE TABLE or information schema metadata.

You can inspect a table definition using:

SHOW CREATE TABLE students;

This displays the FOREIGN KEY definition and its constraint name.

You can also inspect foreign key metadata through MySQL's information_schema tables.

The FOREIGN KEY column and the referenced column should have compatible data types and attributes.

students:
student_id INT PRIMARY KEY

fees:
student_id INT

Using matching definitions helps avoid relationship and constraint problems.

FOREIGN KEY relationships make it easy to retrieve related information using JOIN operations.

SELECT
    students.name,
    courses.course_name
FROM students
INNER JOIN courses
ON students.course_id = courses.course_id;

The FOREIGN KEY is not what performs the JOIN, but it represents the relationship between the tables.

A FOREIGN KEY column can generally contain NULL if the column allows NULL and no NOT NULL constraint is defined.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100),
    course_id INT,
    FOREIGN KEY (course_id)
    REFERENCES courses(course_id)
);

A NULL value means the student currently has no course reference; it is different from referencing a non-existent course ID.

• Referencing a column that does not have a suitable key/index
• Using incompatible column definitions
• Inserting a child value that does not exist in the parent table
• Deleting parent records without considering child records
• Using ON DELETE CASCADE without understanding its effect
• Forgetting the FOREIGN KEY constraint name
• Trying to add a constraint while existing data violates it

A common education-management database can use a relationship between students and fees.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE fees (
    fee_id INT PRIMARY KEY,
    student_id INT,
    amount DECIMAL(10,2),

    FOREIGN KEY (student_id)
    REFERENCES students(student_id)
);

Each fee record can be associated with an existing student.

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100)
);

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE enrollments (
    enrollment_id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT,
    course_id INT,

    FOREIGN KEY (student_id)
    REFERENCES students(student_id),

    FOREIGN KEY (course_id)
    REFERENCES courses(course_id)
);

The enrollments table connects students with courses.

CREATE TABLE courses (
    course_id INT PRIMARY KEY AUTO_INCREMENT,
    course_name VARCHAR(100) NOT NULL
);

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    course_id INT,

    CONSTRAINT fk_student_course
    FOREIGN KEY (course_id)
    REFERENCES courses(course_id)
);

INSERT INTO courses
(course_name)
VALUES
('Python'),
('Java'),
('PHP');

INSERT INTO students
(name, course_id)
VALUES
('Amit', 1),
('Priya', 2),
('Rahul', 3);

SELECT
    students.student_id,
    students.name,
    courses.course_name
FROM students
INNER JOIN courses
ON students.course_id = courses.course_id;

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE fees (
    fee_id INT PRIMARY KEY,
    student_id INT,
    amount DECIMAL(10,2),

    CONSTRAINT fk_fee_student
    FOREIGN KEY (student_id)
    REFERENCES students(student_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

INSERT INTO students
(student_id, name)
VALUES
(1, 'Amit');

INSERT INTO fees
(fee_id, student_id, amount)
VALUES
(101, 1, 5000.00);

DELETE FROM students
WHERE student_id = 1;

Because ON DELETE CASCADE is defined, deleting student 1 also deletes related fee records for that student.