MySQL foreign key: Database novice beginner in MySQL How to define a foreign key

Defining Data Tables

If a computer manufacturer, its database is stored in the machine and accessories product information. The table used to keep the whole product information is called a Pc, and the table used to hold the information of accessory supply is called parts.

There is a field in the PC table that describes the CPU model used by the computer;

There is a field in the Parts table that describes the model of the CPU and we can think of it as a list of all the CPU models.

Obviously, this factory produces the computer, its use CPU must be the supply information table (parts) exists in the model. In this case, there is a constraint relationship (constraint) in two tables--pc the CPU model in the table is constrained by the model in the Parts table.

First we'll create the parts table:

CREATE TABLE parts (
... 字段定义 ...,
model VARCHAR(20) NOT NULL,
... 字段定义 ...
);

Next is the PC table:

CREATE TABLE pc (
... 字段定义 ...,
cpumodel VARCHAR(20) NOT NULL,
... 字段定义 ...
};

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.