MySQL foreign key impact

Database paradigm is an abstract concept , For relational databases , Effectively avoid data redundancy in relational databases , Reduce the storage space of the database , And reduce the cost of maintaining data integrity .

Database paradigm is also one of the core design patterns of relational database , Is also engaged in database development personnel essential knowledge . The foreign key is applicable to 3NF Third normal form , The function in the database is to maintain data consistency , integrity . The main purpose is to associate two tables , Control the data stored in the foreign key table . Foreign keys explain the business logic to some extent , Will make the design thoughtful, specific and comprehensive .

especially ,MySQL Primary key driven data , Experience tells us , Foreign keys are not really suitable for . Foreign keys are rarely used in the real world , Generally, it is the program that implements the association relationship .

Foreign key understanding

stay MySQL Foreign key associations , Contains a variety of action relation , As reflected in grammar ：

[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (col_name, ...)
    REFERENCES tbl_name (col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

Be careful ：

• The current scope of use of foreign keys InnoDB and NDB All storage engines use FOREIGN_KEY, And by lower_case_table_names Case sensitive system variables .

• Parent and child tables must use the same storage engine , And they cannot be defined as temporary tables .
  Creating a foreign key constraint requires... On the parent table REFERENCES jurisdiction .

• Corresponding columns in foreign and reference keys must have similar data types . Fixed precision type ( Such as INTEGER、DECIMAL) Must be the same size and symbol . The length of the string type does not have to be the same . For non binary ( character ) String of characters , The character set and collation must be the same .

• In the foreign key reference table , There must be an index , The foreign key column is in the first column with the same sequence . So that foreign key checking can be done quickly , You don't need a full table scan .

• NDB Require an explicit unique key on any column referenced as a foreign key ( Or the primary key ).InnoDB No .

• Index prefixes on foreign key columns are not supported ,BLOB and TEXT Columns cannot be contained in foreign keys .

• InnoDB Currently, foreign keys of partitioned tables are not supported

• A foreign key constraint cannot reference a virtually generated column .

The behavior of foreign keys

Grammatically, it contains three Action Direct reference ,update ,delete Corresponding operation ：

• CASCADE: Delete or update rows in the parent table , And automatically delete or update the matching rows in the sub table .

• SET NULL: Delete or update rows from the parent table , And set the foreign key column or column in the sub table to NULL.

• RESTRICT: Reject delete or update of parent table .

• NO ACTION: From standard SQL Key words of . stay MySQL in , amount to RESTRICT. If there is a related foreign key value in the referenced table ,MySQL The server refused to delete or update the parent table . Some database systems have deferred checks ,NO ACTION Is to postpone the inspection . stay MySQL in , Foreign key constraints are immediately checked , therefore NO ACTION And RESTRICT identical

• SET DEFAULT: This action is MySQL Parser recognition , however InnoDB and NDB All reject include ON DELETE SET DEFAULT or ON UPDATE SET DEFAULT Table definition of clause .

remarks ： For unspecified ON DELETE or ON UPDATE, The default action is always NO action

InnoDB The depth first search algorithm is used to perform cascading operations on the index records corresponding to foreign key constraints .

Locking mechanism

MySQL Extend metadata locks to foreign key constraint related tables as needed . For foreign key checking , Use shared read-only locks on related tables (lock TABLES READ). For cascading updates , Use a shared write lock on the related tables involved in the operation (lock TABLES write). When there are foreign key constraints , Every time you want to scan whether this record is qualified . So an associated field operation , It is prone to deadlock .

# structure 
CREATE TABLE categories(
   cat_id int not null auto_increment primary key,
   cat_name varchar(255) not null
) ENGINE=InnoDB;
CREATE TABLE products(
   prd_id int not null auto_increment primary key,
   prd_name varchar(355) not null,
   prd_price decimal,
   cat_id int not null,
   FOREIGN KEY fk_cat(cat_id)     REFERENCES categories(cat_id)
   ON UPDATE CASCADE
   ON DELETE RESTRICT
)ENGINE=InnoDB;
# data 
insert into categories(cat_id,cat_name) values(1,' Fruits '),(2,' vegetables ');
insert into products(prd_id,prd_name,prd_price,cat_id) values(1,' Apple ',9,1),(2,' Pineapple ',10,1),(3,' Chinese cabbage ',8,2),(4,' coke ',7,3);

1） Interesting demonstration :
Parent table insert data not submitted , Sub table update waiting .
The parent table was not submitted for update , Sub table insert wait .

# Minimum foreign keys 2 Table operation 
mysql> show OPEN TABLES where In_use > 0;
+----------+----------+--------+-------------+
| Database | Table    | In_use | Name_locked |
+----------+----------+--------+-------------+
| dbdemo   | products |      2 |           0 |
+----------+----------+--------+-------------+

mysql> SHOW ENGINE INNODB STATUS\G

# Foreign key error 
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2022-05-31 17:22:52 140626893833984 Transaction:
TRANSACTION 9011856, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1
MySQL thread id 23, OS thread handle 140626893833984, query id 168 localhost root updating
update categories set cat_id=3 where cat_id=2
Foreign key constraint fails for table `dbdemo`.`products`:
,
  CONSTRAINT `products_ibfk_1` FOREIGN KEY (`cat_id`) REFERENCES `categories` (`cat_id`)
Trying to update in parent table, in index PRIMARY tuple:
DATA TUPLE: 4 fields;
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 000000898290; asc       ;;
 2: len 7; hex 01000001970151; asc       Q;;
 3: len 6; hex e894ace88f9c; asc       ;;

But in child table `dbdemo`.`products`, in index fk_cat, there is a record:
PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 4; hex 80000003; asc     ;;

2）DDL Operations are constrained by foreign keys ：

mysql > TRUNCATE TABLE products;
Query OK, 0 rows affected (0.03 sec)

mysql > TRUNCATE TABLE  categories;
ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (`dbdemo`.`products`, CONSTRAINT `products_ibfk_1`)


mysql> DROP TABLE  categories;
ERROR 3730 (HY000): Cannot drop table 'categories' referenced by a foreign key constraint 'products_ibfk_1' on table 'products'.

Be careful ：MySQL The inner and outer keys are checked instantly , A foreign key check is run for each line . As mentioned above or use load data Import data , It often takes a lot of time to check foreign key constraints . occasionally , It can be handled flexibly , Ignore foreign key checking ：SET foreign_key_checks=0, The default value is 1.

3） The internal and external key values of the sub table are actually saved data , Once the foreign key of the parent table is updated , That sub table will be updated with . It is understandable that the index structure of the two tables has changed . In fact, an action triggers a double index operation ,IO Zoom in .

summary

The foreign key is MySQL There are two kinds of , One is how to ensure the integrity and consistency of database data ; Second, the impact of performance .

Data management is simple without foreign keys , It is easy to operate , High performance （ Import, export and other operations , stay insert, update, delete Data is faster ）

1. With foreign keys , As some additions involving foreign key fields , Delete , After update operation , You need to trigger related operations to check , Resources have to be consumed ;

2. When the delete , The bottom layer of update operation needs to reorganize the index table , This results in prolonged locking time ;

3. Foreign keys are also prone to deadlock because they need to request locks on other tables ;

4. The database needs to maintain internal management of foreign keys ;

Combine these factors , Using foreign keys in relational databases is not recommended , Except for the poor control , stay MySQL On such a lightweight database , The scope of influence is relatively large , It is suggested to rely on code logic to ensure .