How to create foreign key constraints in mysql

Creating foreign key constraints in MySQL can force sub-table records to be associated with main table records to ensure data integrity. Specific steps include: 1. Specify the foreign key column of the subtable; 2. Reference the unique index column of the main table; 3. Set up cascade delete or update operations (optional).

Creating a foreign key constraint in MySQL

A foreign key constraint is a database constraint used to ensure the integrity of data in a table. It forces each record in the child table to reference an existing record in the main table.

How to create a foreign key constraint

In MySQL, you can create a foreign key constraint by specifying the foreign key constraint when creating the child table. The syntax is as follows:

<code class="sql">CREATE TABLE child_table (
  child_column INT NOT NULL,
  PRIMARY KEY (child_column),
  FOREIGN KEY (child_column) REFERENCES parent_table (parent_column)
);</code>

where:

• child_table is the name of the child table.
• parent_table is the name of the main table.
• child_column is a foreign key column in the child table.
• parent_column is the reference column in the main table.

The meaning of foreign key constraints

After a foreign key constraint is created, the following rules will be enforced:

• Every record in the child table must be referenced An existing record in the main table.
• If the referenced record in the main table is deleted, the referenced record in the child table will also be deleted (cascade deletion).
• If the referenced record in the main table is updated, the referenced record in the child table will also be updated (cascade update).

Benefits of using foreign key constraints

There are many benefits of using foreign key constraints:

• Ensure data integrity:Foreign Key constraints prevent the insertion of foreign key values ​​in child tables that point to non-existent records.
• Forced data association: Foreign key constraints force records in the child table to be associated with records in the main table.
• Simplified data maintenance: Foreign key constraints simplify data maintenance because when a record in the main table is deleted or updated, the record in the child table will be automatically updated or deleted.

Note

• The column in the main table must have a unique index: The foreign key column must refer to the column in the main table that has a unique index.
• The foreign key column must exist in the child table: The foreign key column must be an existing column in the child table.
• Cascading delete and update operations are optional: Cascading can be enabled by specifying the ON DELETE CASCADE and ON UPDATE CASCADE options Delete and update operations.

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