github.com/pingcap/tiflow@v0.0.0-20240520035814-5bf52d54e205/tests/integration_tests/foreign_key/data/prepare.sql (about)

     1  set @@foreign_key_checks=1;
     2  drop database if exists `foreign_key`;
     3  create database `foreign_key`;
     4  use `foreign_key`;
     5  
     6  -- Check foreign key on delete/update cascade.
     7  create table t1 (id int key);
     8  create table t2 (id int key, constraint fk_1 foreign key (id) references t1 (id) on delete cascade on update cascade);
     9  begin;
    10  insert into t1 values (1),(2),(3),(4),(5);
    11  insert into t2 values (1),(2),(3),(4),(5);
    12  delete from t1 where id in (1,2);
    13  update t1 set id=id+10 where id =3;
    14  commit;
    15  set @@foreign_key_checks=0;
    16  delete from t1 where id=4;
    17  set @@foreign_key_checks=1;
    18  
    19  -- Check foreign key on delete/update set null.
    20  create table t3 (id int key);
    21  -- Manual create index for column b, since if the downstream TiDB doesn't support foreign key, then the downstream TiDB
    22  -- won't auto-create index for column b, then the upstream and downstream table structures are inconsistent.
    23  create table t4 (id int key, b int, index idx(b), constraint fk_2 foreign key (b) references t3 (id) on delete set null on update set null);
    24  begin;
    25  insert into t3 values (1),(2),(3),(4),(5);
    26  insert into t4 values (1, 1),(2, 2),(3, 3),(4, 4),(5, 5);
    27  delete from t3 where id in (1,2);
    28  update t3 set id=id+10 where id =3;
    29  commit;
    30  
    31  -- Check foreign key on delete/update restrict.
    32  create table t5 (id int key);
    33  create table t6 (id int key, constraint fk_3 foreign key (id) references t5 (id) on delete restrict on update restrict);
    34  insert into t5 values (1),(2),(3),(4),(5);
    35  insert into t6 values (1),(2),(3),(4),(5);
    36  set @@foreign_key_checks=0;
    37  delete from t5 where id in (1,2);
    38  update t5 set id=id+10 where id =3;
    39  delete from t5 where id=4;
    40  set @@foreign_key_checks=1;
    41  
    42  -- Check foreign key on ddl drop table.
    43  create table t7 (id int key);
    44  create table t8 (id int key, constraint fk_4 foreign key (id) references t5 (id) on delete restrict on update restrict);
    45  drop table t7, t8;
    46  
    47  -- Test for cascade delete.
    48  create table t9 (id int key, name varchar(10), leader int,  index(leader), constraint fk_5 foreign key (leader) references t9(id) ON DELETE CASCADE);
    49  insert into t9 values (1, 'boss', null), (10, 'l1_a', 1), (11, 'l1_b', 1), (12, 'l1_c', 1);
    50  insert into t9 values (100, 'l2_a1', 10), (101, 'l2_a2', 10), (102, 'l2_a3', 10);
    51  insert into t9 values (110, 'l2_b1', 11), (111, 'l2_b2', 11), (112, 'l2_b3', 11);
    52  insert into t9 values (120, 'l2_c1', 12), (121, 'l2_c2', 12), (122, 'l2_c3', 12);
    53  insert into t9 values (1000,'l3_a1', 100);
    54  delete from t9 where id=1;
    55  
    56  -- Test ddl add foreign key.
    57  create table t10 (id int key, b int, index(b));
    58  create table t11 (id int key, b int, index(b));
    59  insert into t10 values (1,1),(2,2),(3,3);
    60  insert into t11 values (1,1),(2,2),(3,3);
    61  alter table t11 add constraint fk_6 foreign key (b) references t10(id) on delete cascade on update cascade;
    62  delete from t10 where id=1;
    63  update t10 set id=id+10 where id=2;
    64  
    65  create table finish_mark (id int PRIMARY KEY);
    66