github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/foreign_key/fk_self_refer3.result (about)

     1  drop database if exists fk_self_refer3;
     2  create database fk_self_refer3;
     3  use fk_self_refer3;
     4  drop table if exists t1;
     5  create table t1(a int, b int,
     6  c int,
     7  d int, e int,
     8  f int, g int,
     9  primary key (a,b),
    10  unique key (a,c),
    11  constraint `c1` foreign key fk1(d,e) references t1(a,b),
    12  constraint `c2` foreign key fk2(f,g) references t1(a,c)
    13  );
    14  show create table t1;
    15  Table    Create Table
    16  t1    CREATE TABLE `t1` (\n`a` INT NOT NULL,\n`b` INT NOT NULL,\n`c` INT DEFAULT NULL,\n`d` INT DEFAULT NULL,\n`e` INT DEFAULT NULL,\n`f` INT DEFAULT NULL,\n`g` INT DEFAULT NULL,\nPRIMARY KEY (`a`,`b`),\nUNIQUE KEY `a` (`a`,`c`),\nCONSTRAINT `c1` FOREIGN KEY (`d`,`e`) REFERENCES `t1` (`a`,`b`) ON DELETE RESTRICT ON UPDATE RESTRICT,\nCONSTRAINT `c2` FOREIGN KEY (`f`,`g`) REFERENCES `t1` (`a`,`c`) ON DELETE RESTRICT ON UPDATE RESTRICT\n)
    17  insert into t1 values (1,2,1,1,2,1,1);
    18  insert into t1 values (1,3,2,1,3,1,2);
    19  insert into t1 values (1,4,3,1,5,1,2);
    20  Cannot add or update a child row: a foreign key constraint fails
    21  insert into t1 values (1,4,3,1,4,1,4);
    22  Cannot add or update a child row: a foreign key constraint fails
    23  insert into t1 values (1,4,3,1,4,1,3);
    24  update t1 set b = 5 where b = 4;
    25  internal error: Cannot delete or update a parent row: a foreign key constraint fails
    26  update t1 set e = 5 where b = 4;
    27  Cannot add or update a child row: a foreign key constraint fails
    28  update t1 set e = NULL where b = 4;
    29  update t1 set b = 5 where b = 4;
    30  update t1 set c = 4 where b = 5;
    31  internal error: Cannot delete or update a parent row: a foreign key constraint fails
    32  update t1 set g = 4 where b = 5;
    33  Cannot add or update a child row: a foreign key constraint fails
    34  update t1 set g = 2 where b = 5;
    35  update t1 set c = 4 where b = 5;
    36  delete from t1 where b = 5;
    37  delete from t1 where b = 3;
    38  internal error: Cannot delete or update a parent row: a foreign key constraint fails
    39  update t1 set e = NULL where b = 3;
    40  delete from t1 where b = 3;
    41  internal error: Cannot delete or update a parent row: a foreign key constraint fails
    42  update t1 set g = NULL where b = 3;
    43  delete from t1 where b = 3;
    44  delete from t1 where b = 2;
    45  internal error: Cannot delete or update a parent row: a foreign key constraint fails
    46  update t1 set e = NULL where b = 2;
    47  delete from t1 where b = 2;
    48  internal error: Cannot delete or update a parent row: a foreign key constraint fails
    49  update t1 set g = NULL where b = 2;
    50  delete from t1 where b = 2;
    51  select count(*) from t1;
    52  count(*)
    53  0
    54  drop table if exists p1;
    55  create table p1(
    56  pa int,
    57  pb int,
    58  primary key (pa,pb)
    59  );
    60  drop table if exists q2;
    61  create table q2(
    62  qa int,
    63  qb int,
    64  unique key (qa,qb)
    65  );
    66  drop table if exists t1;
    67  create table t1(a int, b int,
    68  c int,
    69  d int, e int,
    70  f int, g int,
    71  h int, i int,
    72  j int, k int,
    73  primary key (a,b),
    74  unique key (a,c),
    75  constraint `c1` foreign key fk1(d,e) references t1(a,b),
    76  constraint `c2` foreign key fk2(f,h) references t1(a,c),
    77  constraint `c3` foreign key fk3(h,i) references p1(pa,pb),
    78  constraint `c4` foreign key fk4(h,k) references q2(qa,qb)
    79  );
    80  show create table t1;
    81  Table    Create Table
    82  t1    CREATE TABLE `t1` (\n`a` INT NOT NULL,\n`b` INT NOT NULL,\n`c` INT DEFAULT NULL,\n`d` INT DEFAULT NULL,\n`e` INT DEFAULT NULL,\n`f` INT DEFAULT NULL,\n`g` INT DEFAULT NULL,\n`h` INT DEFAULT NULL,\n`i` INT DEFAULT NULL,\n`j` INT DEFAULT NULL,\n`k` INT DEFAULT NULL,\nPRIMARY KEY (`a`,`b`),\nUNIQUE KEY `a` (`a`,`c`),\nCONSTRAINT `c1` FOREIGN KEY (`d`,`e`) REFERENCES `t1` (`a`,`b`) ON DELETE RESTRICT ON UPDATE RESTRICT,\nCONSTRAINT `c2` FOREIGN KEY (`f`,`h`) REFERENCES `t1` (`a`,`c`) ON DELETE RESTRICT ON UPDATE RESTRICT,\nCONSTRAINT `c3` FOREIGN KEY (`h`,`i`) REFERENCES `p1` (`pa`,`pb`) ON DELETE RESTRICT ON UPDATE RESTRICT,\nCONSTRAINT `c4` FOREIGN KEY (`h`,`k`) REFERENCES `q2` (`qa`,`qb`) ON DELETE RESTRICT ON UPDATE RESTRICT\n)
    83  insert into t1 values (1,2,3,1,2,1,3,4,4,4,4);
    84  internal error: Cannot add or update a child row: a foreign key constraint fails
    85  insert into p1 values (4,4);
    86  insert into q2 values (4,4);
    87  insert into t1 values ( 1,2, 4, 1,2, 1,10, 4,4, 10,4);
    88  insert into t1 values (1,3,3,1,2,1,10,NULL,NULL,NULL,4);
    89  insert into t1 values (
    90  1,4,5,
    91  1,3,
    92  1,5,
    93  5,5,
    94  10,5);
    95  internal error: Cannot add or update a child row: a foreign key constraint fails
    96  insert into p1 values (5,5);
    97  insert into q2 values (5,5);
    98  insert into t1 values (
    99  1,4,5,
   100  1,3,
   101  1,5,
   102  5,5,
   103  10,5);
   104  update t1 set c = 6 where b = 3;
   105  update t1 set c = NULL where b = 3;
   106  delete from t1 where c = 4;
   107  internal error: Cannot delete or update a parent row: a foreign key constraint fails
   108  delete from t1 where c = 4;
   109  internal error: Cannot delete or update a parent row: a foreign key constraint fails
   110  update t1 set h = NULL where c = 4;
   111  delete from t1 where c = 4;
   112  internal error: Cannot delete or update a parent row: a foreign key constraint fails
   113  update t1 set d = NULL where c = 4;
   114  delete from t1 where c = 4;
   115  internal error: Cannot delete or update a parent row: a foreign key constraint fails
   116  update t1 set f = NULL,g = NULL where c = 4;
   117  delete from t1 where c = 4;
   118  internal error: Cannot delete or update a parent row: a foreign key constraint fails
   119  update t1 set i = NULL, j = NULL, k = NULL where c = 4;
   120  delete from t1 where c = 4;
   121  internal error: Cannot delete or update a parent row: a foreign key constraint fails
   122  update t1 set c = NULL where b = 2;
   123  delete from t1 where b = 2;
   124  internal error: Cannot delete or update a parent row: a foreign key constraint fails
   125  drop table if exists p1;
   126  internal error: can not drop table 'p1' referenced by some foreign key constraint
   127  create table t1(a int primary key,b int,constraint `c1` foreign key fk1(b) references t1(a));
   128  table t1 already exists
   129  show tables;
   130  Tables_in_fk_self_refer3
   131  p1
   132  q2
   133  t1
   134  show create table t1;
   135  Table    Create Table
   136  t1    CREATE TABLE `t1` (\n`a` INT NOT NULL,\n`b` INT NOT NULL,\n`c` INT DEFAULT NULL,\n`d` INT DEFAULT NULL,\n`e` INT DEFAULT NULL,\n`f` INT DEFAULT NULL,\n`g` INT DEFAULT NULL,\n`h` INT DEFAULT NULL,\n`i` INT DEFAULT NULL,\n`j` INT DEFAULT NULL,\n`k` INT DEFAULT NULL,\nPRIMARY KEY (`a`,`b`),\nUNIQUE KEY `a` (`a`,`c`),\nCONSTRAINT `c1` FOREIGN KEY (`d`,`e`) REFERENCES `t1` (`a`,`b`) ON DELETE RESTRICT ON UPDATE RESTRICT,\nCONSTRAINT `c2` FOREIGN KEY (`f`,`h`) REFERENCES `t1` (`a`,`c`) ON DELETE RESTRICT ON UPDATE RESTRICT,\nCONSTRAINT `c3` FOREIGN KEY (`h`,`i`) REFERENCES `p1` (`pa`,`pb`) ON DELETE RESTRICT ON UPDATE RESTRICT,\nCONSTRAINT `c4` FOREIGN KEY (`h`,`k`) REFERENCES `q2` (`qa`,`qb`) ON DELETE RESTRICT ON UPDATE RESTRICT\n)
   137  insert into t1 values (1,1);
   138  Column count doesn't match value count at row 1
   139  insert into t1 values (2,1);
   140  Column count doesn't match value count at row 1
   141  insert into t1 values (3,2);
   142  Column count doesn't match value count at row 1
   143  delete A from t1 as A,  t1 as B where A.a = B.b;
   144  delete A,B from t1 as A,  t1 as B where A.a = B.b;
   145  update t1 as A,t1 as B set A.a = 4 where A.a = B.b;
   146  update t1 as A,t1 as B set A.a = 4, B.b = 3 where A.a = B.b;
   147  update t1 as A,t1 as B set A.a = 4, A.b = 3 where A.a = B.b;
   148  update t1 as A,t1 as B set B.a = 4 where A.a = B.b;
   149  update t1 as A,t1 as B set A.a = 4 where A.a = 3;
   150  update t1 as A set A.a = 3, A.b = 3 where A.a = A.b;
   151  insert into t1 values (3,3);
   152  Column count doesn't match value count at row 1
   153  update t1 as A set A.a = 4, A.b = 4 where A.a = A.b and A.a = 3;
   154  update t1 as A set A.b = 4, A.a = 4 where A.a = A.b and A.a = 3;
   155  drop table if exists t1;
   156  drop database if exists fk_self_refer3;