How to add foreign key constraint command in navicat

Use SQL commands in Navicat to add foreign key constraints to ensure data consistency: Command structure: ALTER TABLE child_table ADD FOREIGN KEY (child_column) REFERENCES parent_table (parent_column) [ON DELETE action] [ON UPDATE action] Parameter explanation: child_table: child table; child_column: child table refers to the column of parent table column; parent_table: parent table; parent_column: parent table is referenced

Add foreign key constraints command in Navicat

Add foreign key constraints in Navicat to ensure data consistency and integrity. The following is how to add foreign key constraints in Navicat using SQL commands:

Command structure:

ALTER TABLE child_table
ADD FOREIGN KEY (child_column)
REFERENCES parent_table (parent_column)
[ON DELETE action]
[ON UPDATE action];

Parameter explanation:

• child_table: The child table to which foreign key constraints need to be added.
• child_column: The column in the child table that refers to the parent table column.
• parent_table: The parent table containing the referenced column.
• parent_column: The referenced column in the parent table.

• ON DELETE action: Optional, specify the operation to be performed on related records in the child table when the records in the parent table are deleted. Possible values ​​include:

  • CASCADE: Automatically delete related records in the child table.
  • SET NULL: Set the foreign key column of the related record in the child table to NULL.
  • RESTRICT: Prevent deletion of records in the parent table if doing so would affect related records in the child table.

• ON UPDATE action: Optional, specify the operation to be performed on related records in the child table when the records in the parent table are updated. Possible values ​​include:

  • CASCADE: Automatically updates foreign key columns for related records in the child table.
  • SET NULL: Set the foreign key column of the related record in the child table to NULL.
  • RESTRICT: Prevents updates to records in the parent table if doing so would affect related records in the child table.

Example:

The following command adds a foreign key constraint in the subtable named "orders" that refers to "customer_id" column in the parent table named "customers":

ALTER TABLE orders
ADD FOREIGN KEY (customer_id)
REFERENCES customers (customer_id)
ON DELETE CASCADE
ON UPDATE CASCADE;

Tips:

• Before executing the command to add a foreign key constraint, make sure Both parent and child tables exist.
• If data already exists in the child table, use the correct ON DELETE and ON UPDATE operations to prevent data loss.
• You can run these commands in Navicat's SQL editor or use the graphical interface by right-clicking the table and selecting "Alter Table" in the menu.

The above is the detailed content of How to add foreign key constraint command in navicat. For more information, please follow other related articles on the PHP Chinese website!