MySQL Foreign Key

The MySQL foreign key is a database constraint that establishes a link or relationship between two tables. It enforces referential integrity by ensuring that the values in a column (or a set of columns) of one table match the values in the primary key column(s) of another table. Foreign keys are used to maintain data consistency and create relationships between tables.

Here are the key points about foreign keys in MySQL:

1. Referential Integrity: A foreign key enforces referential integrity by ensuring that the values in the referencing table (the table with the foreign key) correspond to valid values in the referenced table (the table with the primary key).
2. Relationships: Foreign keys are used to establish relationships between tables. The table containing the foreign key is called the “child” table, and the table referenced by the foreign key is called the “parent” table.
3. Foreign Key Column: A foreign key is a column (or a set of columns) in the child table that references the primary key column(s) in the parent table.
4. Creating a Foreign Key: You can create a foreign key when defining a table using the FOREIGN KEY constraint. The syntax is as follows:

   CREATE TABLE child_table (
       child_column datatype,
       FOREIGN KEY (child_column) REFERENCES parent_table(parent_column)
   );

• child_table: The name of the table with the foreign key.
• child_column: The column in the child_table that acts as the foreign key.
• parent_table: The name of the referenced (parent) table.
• parent_column: The primary key column in the referenced table.

1. Referential Actions: You can specify referential actions to control what happens when a foreign key relationship is violated. Common actions include CASCADE, SET NULL, SET DEFAULT, and RESTRICT.

• CASCADE: When a parent record is updated or deleted, the corresponding child records are also updated or deleted.
• SET NULL or SET DEFAULT: The child records are set to NULL or to their default values, respectively, when the parent record is updated or deleted.
• RESTRICT: Prevents any update or delete operation that would violate the foreign key relationship.

1. Checking for Foreign Keys: You can query the INFORMATION_SCHEMA to check for foreign keys in your database.

Foreign keys are crucial for maintaining data integrity and consistency in relational databases. They ensure that relationships between tables are well-defined and that data conforms to those relationships. By using foreign keys, you can avoid orphaned records and data anomalies.