How to define foreign key _ MySQL in MySQL

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. Mysql foreign key

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.

In the pc table, there is a field used to describe the CPU model used by the computer. in the parts table, there is a field describing 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 to say, when the primary table is updated, the sub-table (s) generates a chain update action. it seems that some people like to call this "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;

Besides CASCADE, RESTRICT and set null are also supported.

Add to this article:

If you want to delete a record in the primary table, you cannot delete the record in the subtable. add the ON delete restrict command. The complete case is as follows:

Two tables, country and city, and country_id in the city are foreign keys.

Create table country (

Country_idSmallint unsigned not null auto_increment,

Country varchar (50) not null,

Last_update timestamp not null,

Primary key (country_id)

) Engine = innoDB default charset = utf8;

Create table city (

City_id smallint unsigned not null auto_increment,

City varchar (50) not null,

Country_id smallint unsigned not null,

Last_update timestamp not null default current_timestamp on update curren_timestamp,

Primary key (city_id ),

Key idx_fk_country_id (country_id ),

ConstraintFk_city_countryForeign Key(Country_id)ReferenceSCountry (country_id)On DELETE restrict ON update cascade

) Engine = innoDB default charset = utf8;

Delete foreign key:

Delete foreign key definition
-----
When defining a foreign key, the articles. member_id foreign key has a CONSTRAINT fk_member than the articles. category_id clause?
This fk_member is used to delete the foreign key definition, as shown below:
Mysql>Alter table articles drop foreign key fk_member;
Query OK, 1 row affected (0.25 sec)
Records: 1 Duplicates: 0 Warnings: 0

In this way, the foreign key definition of articles. member_id is deleted, but how can I delete it if the CONSTRAINT fk_symbol (that is, the foreign key symbol) is not specified during the definition? Don't worry. MySQL will create one by itself, but you can run the following command to view it:

Mysql>Show create table articles;
+ ---- + ------------ +
| Table | Create Table |
+ ---- + ------------ +
| Articles | create table 'articles '(
'Article _ id' int (11) unsigned not null auto_increment,
'Category _ id' tinyint (3) unsigned not null,
'Member _ id' int (11) unsigned not null,
'Title' varchar (255) not null,
Primary key ('article _ id '),
KEY 'Category _ id' ('Category _ id '),
KEY 'Member _ id' ('Member _ id '),
CONSTRAINT 'articles _ ibfk_1 'foreign key ('Category _ id') REFERENCES 'category' ('id ')
) ENGINE = InnoDB default charset = latin1 |
+ ---- + ------------ +
1 row in set (0.01 sec)

We can see that the foreign key symbol of articles. category_id is articles_ibfk_1, because you can run the following command to delete the foreign key definition:

Mysql> alter table articles drop foreign key articles_ibfk_1;
Query OK, 1 row affected (0.66 sec)
Records: 1 Duplicates: 0 Warnings: 0

6. Summary
---
The disadvantage of introducing a foreign key is that it will reduce the speed and performance. of course, there are many advantages of foreign keys.