MySQL foreign key (ForeignKey) Introduction and usage precautions

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 keys for all tables, but the foreign key fields must be explicitly indexed by the user. 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 you need 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 want to maintain good performance on this basis, it is best to choose the 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, ...) [onDELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}][onUPDATE {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  Char(notnullprimarykeychar (notnull) ENGINE=InnoDB;

CREATE TABLE 2

Create Tablebusi_table (busi_idChar( -) not NULL Primary Key, Busi_nameChar( -) not NULL, repo_idChar( -) not NULL,Foreign Key(repo_id)Referencesrepo_table (repo_id)) ENGINE=InnoDB

Inserting data

Insert  intoRepo_tableValues(" A"," sz ");//SuccessInsert  intoRepo_tableValues(" -"," CD ");//SuccessInsert  intoBusi_tableValues("1003"," CD "," -");//SuccessInsert  intoBusi_tableValues("1002"," sz "," A");//SuccessInsert  intoBusi_tableValues("1001"," GX "," One");//Failed, hint: ERROR 1452 (23000): Cannot add or update a child row:a FOREIGN KEY constraint fails (' Smb_man '. ' Busi_table ', CO Nstraint ' Busi_table_ibfk_1 ' FOREIGN KEY (' repo_id ') REFERENCES ' repo_table ' (' repo_id '))

3) Increase CASCADE operations

Alter Table busi_table Add constraint Id_check Foreign Key (repo_id) References repo_table (repo_id)  on Delete Cascade  on Update Cascade;

-----

Note: Table one requires that the foreign key must be a primary key (PRIMARY key) and first, otherwise it will fail.

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 delete clause that is specified when the foreign key of the child table is defined.

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 references repo_table (repo_id))

Reprinted from: http://www.2cto.com/database/201501/367791.html

MySQL foreign key (ForeignKey) Introduction and usage precautions