MySQL Learning 10: Update operations under FOREIGN KEY constraints

The previous article simply described the requirements for foreign key constraints, and did not tell you about the referential actions of foreign key constraints. This time I'll take a look at the update operation under the FOREIGN KEY constraint.

referential operation for a foreign KEY constraint

After we have created the foreign KEY constraint, the child table is updated accordingly when the table is updated. This is the best we have to create foreign key constraints

Secretariat There are several types of the following:

1) CASCADE: Deletes or updates from the parent table and automatically deletes or updates the matching rows in the child table.

2) Set NULL: Deletes or updates rows from the parent table, and sets the foreign key column in the child table to null. If you use this option, you must ensure that the child table column does not refer to

Set to not NULL.

3) RESTRICT: rejects the deletion or update of the parent table.

4) NO ACTION: Standard SQL keyword, same as restrict in MySQL.

Two updating field preparation for foreign KEY constraints (1) Create a sub-table users1

CREATE TABLE users1 (

ID SMALLINT UNSIGNED PRIMARY KEY auto_increment,

Username VARCHAR (Ten) is not NULL,

pid SMALLINT UNSIGNED,

FOREIGN KEY (PID) REFERENCES provinces (id) on DELETE CASCADE

);

SHOW CREATE TABLE users1;

(2) Insert a record in the parent table provinces and view the record information for the parent table:

INSERT provinces (pname) VALUES (' A ');

INSERT Provinces (pname) VALUES (' B ');

INSERT provinces (pname) VALUES (' C ');

SELECT * from provinces;

(3) Insert record in child table users1:

INSERT users1 (username,pid) VALUES (' Tom ', 3);

INSERT users1 (username,pid) VALUES (' John ', 3);

INSERT users1 (username,pid) VALUES (' Tom ', 6);

INSERT users1 (username,pid) VALUES (' Rose ', 1);

SELECT * from Users1;

The result is known: if the number of the Foreign key column PID field is greater than 3, the record is not inserted because the Foreign key column PID field refers to the parent table

The Reference Column ID field in provinces, since there are only 3 entries in the ID field in the parent table, the PID field inserted in the users1 of the child table cannot be greater than 3;

The index is present in the record, which is why the above results are displayed and the ID field is 1,2,4.

Delete operation under three foreign KEY constraints

Let's say we delete id=3 records from the provinces data sheet.

DELETE from provinces WHERE id=3;

SELECT * from provinces;

SELECT * from Users1;

Now that the deletion has been done, the updated data will do the same, and no examples will be given here.

Four Update operation reference (1) on delete operation

RESTRICT (Constraint): When the corresponding record is deleted in the parent table (that is, the source table of the foreign key), first check that the record has a corresponding foreign key, if there is no

Allow deletion.

No ACTION: The meaning is the same as restrict, that is, if there is data from, delete master data is not allowed.

CASCADE (cascading): When the corresponding record is deleted in the parent table (that is, the source table of the foreign key), first check that the record has a corresponding foreign key, and if there is one, delete

The exception key is the record in the child table (that is, the table that contains the foreign key).

Set NULL: When the corresponding record is deleted in the parent table (that is, the source table of the foreign key), first check that the record has a corresponding foreign key, and if so, set the child table

This foreign key value is null (this requires the foreign key to allow null).

(2) on Update operation

RESTRICT (Constraint): When the corresponding record is updated in the parent table (that is, the source table of the foreign key), first check that the record has a corresponding foreign key, if any

Updates are not allowed.

NO ACTION: Mean with restrict.

CASCADE (cascading): When the corresponding record is updated in the parent table (that is, the source table of the foreign key), first check that the record has a corresponding foreign key, and if so,

Updates the record in the child table (that is, the table that contains the foreign key) for the foreign key.

Set NULL: When the corresponding record is updated in the parent table (that is, the source table of the foreign key), first check that the record has a corresponding foreign key, and if so, set the child

Table This foreign key value is null (this requires the foreign key to allow null).

Note: The difference between the NO action and the Restrict: only in the case of individual cases will lead to differences, the former is in the other binding action after the

The highest priority execution.

Five table-level constraints and column-level constraints

Divided by the number of references: table-level constraints and column-level constraints.

A constraint established on a data column, called a column-level constraint. Column-level constraints can be declared either when a column is defined, or after a column definition.

Constraints that are established on multiple data columns are called table-level constraints. Table-level constraints can only be declared after a column definition.

The use of table-level constraints and column-level constraints requires attention:

A table-level constraint does not exist for a 1NOT null constraint and a DEFAULT constraint. The remaining constraints have column-level constraints and table-level constraints.

2 in the actual development process, column-level constraints are used more, and table-level constraints are seldom used.

MySQL Learning 10: Update operations under FOREIGN KEY constraints