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

     1  drop database if exists fk_self_refer;
     2  create database fk_self_refer;
     3  use fk_self_refer;
     4  drop table if exists t1;
     5  create table t1(a int primary key,b int, constraint `c1` foreign key fk1(b) references t1(a));
     6  show tables;
     7  Tables_in_fk_self_refer
     8  t1
     9  show create table t1;
    10  Table    Create Table
    11  t1    CREATE TABLE `t1` (\n`a` INT NOT NULL,\n`b` INT DEFAULT NULL,\nPRIMARY KEY (`a`),\nCONSTRAINT `c1` FOREIGN KEY (`b`) REFERENCES `t1` (`a`) ON DELETE RESTRICT ON UPDATE RESTRICT\n)
    12  insert into t1 values (1,1);
    13  insert into t1 values (2,1);
    14  insert into t1 values (3,2);
    15  insert into t1 values (5,4);
    16  Cannot add or update a child row: a foreign key constraint fails
    17  insert into t1 values (4,NULL);
    18  insert into t1 values (5,4);
    19  delete from t1 where a= 4;
    20  internal error: Cannot delete or update a parent row: a foreign key constraint fails
    21  delete from t1 where a= 5;
    22  delete from t1 where a= 4;
    23  insert into t1 values (4,4);
    24  select * from t1;
    25  a    b
    26  1    1
    27  2    1
    28  3    2
    29  4    4
    30  delete from t1 where a = 4;
    31  internal error: Cannot delete or update a parent row: a foreign key constraint fails
    32  update t1 set b = NULL where a= 4;
    33  delete from t1 where a = 4;
    34  drop table if exists t1;
    35  create table t1(a int primary key,b int not null, foreign key fk1(b) references t1(a));
    36  insert into t1 values (4,4);
    37  delete from t1 where a= 4;
    38  internal error: Cannot delete or update a parent row: a foreign key constraint fails
    39  update t1 set b=NULL where a= 4;
    40  constraint violation: Column 'b' cannot be null
    41  update t1 set b=5 where a= 4;
    42  Cannot add or update a child row: a foreign key constraint fails
    43  insert into t1 values (3,4);
    44  update t1 set b = 3 where a= 4;
    45  select * from t1;
    46  a    b
    47  3    4
    48  4    3
    49  delete from t1 where a = 3;
    50  internal error: Cannot delete or update a parent row: a foreign key constraint fails
    51  delete from t1 where a = 4;
    52  internal error: Cannot delete or update a parent row: a foreign key constraint fails
    53  update t1 set b = 4 where a =4;
    54  delete from t1 where a = 3;
    55  drop table if exists t1;
    56  create table t1(a int unique key,b int,constraint `c1` foreign key fk1(b) references t1(a));
    57  show tables;
    58  Tables_in_fk_self_refer
    59  t1
    60  show create table t1;
    61  Table    Create Table
    62  t1    CREATE TABLE `t1` (\n`a` INT DEFAULT NULL,\n`b` INT DEFAULT NULL,\nUNIQUE KEY `a` (`a`),\nCONSTRAINT `c1` FOREIGN KEY (`b`) REFERENCES `t1` (`a`) ON DELETE RESTRICT ON UPDATE RESTRICT\n)
    63  insert into t1 values (1,1);
    64  insert into t1 values (2,1);
    65  insert into t1 values (3,2);
    66  insert into t1 values (5,4);
    67  Cannot add or update a child row: a foreign key constraint fails
    68  insert into t1 values (4,NULL);
    69  insert into t1 values (5,4);
    70  delete from t1 where a= 4;
    71  internal error: Cannot delete or update a parent row: a foreign key constraint fails
    72  delete from t1 where a= 5;
    73  delete from t1 where a= 4;
    74  insert into t1 values (4,4);
    75  select * from t1;
    76  a    b
    77  1    1
    78  2    1
    79  3    2
    80  4    4
    81  delete from t1 where a = 4;
    82  internal error: Cannot delete or update a parent row: a foreign key constraint fails
    83  update t1 set b = NULL where a= 4;
    84  delete from t1 where a = 4;
    85  update t1 set a = NULL where a = 3;
    86  insert into t1 values (NULL,NULL);
    87  insert into t1 values (NULL,3);
    88  Cannot add or update a child row: a foreign key constraint fails
    89  insert into t1 values (NULL,2);
    90  drop table if exists t1;
    91  create table t1(a int,b int,key (a), foreign key fk1(b) references t1(a));
    92  internal error: failed to add the foreign key constraint
    93  drop table if exists t1;
    94  drop database if exists fk_self_refer;