🔗 The FOREIGN KEY constraint in SQL is used to create and enforce relationships between tables. It ensures that values in one table correspond to valid values in another table, maintaining referential integrity throughout the database.

📖 What is a FOREIGN KEY?

A FOREIGN KEY is a column (or a group of columns) in one table that references the PRIMARY KEY or a UNIQUE column in another table. This relationship prevents invalid references and ensures that related data remains consistent.

🎯 Why Use FOREIGN KEY?

Foreign keys are essential for maintaining relationships between related tables in relational databases.

📊 Parent and Child Tables

Consider the following two tables.

Parent Table: Students

Child Table: Enrollments

📝 Basic Syntax

CREATE TABLE child_table
(
    column_name data_type,

    FOREIGN KEY (column_name)
    REFERENCES parent_table(parent_column)
);

💡 Create a Table with a FOREIGN KEY

Create an Enrollments table that references the Students table.

CREATE TABLE Enrollments
(
    EnrollmentID INT PRIMARY KEY,
    StudentID INT,
    CourseName VARCHAR(100),

    FOREIGN KEY (StudentID)
    REFERENCES Students(StudentID)
);

Here, every StudentID stored in the Enrollments table must already exist in the Students table.

✅ Valid INSERT

Since student 101 exists in the parent table, the insert succeeds.

INSERT INTO Enrollments
VALUES
(1, 101, 'Database Systems');

❌ Invalid INSERT

The following statement fails because student 999 does not exist.

INSERT INTO Enrollments
VALUES
(2, 999, 'Operating Systems');

➕ Add a FOREIGN KEY to an Existing Table

A foreign key can also be added after the table has been created.

ALTER TABLE Enrollments
ADD CONSTRAINT FK_Enrollments_Students
FOREIGN KEY (StudentID)
REFERENCES Students(StudentID);

➖ Remove a FOREIGN KEY

Foreign key constraints can be removed when they are no longer required.

ALTER TABLE Enrollments
DROP FOREIGN KEY FK_Enrollments_Students;

ALTER TABLE Enrollments
DROP CONSTRAINT FK_Enrollments_Students;

🔄 Referential Actions

Foreign keys can define what happens when rows in the parent table are updated or deleted.

CREATE TABLE Enrollments
(
    EnrollmentID INT PRIMARY KEY,
    StudentID INT,

    FOREIGN KEY (StudentID)
    REFERENCES Students(StudentID)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

⚖️ PRIMARY KEY vs FOREIGN KEY

💼 Real-World Example

In an e-commerce application, every order belongs to a customer. The Orders table stores a CustomerID foreign key that references the Customers table, ensuring orders are always linked to valid customers.

CREATE TABLE Orders
(
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,

    FOREIGN KEY (CustomerID)
    REFERENCES Customers(CustomerID)
);

⚠️ Common Mistakes

⚠️ Best Practices

🚀 Key Points to Remember