Foreign Key (MySQL Key)

In MySQL, a foreign key is a column or set of columns in one table that refers to the primary key or a unique key in another table. The purpose of a foreign key is to enforce referential integrity between the two tables.

Syntax

The syntax for creating a foreign key constraint in MySQL is as follows:

ALTER TABLE child_table
ADD CONSTRAINT fk_constraint
FOREIGN KEY (child_column)
REFERENCES parent_table (parent_column);

In the above syntax, child_table and parent_table refer to the child table and parent table, respectively. child_column and parent_column refer to the columns in the child and parent table, respectively, that are being linked. fk_constraint is a name for the foreign key constraint.

Example

Suppose we have two tables: orders and customers. We want to create a foreign key constraint between the customer_id column in the orders table and the id column in the customers table. Here's how we can do it:

ALTER TABLE orders
ADD CONSTRAINT orders_customer_fk
FOREIGN KEY (customer_id)
REFERENCES customers(id);

Now, if a user tries to insert a row into orders with a non-existent customer_id value, MySQL will throw an error.

Explanation

In the example above, we created a foreign key constraint on the orders table that referenced the customers table. Specifically, we linked the customer_id column in the orders table to the id column in the customers table using the REFERENCES keyword.

With this foreign key constraint in place, MySQL will now enforce referential integrity between the two tables. This means that a user cannot insert a row into orders with a customer_id value that does not exist in the customers table.

Use

Foreign key constraints are an important tool for maintaining the integrity and consistency of data in relational databases. They can help prevent errors, inconsistencies, and duplication of data. They are particularly useful when working with complex data models that involve multiple tables with interdependent relationships.

Important Points

• The child and parent columns in a foreign key constraint must be of the same data type.
• The parent column must have a primary key or unique key constraint in order to be referenced by a foreign key constraint.
• MySQL supports the use of CASCADE actions in foreign key constraints, which can automatically delete or update child rows when a parent row is deleted or updated.

Summary

In this tutorial, we discussed how to create a foreign key constraint in MySQL. We covered the syntax, example, explanation, use, and important points of foreign key constraints. With this knowledge, you can now create foreign key constraints in your MySQL database to maintain the integrity and consistency of data.