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

     1  drop database if exists fk_self_refer4;
     2  create database fk_self_refer4;
     3  use fk_self_refer4;
     4  drop table if exists t1;
     5  create table t1(a int primary key,b int);
     6  show tables;
     7  Tables_in_fk_self_refer4
     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`)\n)
    12  insert into t1 values (1,2),(3,4),(5,6);
    13  alter table t1 add constraint fk1 foreign key (b) references t1(a);
    14  Cannot add or update a child row: a foreign key constraint fails
    15  delete from t1;
    16  insert into t1 values (1,1),(2,3),(3,2);
    17  alter table t1 add constraint fk1 foreign key (b) references t1(a);
    18  alter table t1 add foreign key (b) references t1(a);
    19  alter table t1 add constraint fk1 foreign key (b) references t1(a);
    20  Duplicate foreign key constraint name 'fk1'
    21  insert into t1 values (4,4),(6,5),(5,6);
    22  insert into t1 values (7,8);
    23  Cannot add or update a child row: a foreign key constraint fails
    24  alter table t1 drop foreign key fk1;
    25  insert into t1 values (7,8);
    26  Cannot add or update a child row: a foreign key constraint fails
    27  drop table if exists t1;
    28  create table t2(a int);
    29  insert into t2 values (1),(2),(3);
    30  alter table t2 add constraint fk1 foreign key (a) references t1(a);
    31  no such table fk_self_refer4.t1
    32  alter table t2 add constraint fk1 foreign key (a) references t1(a);
    33  no such table fk_self_refer4.t1
    34  show create table t2;
    35  Table    Create Table
    36  t2    CREATE TABLE `t2` (\n`a` INT DEFAULT NULL\n)
    37  insert into t2 values (7);
    38  insert into t2 values (6);
    39  alter table t2 drop foreign key fk1;
    40  internal error: Can't DROP 'fk1'; check that column/key exists
    41  show create table t2;
    42  Table    Create Table
    43  t2    CREATE TABLE `t2` (\n`a` INT DEFAULT NULL\n)
    44  insert into t2 values (7);
    45  alter table t2 add constraint fk1 foreign key (a) references t1(a);
    46  no such table fk_self_refer4.t1
    47  alter table t2 add constraint fk1 foreign key (a) references t2(a);
    48  internal error: foreign key a can not reference to itself
    49  delete from t2 where a = 7;
    50  alter table t2 add constraint fk1 foreign key (a) references t1(a);
    51  no such table fk_self_refer4.t1
    52  update t2 set a = 7 where a = 6;
    53  select * from t1;
    54  SQL parser error: table "t1" does not exist
    55  delete from t1 where a = 6;
    56  no such table fk_self_refer4.t1
    57  update t1 set b = NULL where a = 5;
    58  no such table fk_self_refer4.t1
    59  select * from t1;
    60  SQL parser error: table "t1" does not exist
    61  update t2 set a = NULL where a = 6;
    62  select * from t2;
    63  a
    64  1
    65  2
    66  3
    67  7
    68  delete from t1 where a = 6;
    69  no such table fk_self_refer4.t1
    70  select * from t1;
    71  SQL parser error: table "t1" does not exist
    72  drop table t1;
    73  no such table fk_self_refer4.t1
    74  drop table t2;
    75  drop table t1;
    76  no such table fk_self_refer4.t1
    77  create table t1(a int primary key ,b int);
    78  alter table t1 add constraint `fk1` foreign key (b) references t1(a);
    79  alter table t1 add constraint `fk2` foreign key (b) references t1(a);
    80  alter table t1 add constraint `fk3` foreign key (b) references t1(a);
    81  alter table t1 add constraint `fk4` foreign key (b) references t1(a);
    82  alter table t1 add constraint `fk5` foreign key (b) references t1(a);
    83  show create table t1;
    84  Table    Create Table
    85  t1    CREATE TABLE `t1` (\n`a` INT NOT NULL,\n`b` INT DEFAULT NULL,\nPRIMARY KEY (`a`),\nCONSTRAINT `fk5` FOREIGN KEY (`b`) REFERENCES `t1` (`a`) ON DELETE RESTRICT ON UPDATE RESTRICT,\nCONSTRAINT `fk4` FOREIGN KEY (`b`) REFERENCES `t1` (`a`) ON DELETE RESTRICT ON UPDATE RESTRICT,\nCONSTRAINT `fk3` FOREIGN KEY (`b`) REFERENCES `t1` (`a`) ON DELETE RESTRICT ON UPDATE RESTRICT,\nCONSTRAINT `fk2` FOREIGN KEY (`b`) REFERENCES `t1` (`a`) ON DELETE RESTRICT ON UPDATE RESTRICT,\nCONSTRAINT `fk1` FOREIGN KEY (`b`) REFERENCES `t1` (`a`) ON DELETE RESTRICT ON UPDATE RESTRICT\n)
    86  insert into t1 values (1,4),(2,3),(3,2),(4,1),(5,5);
    87  delete from t1 where a = 4;
    88  internal error: Cannot delete or update a parent row: a foreign key constraint fails
    89  delete from t1 where a = 5;
    90  internal error: Cannot delete or update a parent row: a foreign key constraint fails
    91  alter table t1 drop foreign key fk1;
    92  alter table t1 drop foreign key fk2;
    93  alter table t1 drop foreign key fk3;
    94  alter table t1 drop foreign key fk4;
    95  delete from t1 where a = 4;
    96  internal error: Cannot delete or update a parent row: a foreign key constraint fails
    97  delete from t1 where a = 5;
    98  internal error: Cannot delete or update a parent row: a foreign key constraint fails
    99  alter table t1 drop foreign key fk5;
   100  delete from t1 where a = 4;
   101  delete from t1 where a = 5;
   102  delete from t1 where a = 1;
   103  alter table t1 add constraint `fk1` foreign key (b) references t1(a);
   104  alter table t1 drop foreign key fk1, drop foreign key fk2, drop foreign key fk1;
   105  internal error: Can't DROP 'fk2'; check that column/key exists
   106  alter table t1 add constraint fk1 foreign key (b) references t1(a), drop foreign key fk1, add constraint fk1 foreign key (b) references t1(a);
   107  Duplicate foreign key constraint name 'fk1'
   108  alter table t1 drop foreign key fk1, drop foreign key fk1, drop foreign key fk1;
   109  alter table t1 add constraint fk1 foreign key (b) references t1(a), drop foreign key fk1, add constraint fk1 foreign key (b) references t1(a);
   110  internal error: Can't DROP 'fk1'; check that column/key exists
   111  alter table t1 add constraint fk1 foreign key (b) references t1(a), add constraint fk1 foreign key (b) references t1(a);
   112  Duplicate foreign key constraint name 'fk1'
   113  alter table t1 add constraint `fk1` foreign key (b) references t1(a);
   114  alter table t1 drop constraint fk1;
   115  drop database if exists fk_self_refer4;