FOREIGN KEY constraints and modeling

Statement: The material used in this column is written by the VIP students of the Kay Academy, who has the right to be anonymous and has the final right to interpret the article; The Edith Academy is designed to promote students to learn from each other on the basis of public notes.

FOREIGN KEY constraints and modeling

For the MySQL relationship, we can use the modeling software to make a detailed description of the database tables, both PowerDesigner and SQLyog can be used to build the relational model.

PowerDesigner advantage is that the table can be in Chinese notes, SQLyog cannot have Chinese comments.

PowerDesigner is modeled as follows:

SQLyog modeling needs to click on the new schema designer:

The modeling is as follows:

FOREIGN KEY constraints you can double-click a relationship line to add a foreign key constraint with four different options for foreign KEY constraints: CASCADE, SET NULL, NO ACTION, RESTRICT.

1.CASCADE: Deletes or updates the corresponding row from the parent table, and automatically deletes or updates the matching rows from the table. [on DELETE Canscade and on UPDATE Canscade are supported by InnoDB. ]

2.SET NULL: Deletes or updates the corresponding row from the parent table, and sets the foreign key column in the child table to be empty. [Note that these foreign key columns are not set to NOT NULL if they are not valid.] On DELETE set null and on UPDATE set set NULL are supported by INNODB. ]

3.NO Action:innodb refuses to delete or update the parent table.

4.RESTRICT: Refuses to delete or update the parent table. [Specifying Restrict (or no ACTION) is the same as ignoring the on delete or on Update option. ]

The operation diagram is as follows:

Four options can be set in the drop-down list.

FOREIGN KEY constraints and modeling