github.com/pingcap/tiflow@v0.0.0-20240520035814-5bf52d54e205/dm/tests/foreign_key/data/db1.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, 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  create table t4 (id int key, b int, foreign key (b) references t3 (id) on delete set null on update set null);
    22  begin;
    23  insert into t3 values (1),(2),(3),(4),(5);
    24  insert into t4 values (1, 1),(2, 2),(3, 3),(4, 4),(5, 5);
    25  delete from t3 where id in (1,2);
    26  update t3 set id=id+10 where id =3;
    27  commit;
    28  
    29  -- Check foreign key on delete/update restrict.
    30  create table t5 (id int key);
    31  create table t6 (id int key, foreign key (id) references t5 (id) on delete restrict on update restrict);
    32  insert into t5 values (1),(2),(3),(4),(5);
    33  insert into t6 values (1),(2),(3),(4),(5);
    34  set @@foreign_key_checks=0;
    35  delete from t5 where id in (1,2);
    36  update t5 set id=id+10 where id =3;
    37  delete from t5 where id=4;
    38  set @@foreign_key_checks=1;
    39  
    40  -- Check foreign key on ddl drop table.
    41  create table t7 (id int key);
    42  create table t8 (id int key, foreign key (id) references t5 (id) on delete restrict on update restrict);
    43  drop table t7, t8;
    44  
    45  -- Test for cascade delete.
    46  create table t9 (id int key, name varchar(10), leader int,  index(leader), foreign key (leader) references t9(id) ON DELETE CASCADE);
    47  insert into t9 values (1, 'boss', null), (10, 'l1_a', 1), (11, 'l1_b', 1), (12, 'l1_c', 1);
    48  insert into t9 values (100, 'l2_a1', 10), (101, 'l2_a2', 10), (102, 'l2_a3', 10);
    49  insert into t9 values (110, 'l2_b1', 11), (111, 'l2_b2', 11), (112, 'l2_b3', 11);
    50  insert into t9 values (120, 'l2_c1', 12), (121, 'l2_c2', 12), (122, 'l2_c3', 12);
    51  insert into t9 values (1000,'l3_a1', 100);
    52  delete from t9 where id=1;
    53  
    54  -- Test ddl add foreign key.
    55  create table t10 (id int key, b int, index(b));
    56  create table t11 (id int key, b int);
    57  insert into t10 values (1,1),(2,2),(3,3);
    58  insert into t11 values (1,1),(2,2),(3,3);
    59  alter table t11 add foreign key (b) references t10(id) on delete cascade on update cascade;
    60  delete from t10 where id=1;
    61  update t10 set id=id+10 where id=2;