Application of MySQL multi-to-many relation foreign key

Business Requirements: User table R_user Save the user name and other information. Now need to set up a work base for each user, a user can have more than one work base, multiple users can also have a work base, that is, many-to-many relationships. (Foreign key, if there are two table a,b,c is the primary key of a, and B also has a C field, then C is the foreign key of table B, foreign KEY constraints are mainly used to maintain data consistency between the two tables)

Design scheme:

Plan One: Establish a user base table, with R_user and user base table, maintain a one-to-many relationship, r_user the primary key ID as R_user_base foreign key user_id. Load all the user's work bases in the R_user_base table with the ID in R_user.

Scenario Two: Establish a base table Base_info Save all the existing bases and build a user_base relationship table. , the relational table User_base has two foreign keys user_id and base_id

Scenario one is characterized by the need for a single table of keys to complete business requirements. The disadvantage is not enough modularity, if in other places to use the base information, but also to build a base table

The feature of scenario two is that two tables of information are connected with a relational table. Facilitate the maintenance and reuse of information tables.

Based on business requirements and future expansion and re-use considerations, the implementation of scenario two requirements.

After MySQL version 3.23.44, the InnoDB Engine type table supports foreign KEY constraints.

Use conditions for foreign keys:

1. Two tables must be a InnoDB table, the MyISAM table temporarily does not support foreign keys (it is said that later versions may be supported, but at least not currently supported);

2. The foreign key column must be indexed, MySQL 4.1.2 later version will automatically create the index when the foreign key is established, but if the earlier version needs to display the establishment;

3. The columns of the two tables of the foreign-key relationship must be of similar data types, i.e., columns that can be converted to each other, such as int and tinyint, and int and char are not allowed;

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}]

Cascade Way

Synchronize update/delete out a matching record of a child table when Update/delete records on the parent table
On DELETE cascade available from mysql3.23.50; On UPDATE cascade available starting from mysql4.0.8.

SET NULL Way  

Set the column of the matching record on the child table to NULL when the record is update/delete on the parent table

Note that the foreign key column of the child table cannot be not NULL

On delete Set NULL is available from mysql3.23.50; On update set NULL is available starting from mysql4.0.8
No Action Way

If there are matching records in the child table, the Update/delete action is not allowed for the parent table corresponding to the candidate key
This is the ANSI SQL-92 standard, starting from mysql4.0.8 support
Restrict Way  

Same no action, check foreign KEY constraints immediately

To create a personnel information table:

1 CREATE TABLE ' R_user ' (2   ' id ' bigint () not NULL auto_increment,3   ' NAME ' varchar () DEFAULT null,4   ' PASSW ORD ' varchar ' default null,5   ' staff_num ' varchar (default null,6    ' user_name ' varchar) default null,7< C5/>primary KEY (' id '), 8   ) Engine=innodb auto_increment=54 DEFAULT Charset=utf8

Build a base information table

1 CREATE TABLE ' Branch_info ' (2   ' ID ' bigint () not null auto_increment,3    ' branch_code ' varchar (255) DEFAULT NULL , 4   ' branch_desc ' varchar (255) Default null,5   PRIMARY KEY (' ID ') 6) Engine=innodb auto_increment=19 default Charset=utf8

Relationship table:

1 CREATE TABLE ' User_work_base ' (2   ' id ' bigint () not NULL auto_increment,3   ' version ' int (one) not null,4   ' us er_id ' bigint () not NULL, 5   ' base_id ' bigint (a) not NULL, 6   PRIMARY KEY (' id '), 7   CONSTRAINT ' User_work_bas E_ibfk_1 ' FOREIGN KEY (' user_id ') REFERENCES ' R_user ' (' ID ') on the DELETE CASCADE on UPDATE cascade,8   CONSTRAINT ' User_wo Rk_base_ibfk_2 ' FOREIGN KEY (' base_id ') REFERENCES ' branch_info ' (' ID ') on the DELETE CASCADE on UPDATE CASCADE9) Engine=inno DB auto_increment=37 DEFAULT Charset=utf8

Deleting a person r_user table in person A, the relationship table User_base automatically deletes the relationship data for a.

If the foreign key uses restrict method, only delete a, then error.

Cannot delete or update a parent ROW:A FOREIGN KEY constraint fails (' Maircrew ', ' user_work_base ', constraint ' fk41eb46d32 Aa89ea0 '

FOREIGN KEY (' user_id ') REFERENCES ' R_user ' (' ID '))

Application of MySQL multi-to-many relation foreign key