MySQL – Foreign Key
A foreign key is a logical association between rows in two tables, in the form of a parent-child relationship. It is possible for one or more rows in a “parent” table to reference a row in a “child” table. The foreign key typically indicates how to JOIN tables in most queries. As a special case, a table can also refer to itself.
The foreign key field of one table corresponds to the primary key field of another table. It means that a foreign key field in one table refers to a primary key field in another table. Each row in another table is uniquely identified, which maintains MySQL’s referential integrity.
[CONSTRAINT constraint_name] FOREIGN KEY [foreign_key_name] (col_name, ...) REFERENCES parent_tbl_name (col_name,...) ON DELETE referenceOption ON UPDATE referenceOption
Constraint_name – This field specifies the name of the foreign key constraint. It is automatically generated by MySQL if we do not provide a name for the constraint.
Col_name – This is the name of the column for which foreign keys will be created.
Parent_tbl_name – In this case, the foreign key columns are referenced by the name of the parent table followed by the name of the columns referencing the foreign key.
Refrence_option – In order to maintain referential integrity between parent and child tables, this clause is used to ensure that foreign keys remain valid when ON DELETE and ON UPDATE clauses are used.
.The following example illustrates how to create a table with a foreign key:
CREATE TABLE orders ( order_id INT PRIMARY KEY, customer_id INT, order_date DATE, FOREIGN KEY (customer_id) REFERENCES customers(customer_id) );