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

     1  drop database if exists fk_self_refer;
     2  create database fk_self_refer;
     3  use fk_self_refer;
     4  
     5  ----create fk on a primary key----
     6  -- test only one fk self refer in a table
     7  drop table if exists t1;
     8  create table t1(a int primary key,b int, constraint `c1` foreign key fk1(b) references t1(a));
     9  show tables;
    10  show create table t1;
    11  insert into t1 values (1,1);
    12  insert into t1 values (2,1);
    13  insert into t1 values (3,2);
    14  
    15  --error. no number 4 in column a
    16  insert into t1 values (5,4);
    17  
    18  --no error. same as mysql
    19  insert into t1 values (4,NULL);
    20  
    21  --no error. number 4 is in column a
    22  insert into t1 values (5,4);
    23  
    24  --error. number 4 is referred
    25  delete from t1 where a= 4;
    26  
    27  --delete the row a = 5.
    28  delete from t1 where a= 5;
    29  
    30  --no error. number 4 is not referred
    31  delete from t1 where a= 4;
    32  
    33  --no error. number 4 is not referred
    34  insert into t1 values (4,4);
    35  
    36  select * from t1;
    37  
    38  --error. number 4 is referred by the b in same row
    39  delete from t1 where a = 4;
    40  
    41  --no error
    42  update t1 set b = NULL where a= 4;
    43  
    44  --no error. number 4 is not referred.
    45  delete from t1 where a = 4;
    46  
    47  drop table if exists t1;
    48  
    49  --column b is not null
    50  create table t1(a int primary key,b int not null, foreign key fk1(b) references t1(a));
    51  
    52  insert into t1 values (4,4);
    53  
    54  -- error. number is referred by the b in same row
    55  delete from t1 where a= 4;
    56  
    57  --error. b is not null
    58  update t1 set b=NULL where a= 4;
    59  
    60  --error. number 5 does not exists
    61  update t1 set b=5 where a= 4;
    62  
    63  --no error
    64  insert into t1 values (3,4);
    65  
    66  --no error
    67  update t1 set b = 3 where a= 4;
    68  
    69  --loop self reference
    70  select * from t1;
    71  
    72  --error. number 3 is referred
    73  delete from t1 where a = 3;
    74  --error. number 4 is referred
    75  delete from t1 where a = 4;
    76  
    77  --break loop self reference
    78  update t1 set b = 4 where a =4;
    79  
    80  --no error. number 3 is not referred
    81  delete from t1 where a = 3;
    82  
    83  
    84  ----create fk on a unique key----
    85  -- test only one fk self refer in a table
    86  drop table if exists t1;
    87  create table t1(a int unique key,b int,constraint `c1` foreign key fk1(b) references t1(a));
    88  show tables;
    89  show create table t1;
    90  insert into t1 values (1,1);
    91  insert into t1 values (2,1);
    92  insert into t1 values (3,2);
    93  
    94  --error. no number 4 in column a
    95  insert into t1 values (5,4);
    96  
    97  --no error. same as mysql
    98  insert into t1 values (4,NULL);
    99  
   100  --no error. number 4 is in column a
   101  insert into t1 values (5,4);
   102  
   103  --error. number 4 is referred
   104  delete from t1 where a= 4;
   105  
   106  --no error . delete the row a = 5.
   107  delete from t1 where a= 5;
   108  
   109  --no error. number 4 is not referred
   110  delete from t1 where a= 4;
   111  
   112  --no error. number 4 is not referred
   113  insert into t1 values (4,4);
   114  
   115  select * from t1;
   116  
   117  --error. number 4 is referred by the b in same row
   118  delete from t1 where a = 4;
   119  
   120  --no error
   121  update t1 set b = NULL where a= 4;
   122  
   123  --no error. number 4 is not referred.
   124  delete from t1 where a = 4;
   125  
   126  --no error
   127  update t1 set a = NULL where a = 3;
   128  
   129  --no error
   130  insert into t1 values (NULL,NULL);
   131  
   132  --error
   133  insert into t1 values (NULL,3);
   134  
   135  --no error
   136  insert into t1 values (NULL,2);
   137  
   138  
   139  ----create fk on a secondary key----
   140  -- test only one fk self refer in a table
   141  drop table if exists t1;
   142  -- mo error. does not support fk on secondary key
   143  create table t1(a int,b int,key (a), foreign key fk1(b) references t1(a));
   144  -- show tables;
   145  -- show create table t1;
   146  -- insert into t1 values (1,1);
   147  -- insert into t1 values (2,1);
   148  -- insert into t1 values (3,2);
   149  --
   150  -- --error. no number 4 in column a
   151  -- insert into t1 values (5,4);
   152  --
   153  -- --no error. same as mysql
   154  -- insert into t1 values (4,NULL);
   155  --
   156  -- --no error. number 4 is in column a
   157  -- insert into t1 values (5,4);
   158  --
   159  -- --error. number 4 is referred
   160  -- delete from t1 where a= 4;
   161  --
   162  -- --no error . delete the row a = 5.
   163  -- delete from t1 where a= 5;
   164  --
   165  -- --no error. number 4 is not referred
   166  -- delete from t1 where a= 4;
   167  --
   168  -- --no error. number 4 is not referred
   169  -- insert into t1 values (4,4);
   170  --
   171  -- select * from t1;
   172  --
   173  -- --error. number 4 is referred by the b in same row
   174  -- delete from t1 where a = 4;
   175  --
   176  -- --no error
   177  -- update t1 set b = NULL where a= 4;
   178  --
   179  -- --no error. number 4 is not referred.
   180  -- delete from t1 where a = 4;
   181  --
   182  -- --no error in mysql
   183  -- --error in mo. internal error: unexpected input batch for column expression
   184  -- update t1 set a = NULL where a = 3;
   185  --
   186  -- --no error
   187  -- insert into t1 values (NULL,NULL);
   188  --
   189  -- --error
   190  -- insert into t1 values (NULL,3);
   191  --
   192  -- --no error
   193  -- insert into t1 values (NULL,2);
   194  
   195  drop table if exists t1;
   196  
   197  drop database if exists fk_self_refer;