Home > Database > navicat > How to add foreign key constraint command in navicat

How to add foreign key constraint command in navicat

下次还敢
Release: 2024-04-24 00:33:20
Original
586 people have browsed it

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

How to add foreign key constraint command in navicat

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:

<code class="sql">ALTER TABLE child_table
ADD FOREIGN KEY (child_column)
REFERENCES parent_table (parent_column)
[ON DELETE action]
[ON UPDATE action];</code>
Copy after login

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":

<code class="sql">ALTER TABLE orders
ADD FOREIGN KEY (customer_id)
REFERENCES customers (customer_id)
ON DELETE CASCADE
ON UPDATE CASCADE;</code>
Copy after login

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!

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template