Getting started: how to define foreign key _ MySQL in MySQL database

Define a data table if a computer manufacturer stores the product information of the entire machine and accessories in its database. The table used to save the product information of the entire machine is called pc; the table used to save the accessory supply information is called parts. In the pc table, there is a field used to describe the CPU model used by this computer. in the parts table, there is a field describing the CPU model. we can use the Mysql foreign key.

Define Data tables

Assume that a computer manufacturer stores the product information of the whole machine and accessories in its database. The table used to save the product information of the entire machine is called pc; the table used to save the accessory supply information is called parts.

There is a field in the pc table to describe the CPU model used by this computer;

The corresponding field in the parts table describes the CPU model. we can think of it as a list of all CPU models.

Obviously, the CPU used by the computer produced by this manufacturer must be the model existing in the supply Information Table (parts. In this case, two tables have a constraint. the CPU model in the pc table is subject to the model constraints in the parts table.

First, create a parts table:

Create table parts (... field definition..., model VARCHAR (20) not null,... field definition ...);

Next is the PC table:

Create table pc (... field definition..., cpumodel VARCHAR (20) not null,... field definition ...};

Set indexes

To set a foreign key, in the reference table (referencing table, that is, the pc table) and the referenced table (referenced table, that is, the parts table, indexes must be set for both fields ).

For the parts table:

ALTER TABLE parts ADD INDEX idx_model (model);

This statement adds an index to the parts table, which is created on the model Field and named idx_model.

Similar to pc tables:

ALTER TABLE pc ADD INDEX idx_cpumodel (cpumodel);

In fact, these two indexes can be set during table creation. This is just to highlight its necessity.

Define foreign keys

The following describes how to create a constraint between two tables ". Because the CPU model of the pc 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 this key comes from other tables.

ALTER TABLE pc ADD CONSTRAINT fk_cpu_model FOREIGN KEY (cpumodel) REFERENCES parts(model);

The first line is to set a foreign key for the pc table and name it fk_cpu_model. The second line is to set the cpumodel field of the table as a foreign key; the third row indicates that the foreign key is restricted by the model field of the parts table.

In this way, our foreign keys are ready! If we try to CREATE a pc, and the CPU model used does not exist in the parts table, MySQL will disable this PC from being created.

Cascade operations

Consider the following situations:

Technicians found that all the cpu (possibly many) models of a series entered in the parts table a month ago had a wrong letter, and now they need to correct it. We hope that when the Referenced columns in the parts table change, the Referencing Column in the corresponding table can also be automatically corrected.

When defining a foreign key, you can add such a keyword at the end:

On update cascade; that is, when the primary table is updated, sub-tables (who) generate a chain UPDATE action. it seems that some people like to call this a CASCADE operation.

If the statement is completely written, 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 also operations such as RESTRICT (prohibit changes to the master table) and set null (the corresponding fields of the sub-table are SET to NULL.