Foreign keys in MySQL are a way to define a relationship between two tables. This relationship ensures that the data stored in one table is linked to data in another table. In MySQL, a foreign key is a field or combination of fields that references the primary key of another table.

Creating a foreign key relationship requires the use of the

FOREIGN KEY

clause when defining a table. For example:

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

In this example, the

orders

table has a field called

customer_id

that references the

customer_id

field in the

customers

table. This ensures that every

customer_id

value in the

orders

table exists in the

customers

table.

Another important aspect of foreign keys is how they affect data manipulation. For example, when a record with a foreign key is deleted, MySQL can either delete all related records in the child table (

ON DELETE CASCADE

), set the foreign key value to NULL (

ON DELETE SET NULL

), or simply prohibit the deletion of the record (

ON DELETE RESTRICT

). This behavior is controlled by the options specified in the

REFERENCES

clause.

Foreign keys are an important tool for maintaining data consistency and enforcing referential integrity in MySQL. By defining relationships between tables, we can ensure that data is stored in a logical and organized manner.