What does FOREIGN KEY mean and how can we use it in MySQL tables?

Actually FOREIGN KEY is a column or a combination of several columns that can be used to set a link between data in two tables. In other words, we can say that the FOREIGN KEY constraint is related to both tables. It is also used to enhance data integrity because the primary keys of a table are linked to foreign keys of other tables.

Syntax

FOREIGN KEY [column_name] REFERENCES [table having Primary Key] ([column_name]);

Here REFERENCES is a keyword; column_name is the list of columns to set FOREIGN KEY; the table with the primary key is the name of the table containing the primary key; column_name is the PRIMARY KEY that has been set list of columns.

Example

Suppose we have two tables "Customer" and "Orders". The relationship between the two tables can be established through a foreign key in the table "orders" specified by the field "Cust_Id". The query to create two tables is as follows -

mysql> Create Table Customer(Cust_ID int Primary Key, First_name Varchar(20), Last_name  Varchar(20), City Varchar(10));
Query OK, 0 rows affected (0.13 sec)

mysql> Create Table Orders(Order_Id Int Primary Key, Product_Name Varchar(25), Orderdate DATE, Cust_ID Int, FOREIGN KEY(Cust_ID) REFERENCES Customer(Cust_id));
Query OK, 0 rows affected (0.13 sec)

mysql> Describe Orders;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| Order_Id     | int(11)     | NO   | PRI | NULL    |       |
| Product_Name | varchar(25) | YES  |     | NULL    |       |
| Orderdate    | date        | YES  |     | NULL    |       |
| Cust_ID      | int(11)     | YES  | MUL | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
4 rows in set (0.04 sec)

Here, the table "Customer" is called the parent table or reference table, and the table "Orders" is called the child table or reference table. Rows in the child table must contain values ​​that exist in the parent table. For example, each row in the table "orders" must have a "Cust_Id" that exists in the "customer" table.

The above is the detailed content of What does FOREIGN KEY mean and how can we use it in MySQL tables?. For more information, please follow other related articles on the PHP Chinese website!