How to define foreign key in MySQL

If a computer manufacturer, its database holds machine and accessories product information. The table used to save the whole product information is called pc; the table used to save the parts supply information is called parts.

There is a field in the pc table, used to describe the type of CPU used in this computer; there is a field in the parts table, the description of the CPU model, we can think of it as a list of all CPU models.

Obviously, the CPU used by this manufacturer must be a model that exists in the supply information sheet. At this point, there is a constraint between the two tables - the CPU model in the pc table is constrained by the model in the parts table.

First let's create the 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 the index

To set the foreign key, the two fields corresponding to the referencing table (ie, the pc table) and the referenced table (ie, the parts table) must all be indexed.

Parts of the table:

ALTER TABLE parts ADD INDEX idx_model (model);

The meaning of this sentence is to add an index for the parts table, the index is based on the model field, give the index a name idx_model.

Similar to the pc table:

ALTER TABLE pc ADD INDEX idx_cpumodel (cpumodel);

In fact these two indexes can be set when creating the table. This is just to highlight its necessity.

Define foreign keys

The following two tables for the establishment of the kind of "constraint". Because the pc's CPU model must refer to the parts table in the corresponding model, so we will pc table cpumodel field is set to "foreign key" (FOREIGN KEY), the key reference value from other tables.

ALTER TABLE pc ADD CONSTRAINT fk_cpu_model

FOREIGN KEY (cpumodel)

REFERENCES parts (model);

The first line is to say for the pc table to set the foreign key to the foreign key from a name called fk_cpu_model; The second line is to set the cpumodel field of the table as a foreign key; The third line is that the foreign key constraints The model field from the parts table.

In this way, we have a good foreign key! If we try to CREATE a pc, it uses the type of CPU does not exist in the parts table, then MySQL will prevent this PC is CREATE out.

Cascade operation

Consider the following scenario:

The technician found that the models entered into a series of cpu (possibly many) models a month ago were all typo by one letter and now need to be corrected. The hope is that when the Referenced Column in the parts table changes, the Referencing Column in the corresponding table will automatically correct itself.

Can be defined in the foreign key, at the last to join such a keyword:

ON UPDATE CASCADE;

That is, when the main table is updated, sub-tables (we) have a chain update action, it seems that some people like to call this "cascade" operation.

If you write this sentence completely, it is:

ALTER TABLE pc ADD CONSTRAINT fk_cpu_model

FOREIGN KEY (cpumodel)

REFERENCES parts (model)

ON UPDATE CASCADE;

In addition to CASCADE, but also RESTRICT (prohibit the main table changes), SET NULL

Supplement on the article:

If you need to delete records in the main table, when the corresponding records in the sub-table is not allowed to delete, plus ON delete restrict. The complete case is as follows:

Two tables, country and city, the country_id in the city is a foreign key.

Create table country (

country_id smallint 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),

constraint fk_city_country Foreign Key (country_id) References country (country_id) on DELETE restrict ON update cascade

) engine = innoDB default charset = utf8;

Delete foreign key:

Delete foreign key definition -----
When defining the foreign key articles.member_id foreign key more than articles.category_id clause a CONSTRAINT fk_member?
This fk_member is used to delete the foreign key definition, as follows:
mysql> ALTER TABLE articles DROP FOREIGN KEY fk_member;
Query OK, 1 row affected (0.25 sec)
Records: 1 Duplicates: 0 Warnings: 0

This article.member_id foreign key definition is deleted, but if the definition does not specify CONSTRAINT fk_symbol (foreign key symbols) how to delete it? Do not worry, there is no specified, MySQL will create one, you can view the following command:

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 `categories` (` id`)
ENGINE = InnoDB DEFAULT CHARSET = latin1 |
+ ---- + ------------ +
1 row in set (0.01 sec)

You can see that the foreign key symbol for articles.category_id is articles_ibfk_1 because you can delete the foreign key definition by executing the following command:

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 is the introduction of foreign keys will make the speed and performance degradation, of course, the advantages brought by the foreign key there are many.