MySQL FOREIGN KEY Constraint

The FOREIGN KEY constraint establishes a relationship between two tables by linking a column (or group of columns) in the child table to a PRIMARY KEY in the parent table. This ensures referential integrity, preventing invalid data from being inserted into the foreign key column.

Key Points about FOREIGN KEY:

1. Parent Table: Contains the primary key (e.g., Persons table).
2. Child Table: Contains the foreign key referencing the primary key (e.g., Orders table).
3. Referential Integrity: Ensures that the value in the foreign key column must exist in the referenced primary key column.

Creating a FOREIGN KEY Constraint

FOREIGN KEY on CREATE TABLE

You can define a foreign key while creating the table.

Example 1: Basic FOREIGN KEY

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);

Example 2: Naming the FOREIGN KEY Constraint

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)
    REFERENCES Persons(PersonID)
);

FOREIGN KEY on ALTER TABLE

If the table is already created, you can add a foreign key constraint using the ALTER TABLE statement.

Example 1: Basic FOREIGN KEY

ALTER TABLE Orders
ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

Example 2: Naming the FOREIGN KEY Constraint

ALTER TABLE Orders
ADD CONSTRAINT FK_PersonOrder
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

Dropping a FOREIGN KEY Constraint

To remove a foreign key constraint, use the DROP FOREIGN KEY statement. You need to reference the name of the foreign key constraint, not the column.

Syntax:

ALTER TABLE table_name
DROP FOREIGN KEY constraint_name;

Example:

ALTER TABLE Orders
DROP FOREIGN KEY FK_PersonOrder;

Example Use Case

Parent Table: Persons

CREATE TABLE Persons (
    PersonID int NOT NULL,
    LastName varchar(255),
    FirstName varchar(255),
    PRIMARY KEY (PersonID)
);

Child Table: Orders

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);

Why Use FOREIGN KEY Constraints?

1. Data Integrity: Ensures the child table references valid data from the parent table.
2. Cascading Actions:
   • ON DELETE CASCADE: Automatically deletes child rows when the parent row is deleted.
   • ON UPDATE CASCADE: Automatically updates child rows when the parent row is updated.

Example with Cascading:

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);