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

Creating table foreign key constraints in MySQL ensures referential integrity. The steps are as follows: Create two tables and specify primary keys. Add a FOREIGN KEY constraint to the child table, referencing the primary key of the parent table. Optionally specify reference actions (such as cascading deletes or restricted updates).

Create table foreign key constraints in MySQL

When a foreign key constraint is established between two tables, This ensures referential integrity between them. The following steps introduce how to create table foreign key constraints in MySQL:

Step 1: Create two tables

Create two tables in which we want to Establish foreign key constraints:

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

CREATE TABLE child_table (
    id INT NOT NULL,
    parent_id INT NOT NULL,
    value VARCHAR(255) NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (parent_id) REFERENCES parent_table(id)
);</code>

Step 2: Add foreign key constraints in the child table

In child_table, use FOREIGN KEY constraint references the parent_id column to the id column in parent_table:

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

Step 3: Specify Reference operations (optional)

You can use the ON DELETE and ON UPDATE clauses to specify that the child table be deleted or updated when the parent table is deleted or updated. Behavior of affected records in table:

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

In this code:

• ON DELETE CASCADE: When removing from parent_table When a record is deleted, related records are automatically deleted cascaded from child_table.
• ON UPDATE RESTRICT: When a record in parent_table is updated, any updates to the affected record in child_table will be blocked.

Note:

• Ensure that the column data type in the parent table is the same as the foreign key column in the child table.
• If a column in the parent table allows null values, the foreign key column in the child table must also allow null values.
• If you change a column in the parent table after creating a foreign key constraint, you will need to modify the foreign key constraint to match the change.
• Foreign key constraints help ensure data integrity and prevent the insertion and deletion of inconsistent data.

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!