Support Foreign Key Dev Task

[crazycs520]

Support Foreign Key Dev Task

Related Issue: #18209
Design Doc: #35982
Draft PR: #36757

Dev Task

DDL Dev Task

• Create table with foreign key Support create table with foreign key #37147
  • Add global variable: tidb_enable_foreign_key
• Alter table add foreign key
  • Support add foreign key without auto create index. add foreign key constrain need validation #38168
  • Support add foreign key with auto create index. support add foreign key constraint with auto-create index. #38762
• Rename Column with foreign key rename column should update foreign key constrain #37868
• Rename Table with foreign key rename table should update foreign key constraint #38080
• Rename Tables with foreign key rename tables should update foreign key constraint #38133
• Modify Column with foreign key modify column should check foreign key constrain #37821
• Drop Database with foreign key drop database should check foreign key constraint #38005
• Drop/Truncate Table with foreign key add foreign key check when execute drop/truncate table #37772
• Drop Index is needed in foreign key drop index should check index is needed in foreign key #37812
• Drop Column with foreign key. drop column should check foreign key constrain #37842

DML Dev Task

• Support foreign key ON DELETE
  • Support ON DELETE RESTRICT, NO ACTION, SET DEFAULT action. support foreign key constrain check when execute delete statement #38221
  • Support ON DELETE CASCADE action. support foreign key cascade delete #38376
  • Support ON DELETE SET NULL action. support foreign key on delete set null #38594
• Support foreign key ON UPDATE
  • Support ON UPDATE RESTRICT, NO ACTION, SET DEFAULT action. support foreign key constrain check when execute update statement #38155
  • Support ON UPDATE CASCADE action. support foreign key on update cascade and set null #38651
  • Support ON UPDATE SET NULL action. support foreign key on update cascade and set null #38651
  • Support foreign key in INSERT ON DUPLICATE KEY statement. support foreign key on update cascade in insert on duplicate update statement #38792
• Support foreign key ON INSERT support foreign key constrain check when execute Insert statement #37465
  • support foreign key constrain check when execute insert statement
  • Add lock on reference table row when insert/update child table.
  • Support INSERT IGNORE when foreign key constraints failed.
• Support foreign key check when UPDATE child table. support foreign key constrain check when execute update statement #38155
• Support foreign key when execute REPLACE INTO statement support foreign key constraint when execute replace into statement #40068

Enhancement

• Show create table with foreign key show create table with foreign key need display database name #38118
• show create table should distinguish between old and new foreign key show create table should distinguish between old and new foreign key #38717
• Refine EXPLAIN plan about DML with foreign key.
  • Refine EXPLAIN ANALYZE info for INSERT IGNORE statement. Refine EXPLAIN ANALYZE information for INSERT IGNORE statement.  #38769
  • add foreign key check/cascade information in explain result Explain DML should contain foreign key check/cascade information #39166
  • add foreign key check/cascade information in explain analyze result Explain analyze DML should contain foreign key check/cascade information #39202

Compatibility

• enable foreign_key_checks variable by default, be consistent with MySQL. enable foreign_key_checks variable by default  #39694

After Foreign Key GA

• Remove global variable: tidb_enable_foreign_key remove tidb_enable_foreign_key variable #39670

Test

• Add GORM test about foreign key. https://github.com/pingcap/tidb-test/pull/1917
• Import MySQL Test. https://github.com/pingcap/tidb-test/pull/2006

User Doc

• 中文文档
• English Document