How to write a foreign key to create a table in MySQL

In MySQL, you can create a table foreign key through the following steps: Create a parent table and a child table, and ensure that the corresponding columns exist in the parent table. Use FOREIGN KEY constraints to relate columns in the child table to columns in the parent table. Optionally specify a cascading operation that defines the impact on child table records when parent table records are deleted or updated. Run the query to check whether the foreign key constraints have been applied correctly.

How to use MySQL to create a table foreign key

In MySQL, foreign key constraints are used to ensure that in child tables The records correspond to related records in the parent table. It helps maintain data consistency and integrity. The following are the steps to create a foreign key:

1. Create the parent table and child table

First, create the child table that contains the foreign key column. Make sure the corresponding columns exist in the parent table. For example:

<code class="sql">CREATE TABLE parent_table (
  id INT NOT NULL,
  name VARCHAR(255)
);

CREATE TABLE child_table (
  id INT NOT NULL,
  parent_id INT,
  name VARCHAR(255)
);</code>

2. Create a foreign key constraint

Use the FOREIGN KEY constraint in the child table to parent_id in the child table The column is associated with the id column in the parent table. For example:

<code class="sql">ALTER TABLE child_table
ADD FOREIGN KEY (parent_id) REFERENCES parent_table (id);</code>

3. Specify cascade operation (optional)

Cascade operation defines foreign key constraints when records in the parent table are deleted or updated How to affect related records in the child table. You can specify these operations using the ON DELETE and ON UPDATE clauses. For example:

<code class="sql">ALTER TABLE child_table
ADD FOREIGN KEY (parent_id) REFERENCES parent_table (id)
ON DELETE CASCADE
ON UPDATE RESTRICT;</code>

This example specifies:

• When the parent record referenced in the parent table is deleted, the related child records will be cascaded deleted from the child table.
• Updates will be blocked when the referenced parent record in the parent table is updated.

4. Check constraints

After creating the foreign key constraint, run the following query to check whether the constraint has been applied:

<code class="sql">SELECT * FROM child_table
WHERE parent_id NOT IN (SELECT id FROM parent_table);</code>

If the query returns The result indicates that there is a record in the child table that does not correspond to the parent record, and the foreign key constraints are not correctly applied.

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