Adding and canceling FOREIGN KEY constraint statements in MySQL

Grammar

[CONSTRAINT [Symbol]] FOREIGN KEY
[Index_name] (Index_col_name, ...)
REFERENCES tbl_name (Index_col_name,...)
[on DELETE Reference_option]
[on UPDATE Reference_option]

Reference_option:
RESTRICT | CASCADE | SET NULL | The use of the NO ACTION foreign key requires that the following conditions be met:

1. Two tables must all be innodb tables, and they do not have temporary tables.

2. The corresponding column that establishes the foreign key relationship must have a similar INNODB internal data type.

3. The corresponding column establishing a foreign key relationship must have an index established.

4. If the constraint symbol is explicitly given, then symbol must be unique in the database. If not explicitly given, InnoDB will be created automatically.

If the child table attempts to create a foreign key value that does not exist in the parent table, InnoDB rejects any insert or update operation. The final action depends on the on update and on delete options in the FOREIGN KEY constraint definition if the parent table attempts to update or delete a foreign key value that exists or matches in any of the child tables. InnoDB supports 5 different actions, and if no on delete or on UPDATE is specified, the default action is restrict:

1. CASCADE: Deletes or updates the corresponding rows from the parent table, and automatically deletes or updates the rows that are matched from the table. The 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 null. Note that these foreign key columns are not valid until they are set to NOT NULL. 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. The effect of specifying restrict (or no ACTION) and ignoring on delete or on update options is the same.

5. SET Default:innodb is not currently supported.

There are no exceptions to the two situations where foreign key constraints are used most:

1 The child table is also updated when the parent table is updated, and deletion fails if the child table has a matching entry when the parent table is deleted;

2 The child table is also updated when the parent table is updated, and the child table matching items are deleted when the parent table is deleted.

In the previous case, in the foreign key definition, we used the on Update CASCADE on delete RESTRICT, and in the latter case, the on update CASCADE to delete CASCADE.

InnoDB allows you to use ALTER TABLE to add a new foreign key to an already existing table:

ALTER TABLE Tbl_name
ADD [CONSTRAINT [symbol]] FOREIGN KEY
[Index_name] (Index_col_name, ...)
REFERENCES tbl_name (Index_col_name,...)
[on DELETE Reference_option]
[on UPDATE Reference_option]

InnoDB also supports the use of ALTER TABLE to remove foreign keys:
ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;

Set Index

To set a foreign key, the corresponding two fields must be indexed (index) in the reference table (referencing table, PC tables) and referenced tables (referenced table, parts tables).

For parts table:

ALTER TABLE Parts ADD INDEX Idx_model (model);

To add an index to the Parts table, the index is built on the model field, giving the cable a name called Idx_model.

Also similar to PC tables:

ALTER TABLE pc ADD INDEX Idx_cpumodel (Cpumodel);

In fact, these two indexes can be set when the table is created. Here just to highlight its necessity.

Defining foreign keys

The following is a set of "constraints" that are described above between two tables. Since the PC's CPU model must refer to the corresponding model in the Parts table, we set the Cpumodel field of the PC table to "foreign key" (FOREIGN key), that is, the reference value of the key is from another table.

ALTER TABLE pc ADD CONSTRAINT Fk_cpu_model
FOREIGN KEY (Cpumodel)
REFERENCES parts (model);

The first line is to set the foreign key for the PC table, give the foreign key a name called Fk_cpu_model; the second line is to set the Cpumodel field in this table to a foreign key; the third line is to say that the foreign key is constrained by the model field of the Parts table.

In this way, our foreign key is OK. If we try to create a PC, it uses a CPU model that does not exist in the parts table, then MySQL will prohibit the PC from being created.

Cascade Operations

Consider the following:

Technicians found that the models of a series of CPUs that were entered into the parts table one months ago had lost one letter, and now need to be corrected. We want the referencing column in the corresponding table to be corrected automatically when the referenced column in the Parts table changes.

When you define a foreign key, you add the keyword at the end:

On UPDATE CASCADE; That is, when the primary table is updated, the child tables (they) produce a chain update action, it seems that some people like to call this "cascade" operation. :)

If the sentence is written out in full, it is:

ALTER TABLE pc ADD CONSTRAINT Fk_cpu_model
FOREIGN KEY (Cpumodel)
REFERENCES Parts (model)
On UPDATE CASCADE;

In addition to CASCADE, there are operations such as RESTRICT (prevent master table changes), set null (the corresponding field of the child table is set to null), and so on.

Cancel foreign key constraint in MySQL ...

SET foreign_key_checks=0, cancel foreign key constraint in MySQL.