How to write foreign key constraints when creating a table in MySQL

MySQL foreign key constraint syntax is: ALTER TABLE child_table ADD FOREIGN KEY (foreign_key_column) REFERENCES parent_table (primary_key_column), which ensures the integrity of the relationship between the two tables. The functions of foreign key constraints include: 1. Data integrity: the constrained child table reference must exist in the parent table; 2. Cascade update: automatically update all references when the parent table primary key value changes; 3. Cascade delete: the parent table primary key All references are automatically deleted when the value is deleted.

MySQL foreign key constraint syntax

In MySQL, foreign key constraints are used to ensure that between two tables of relationship integrity. The syntax is as follows:

<code>ALTER TABLE child_table
ADD FOREIGN KEY (foreign_key_column)
REFERENCES parent_table (primary_key_column)</code>

Among them:

• child_table: The table to which foreign key constraints need to be added.
• foreign_key_column: The column in child_table that references the primary key of parent_table.
• parent_table: The referenced table whose primary key is used to define constraints.
• primary_key_column: The primary key column referenced in parent_table.

The role of foreign key constraints

Foreign key constraints help ensure the following:

• Data integrity Properties: Prevent references from being inserted into the child table that do not exist in the parent table.
• Cascade update: When the primary key value in the parent table changes, automatically update the values ​​in all referenced child tables.
• Cascade deletion: When the primary key value in the parent table is deleted, all referenced values ​​in the child table are automatically deleted.

Example

Suppose we have two tables: orders and order_items:

<code>CREATE TABLE orders (
  order_id INT NOT NULL AUTO_INCREMENT,
  customer_id INT NOT NULL,
  PRIMARY KEY (order_id)
);

CREATE TABLE order_items (
  item_id INT NOT NULL AUTO_INCREMENT,
  order_id INT NOT NULL,
  product_id INT NOT NULL,
  PRIMARY KEY (item_id),
  FOREIGN KEY (order_id) REFERENCES orders (order_id)
);</code>

In this example, the foreign key constraint on the order_items table ensures that each order_id value exists in the orders table. This means that we cannot insert an order_id value into the order_items table that does not exist in the orders table.

The above is the detailed content of How to write foreign key constraints when creating a table in MySQL. For more information, please follow other related articles on the PHP Chinese website!