Mysql foreign key Usage introduction, mysqlforeign

Mysql foreign key Usage introduction, mysqlforeign

I. Basic Concepts

1. MySQL has the same definition of "key" and "Index", so foreign keys and primary keys are also an index. The difference is that MySQL will automatically index the primary keys of all tables, but the foreign key field must be explicitly indexed by the user. Fields used for foreign key relationships must be explicitly indexed in all reference tables. InnoDB cannot automatically create indexes.

2. The foreign key can be one-to-one, and the record of one table can only be connected to one record of another table, or one-to-multiple, records of one table are connected to multiple records of another table.

3. If you need better performance and do not need integrity check, you can select the MyISAM Table type, if you want to create a table in MySQL Based on the integrity of the reference and maintain good performance on this basis, it is best to select the table structure as innoDB type.

4. usage conditions of foreign keys

① The two tables must be InnoDB tables, and the MyISAM table does not support foreign keys currently

② The foreign key column must have an index. MySQL 4.1.2 and later versions will automatically create an index when a foreign key is created. However, if an earlier version is used, an index must be explicitly created;

③ The columns of the foreign key relationship must be of similar data types, that is, columns that can be converted to each other. For example, int and tinyint can be used, but int and char cannot;

5. Benefits of Foreign keys: two tables can be associated to ensure data consistency and perform cascade operations.

Ii. Usage

1. syntax for creating foreign keys:

Syntax for defining 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 in create table and alter table. If the CONSTRAINT symbol is not specified, MYSQL automatically generates a name.

On delete and on update indicate the event trigger limit. You can set the following parameters:

① RESTRICT (RESTRICT foreign key changes in the external table, default value)

② CASCADE (with foreign key changes)

③ Set null (set null)

④ Set default (SET the DEFAULT value)

⑤ No action (no action, default)

2. Example

1) Create Table 1

Create table repo_table (

Repo_id char (13) not null primary key,

Repo_name char (14) not null)

Type = innodb;

Create Table 2

Mysql> create table busi_table (

-> Busi_id char (13) not null primary key,

-> Busi_name char (13) not null,

-> Repo_id char (13) not null,

-> Foreign key (repo_id) references repo_table (repo_id ))

-> Type = innodb;

2) insert data

Insert into repo_table values ("12", "sz"); // success

Insert into repo_table values ("13", "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, prompt:

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) add 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 can replace type = innodb;

3. Related Operations

Description of the foreign key constraint (table 2) on the parent table (table 1:

When updating or deleting a parent table to update or delete one or more candidate keys that match the row in the child table, the behavior of the parent table depends on: the on update/on delete clause specified when defining the foreign key of the sub-table.

Keywords	Description
CASCADE	Delete all records with reference to the deleted key value
SET NULL	Modify all records that have reference relationships with deleted key values and replace them with NULL values (only for Fields marked as not null)
RESTRICT	Reject the deletion request until the secondary table that uses the deleted key value is manually deleted and has no reference (this is the default setting and the safest setting)
NO ACTION	Nothing

4. Others

Create an index on the foreign key:

Index repo_id (repo_id ),

Foreign key (repo_id) references repo_table (repo_id ))