• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL Foreign Key

← Previous Next →

A MySQL Foreign Key is a constraint that enforces a link between two tables. It ensures that the value in one table corresponds to a valid value in another table, maintaining referential integrity.

Foreign keys are crucial for relational databases, as they prevent orphaned records and help enforce relationships between parent and child tables.

What Is a Foreign Key?

A foreign key is a column (or combination of columns) in a child table that references the primary key or unique key of a parent table.

Key Points:

  • Maintains referential integrity between tables.

  • Prevents invalid data in child tables.

  • Can be applied at table creation or later using ALTER TABLE.

  • Can be used with ON DELETE and ON UPDATE actions.

Syntax for Foreign Key

Column-Level (inline definition):

CREATE TABLE child_table (
    column_name datatype,
    FOREIGN KEY (column_name) REFERENCES parent_table(parent_column)
);

Table-Level:

CREATE TABLE child_table (
    column1 datatype,
    column2 datatype,
    CONSTRAINT fk_name FOREIGN KEY (column1)
    REFERENCES parent_table(parent_column)
);

Explanation:

  • column_name: Column in the child table referencing the parent table.

  • parent_table(parent_column): Column in the parent table being referenced.

  • fk_name: Optional name for the foreign key constraint.

Creating a Foreign Key Relationship

Example: students table (parent) and enrollments table (child):

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(50) NOT NULL
);

CREATE TABLE enrollments (
    enrollment_id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT,
    course_id INT,
    FOREIGN KEY (student_id) REFERENCES students(student_id)
);

Explanation:

  • student_id in enrollments must exist in students.

  • Prevents enrolling a student who doesn’t exist.

ON DELETE and ON UPDATE Options

You can control what happens to child records when the parent record is deleted or updated:

CREATE TABLE enrollments (
    enrollment_id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT,
    course_id INT,
    FOREIGN KEY (student_id) REFERENCES students(student_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

Explanation:

  • ON DELETE CASCADE: Deletes child rows automatically if the parent is deleted.

  • ON UPDATE CASCADE: Updates child rows automatically if the parent key changes.

  • Other options: SET NULL, RESTRICT, and NO ACTION.

Adding a Foreign Key to an Existing Table

You can add a foreign key using ALTER TABLE:

ALTER TABLE enrollments
ADD CONSTRAINT fk_student
FOREIGN KEY (student_id) REFERENCES students(student_id)
ON DELETE CASCADE
ON UPDATE CASCADE;

Explanation:

  • Ensures future inserts and updates respect referential integrity.

  • Existing rows must have valid references or the command will fail.

Deleting and Updating Parent Records

Example scenario:

  • Parent table students has student_id = 1.

  • Child table enrollments has student_id = 1.

ON DELETE CASCADE: Deleting student_id = 1 from students will automatically delete corresponding rows in enrollments.

ON UPDATE CASCADE: Updating student_id = 1 to 10 in students will automatically update enrollments.student_id to 10.

This ensures data consistency between tables.

Practical Use Cases

  1. Student Management: Enforce relationships between students and enrollments.

  2. Orders and Customers: Ensure orders reference valid customers.

  3. Inventory: Products in orders must reference existing products.

  4. Employee Management: Department IDs in employees table must exist in departments table.

  5. Blog Application: Comments must reference valid posts.

Best Practices

  1. Always Define Foreign Keys: Maintain referential integrity.

  2. Use CASCADE Carefully: Only if you want automatic deletion or updates.

  3. Index Foreign Key Columns: Improves query performance.

  4. Align Data Types: Parent and child columns must have compatible data types.

  5. Name Constraints Clearly: Makes future maintenance easier.

Common Mistakes to Avoid

  1. Mismatched Data Types: Parent and child columns must match.

  2. Inserting Invalid References: Child rows must reference existing parent rows.

  3. Dropping Parent Records Without Cascade: May lead to errors or orphaned records.

  4. Using Foreign Keys on Large Tables Without Indexing: Can slow performance.

  5. Ignoring Existing Data: Adding a foreign key to a table with invalid references will fail.

Summary of the Tutorial

The MySQL Foreign Key is a vital tool for maintaining relationships and data integrity between tables:

  • Links child tables to parent tables.

  • Supports ON DELETE and ON UPDATE actions.

  • Ensures consistent and valid data in relational databases.

  • Helps prevent orphaned records and maintain structured relationships.

Proper use of foreign keys creates reliable, consistent, and well-organized databases that are easier to maintain and scale.

Practice Questions

  1. Create a students table with student_id as PRIMARY KEY and student_name as NOT NULL.

  2. Create an enrollments table with enrollment_id as PRIMARY KEY and student_id as a FOREIGN KEY referencing students(student_id).

  3. Add ON DELETE CASCADE to the foreign key in the enrollments table.

  4. Add ON UPDATE CASCADE to the foreign key in the enrollments table.

  5. Modify an existing table orders to add a foreign key customer_id referencing customers(customer_id).

  6. Create a comments table with comment_id as PRIMARY KEY and post_id as a foreign key referencing posts(post_id).

  7. Try inserting a record in the child table with a non-existing parent ID and observe the error.

  8. Create a products table and an order_items table, linking product_id as a foreign key in order_items.

  9. Drop a parent record in a table with ON DELETE CASCADE and verify the child rows are deleted automatically.

  10. Create a composite foreign key in an enrollments table referencing a composite primary key in a courses table.

← Previous Next →

Try a Short Quiz.

coding learning websites codepractice

No quizzes available.

MySQL

online coding class codepractice

Introduction to MySQL

MySQL SQL Basics

MySQL Functions & Operators

MySQL Joins & Unions

MySQL Grouping & Filtering

Database & Table Management

MySQL Constraints

Date & Views

References

codepractice logo
Login
Go Back Top