MySQL : FOREIGN KEY Constraint

In MySQL, the `FOREIGN KEY` constraint is used to enforce referential integrity between two tables. It ensures that the values in a column (or group of columns) in one table match the values in a column (or group of columns) in another table's primary key or unique key column(s).

Here's how you can use the `FOREIGN KEY` constraint while creating a table:

CREATE TABLE child_table (
    ...
    foreign_key_column data_type,
    FOREIGN KEY (foreign_key_column) REFERENCES parent_table(parent_column)
);
 

• child_table: The table in which you want to create the foreign key constraint.
• foreign_key_column: The column in the child table that references the parent table's column.
• parent_table: The name of the parent table.
• parent_column: The column in the parent table that the foreign key column references.

For example, suppose you have two tables named orders and customers. You want to ensure that the customer_id column in the orders table references the customer_id column in the customers table:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
 

In this example, the customer_id column in the orders table is defined as a foreign key, and it references the customer_id column in the customers table. This ensures that every customer_id value in the orders table must exist in the customers table, thus maintaining referential integrity between the two tables.

Using foreign key constraints helps to maintain data consistency and integrity by preventing actions that would invalidate the relationships between related tables. It ensures that only valid data is inserted into the database, and it helps to enforce business rules at the database level.