MySql multi-to-multi-link foreign key application _ MySQL

MySql multi-to-multi-link foreign key application Mysql foreign key

BitsCN.com

Business Requirements: The user table r_user stores user name and other information. Now you need to set up a work base for each user. one user can have multiple work bases, and multiple users can also have one work base, that is, many-to-many relationship. (Foreign key. if two tables A, B, and C are the primary keys of A, and B also has the C Field, C is the foreign key of Table B, foreign key constraints are mainly used to maintain data consistency between two tables)

Design scheme:

Solution 1: Create a user base table and maintain a one-to-many relationship with the r_user base table. the primary key id of r_user is used as the foreign key user_id of r_user_base. Load all the user's work bases in the r_user_base table using the id in r_user.

Solution 2: create a base table base_info to save all existing bases and create a user_base relational table ., The relationship table user_base has two foreign keys: user_id and base_id.

Solution 1 features that you only need to key a table to meet your business needs. The disadvantage is that it is not modular enough. if we need to use the base information elsewhere, we need to build a base surface.

Solution 2 is characterized by using a relational table to connect two information tables. It facilitates maintenance and reuse of information tables.

Based on business needs and future expansion and reusability considerations, solution 2 is used to meet requirements.

MySQL 3.23.44 and later versions support foreign key constraints for InnoDB engine tables.

Usage conditions of foreign keys:

1. the two tables must be InnoDB tables, and MyISAM tables do not currently support foreign keys (it is said that foreign keys may be supported in later versions, but at least not supported currently );

2. the foreign key column must have an index. MySQL 4.1.2 and later versions will automatically create an index when creating the foreign key. However, if the foreign key column is in an earlier version, the index must be created;

3. 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;

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

CascadeMethod

When updating/deleting records in the parent table, synchronize update/delete matching records in the child table
On delete cascade is available from mysql3.23.50; on update cascade is available from mysql4.0.8.

Set nullMethod

When updating/deleting records in the parent table, set the columns of matched records in the child table to null.

Note that the foreign key column of the sub-table cannot be not null.

On delete set null is available from mysql3.23.50; on update set null is available from mysql4.0.8
No actionMethod

If the child table has matched records, the update/delete operation on the candidate keys corresponding to the parent table is not allowed.
This is ANSI SQL-92 standard, supported since mysql4.0.8
RestrictMethod

The same as no action, both check foreign key constraints immediately

Create a personnel information table:

1 CREATE TABLE `r_user` (2   `id` bigint(20) NOT NULL AUTO_INCREMENT,3   `NAME` varchar(20) DEFAULT NULL,4   `PASSWORD` varchar(50) DEFAULT NULL,5   `STAFF_NUM` varchar(20) DEFAULT NULL,6    `USER_NAME` varchar(20) DEFAULT NULL,7   PRIMARY KEY (`id`),8   ) ENGINE=InnoDB AUTO_INCREMENT=54 DEFAULT CHARSET=utf8 

Create Base info table

1 CREATE TABLE `branch_info` (2   `ID` bigint(20) 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 

Link table:

1 CREATE TABLE `user_work_base` (2   `id` bigint(20) NOT NULL AUTO_INCREMENT,3   `version` int(11) NOT NULL,4   `user_id` bigint(20) NOT NULL ,5   `base_id` bigint(20) NOT NULL ,6   PRIMARY KEY (`id`),7   CONSTRAINT `user_work_base_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `r_user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,8   CONSTRAINT `user_work_base_ibfk_2` FOREIGN KEY (`base_id`) REFERENCES `branch_info` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE9 ) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8

Delete personnel A in the r_user table, and the relation table user_base automatically deletes the relational data of.

If the foreign key uses the Restrict method and only deletes A, an error is returned.

Cannot delete or update a parent row: a foreign key constraint fails ('maircrew', 'User _ work_base ', CONSTRAINT 'fk41eb46d32aa89ea0'

Foreign key ('User _ id') REFERENCES 'r _ user' ('id '))

BitsCN.com