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

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