current position：Home>MySQL foreign key impact
MySQL foreign key impact
2022-06-24 12:39:55【Kevin Cui】
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 .
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 .
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 .
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 .
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 .
author[Kevin Cui],Please bring the original link to reprint, thank you.
The sidebar is recommended
- Tencent Youtu, together with Tencent security Tianyu and wechat, jointly launched an infringement protection scheme
- Fbnet/fbnetv2/fbnetv3: Facebook's lightweight network exploration in NAS | lightweight network
- A flaw in R markdown: folders cannot have Chinese
- Getting started with scrapy
- Detailed explanation of the execution order of the expression and loop body in the for loop
- Remote terminal RTU slope monitoring and early warning
- Jupyter notebook service installation and startup
- About Adobe Photoshop adjusting selection
- Kubernetes practical skills: use cert manager to issue free certificates for DNSPod domain names
- In depth analysis, from ordinary clock system to various time service modes
guess what you like
What are the software prototyping tools?
[tke] GPU node NVIDIA Tesla driver reinstallation
A good habit that makes your programming ability soar
Google hacking search engine attack and Prevention
Data stack technology sharing: open source · data stack - extend flinksql to realize the join of flow and dimension tables
Pinduoduo press the user accelerator key
Installing sqlserver extension PDO of PHP under Linux_ sqlsrv
Go basic series | 1 Leading
Node cache vs browser cache
Istio practical skills: implement header based authorization
- [Tencent cloud 618 countdown!] Promotion strategy of the promotion activities
- Simple and flexible permission design?
- Making daily menu applet with micro build low code
- 99% of the students can't write good code because of this problem!
- Istio practical skills: using prism to construct multi version test services
- Kubernetes practical technique: setting kernel parameters for pod
- A scheme for crawlers to collect public opinion data
- Essential key steps in the construction of e-commerce live broadcast source code
- How do websites and we media tap user needs? Deeply expose the secrets behind the keywords!
- From theory to practice, decipher Alibaba's internal MySQL optimization scheme in simple terms
- Continuous testing | key to efficient testing in Devops Era
- It's settled! Bank retail credit risk control just does it!
- Encapsulate the method of converting a picture file object to Base64
- The pod is evicted due to insufficient disk space of tke node
- Post processing - deep camera deformation effects
- Tencent released credit risk control results safely: it has helped banks lend more than 100 billion yuan
- Interesting erasure code
- Five minutes to develop your own code generator
- How to make secruecrt more productive
- About me, a 19 line programmer
- Kubernetes practical skill: entering container netns
- [programming navigation] the practical code summarized by foreign great God, learned in 30 seconds!
- Design and implementation of high performance go log library zap
- Ghost, a synonym for blog system
- Engage in audio and video development? Several things I have to say about SRT live broadcast protocol
- IOMMU (VII) -vfio and mdev
- [highlights] summary of award-winning activities of Tencent cloud documents
- The programmer's graduation project is still bald after a year
- How to solve the problem that MBR does not support partitions over 2T, and lossless transfer to GPT
- Another prize! Tencent Youtu won the leading scientific and technological achievement award of the 2021 digital Expo
- Use txvideoeditor to add watermark and export video card at 99%? No successful failed callback?
- Reset the password, and the automatic login of the website saved by chrome Google browser is lost. What is the underlying reason?
- [log service CLS] Tencent cloud log service CLS accesses CDN
- [live broadcast of celebrities] elastic observability workshop
- Smart Policing: how to use video intelligent analysis technology to help urban policing visual comprehensive supervision and command system
- SMS SMS
- WPF from zero to 1 tutorial details, suitable for novices on the road
- Dingding, Feishu, and enterprise wechat: different business approaches
- [2022 national tournament simulation] BigBen -- determinant, Du Jiao sieve
- How to do research on plant endophytes? Special topic on Microbiology