Foreign Key In MySQL

In MySQL, a foreign key is a field or a set of fields in a table that refers to the primary key or a unique key in another table.

It establishes a link or relationship between the data in two tables, enforcing referential integrity.

The use of foreign keys helps maintain consistency in a relational database by ensuring that values in one table correspond to valid values in another table.

Create Foreign Key Syntax In MySQL:

Syntax: In Child Table

CREATE TABLE childTable (
    column1 data_type,
    column2 data_type,
    FOREIGN KEY (column_key_column1) REFERENCES child_table(column_key_column1)
);

Syntax: In Parent Table

CREATE TABLE parentTable (
    column_key_column1 data_type, -- Foreign Key is another table
    column_key_column2 data_type,
);

Foreign Key Example In MySQL:

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(50),
    product_description VARCHAR(200),
    product_price INT,
    FOREIGN KEY (catalog_id) REFERENCES catalog(catalog_id)
);

CREATE TABLE catalog (
    catalog_id INT PRIMARY KEY,
    catalog_name VARCHAR(50),
    catalog_description VARCHAR(200)
);

The `products` table has a column named `product_id` that is intended to store the products to which each product belongs.
The `catalog` table has a primary key column named `catalog_id`.

Now we can establish a foreign key relationship between the products and catalog tables.

Add Foreign Key Constraint In MySQL Table:

Example:

ALTER TABLE products
ADD CONSTRAINT fk_catalog
FOREIGN KEY (catalog_id)
REFERENCES catalog(catalog_id);

`fk_catalog` is the name of the foreign key constraint. We can choose any meaningful name for our foreign key constraint.
FOREIGN KEY (catalog_id) specifies the column in the `products` table that will act as the foreign key, referencing the `catalog_id` column in the `catalog` table.
REFERENCES catalog(catalog_id) indicates the target table (catalog) and the column in that table that is being referenced (catalog_id).

Note:

The FOREIGN KEY constraint in MySQL is essential for maintaining referential integrity between tables in the Database.

FOREIGN KEY Constraints can be applied during table creation or added to existing tables.

The table contain the foreign key is called the child table, and the table contain the primary key is called the parent table. The foreign key in the child table references the primary key in the parent table.

<-- Primary Key

Unique Key -->