MySQL FOREIGN key combat

First, the basic concept

1, MySQL "key" and "index" definition is the same, so the foreign key and the primary key is also one of the index. The difference is that MySQL automatically indexes the primary key for all tables, but the foreign key field must be the user

To make an explicit index. The fields used for foreign key relationships must be explicitly indexed in all reference tables, and InnoDB cannot automatically create indexes.

2, the foreign key can be one-to-two, the record of a table can only be connected with one record of another table, or one-to-many, a table record is connected with multiple records of another table.

3, if the need for better performance, and do not need integrity check, you can choose to use the MyISAM table type, if you want to build a table in MySQL based on referential integrity, and you want to maintain on this basis

Good performance, the best choice table structure for the InnoDB type.

4, foreign key conditions of use

① Two table must be a InnoDB table, MyISAM table temporarily does not support foreign keys

② foreign key columns must be indexed, MySQL 4.1.2 later versions will automatically create an index when a foreign key is created, but if an earlier version requires an explicit build;

The columns of the two tables of the ③ foreign key relationship must be of similar data types, that is, columns that can be converted to each other, such as int and tinyint, while int and char are not allowed;

5, the advantages of foreign keys: can make two tables association, to ensure consistency of data and implementation of some cascade operations.

Ii. Methods of Use

1. Create a foreign key syntax:

Definition syntax for foreign keys:

[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)

REFERENCES tbl_name (index_col_name, ...)

[on DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

[on UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

This syntax can be used when creating table and ALTER table, and if you do not specify constraint Symbol,mysql automatically generates a name.

On DELETE, the on update indicates the event-triggering limit, and can be set with parameters:

①restrict (Limit foreign key changes in appearance, default value)

②cascade (following foreign key changes)

③set null (SET NULL value)

④set default (Set defaults)

⑤no Action (no action, default)

2. Example

1) CREATE table 1

CREATE TABLE Repo_table (

repo_id char (+) NOT null primary key,

Repo_name char (+) NOT NULL)

Type=innodb;

CREATE TABLE 2

Mysql> CREATE TABLE Busi_table (

-busi_id char (+) NOT null primary key,

-Busi_name char (+) NOT NULL,

-repo_id char (+) NOT NULL,

Foreign KEY (repo_id) references repo_table (repo_id))

Type=innodb;

2) inserting data

INSERT into repo_table values ("N", "sz"); Success

INSERT into repo_table values ("All", "CD"); Success

INSERT into busi_table values ("1003", "CD", "13"); Success

INSERT into busi_table values ("1002", "sz", "12"); Success

INSERT into busi_table values ("1001", "GX", "11"); Failed, Tip:

ERROR 1452 (23000): Cannot add or update a child row:a FOREIGN KEY constraint fails (' Smb_man '. ' busi_table ', constraint ' Busi_table_ibfk_1 ' FOREIGN KEY (' repo_id ') REFERENCES ' repo_table ' (' repo_id '))

3) Increase CASCADE operations

Mysql> ALTER TABLE busi_table

Add Constraint Id_check

Foreign KEY (REPO_ID)

References Repo_table (repo_id)

-ON DELETE Cascade

-On UPDATE cascade;

-----

Engine=innodb DEFAULT charset=gb2312;//Another method that can replace Type=innodb;

3. Related operation

What the FOREIGN KEY constraint (table 2) means for the parent table (table 1):

When you update/delete on a parent table to update or delete a candidate key that has one or more matching rows in the child table, the behavior of the parent table is determined by the on update/on specified when the foreign key of the child table is defined

Delete clause.

Key words	Meaning
CASCADE	Delete all records that contain a referential relationship to the deleted key value
SET NULL	Modify all records that contain a reference relationship to the deleted key value, replace with a null value (only for fields that have been marked as NOT NULL)
RESTRICT	Deny delete requirements until a secondary table using the DELETE key value is manually deleted and no references are available (this is the default and the safest setting)
NO ACTION	You don't do anything.

4. Other

Indexing on foreign keys:

Index repo_id (repo_id),

Foreign KEY (repo_id) references repo_table (repo_id))

Technology sharing: www.kaige123.com

MySQL FOREIGN key combat