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

     1  drop database if exists fk_self_refer4;
     2  create database fk_self_refer4;
     3  use fk_self_refer4;
     4  
     5  drop table if exists t1;
     6  --alter table add fk
     7  create table t1(a int primary key,b int);
     8  show tables;
     9  show create table t1;
    10  insert into t1 values (1,2),(3,4),(5,6);
    11  
    12  --error
    13  alter table t1 add constraint fk1 foreign key (b) references t1(a);
    14  
    15  delete from t1;
    16  insert into t1 values (1,1),(2,3),(3,2);
    17  
    18  --no error
    19  alter table t1 add constraint fk1 foreign key (b) references t1(a);
    20  
    21  --no error. uuid for constraint name
    22  alter table t1 add foreign key (b) references t1(a);
    23  
    24  --should be error. duplicate foreign key
    25  alter table t1 add constraint fk1 foreign key (b) references t1(a);
    26  
    27  insert into t1 values (4,4),(6,5),(5,6);
    28  
    29  --error. violate foreign key
    30  insert into t1 values (7,8);
    31  
    32  --syntax error
    33  --alter table t1 drop constraint fk1;
    34  
    35  --no error
    36  alter table t1 drop foreign key fk1;
    37  
    38  --error. violate foreign key
    39  insert into t1 values (7,8);
    40  
    41  drop table if exists t1;
    42  create table t2(a int);
    43  insert into t2 values (1),(2),(3);
    44  
    45  --no error
    46  alter table t2 add constraint fk1 foreign key (a) references t1(a);
    47  
    48  --error. duplicate fk1
    49  alter table t2 add constraint fk1 foreign key (a) references t1(a);
    50  
    51  --no error
    52  show create table t2;
    53  
    54  --error. violate foreign key
    55  insert into t2 values (7);
    56  
    57  --no error
    58  insert into t2 values (6);
    59  
    60  --no error
    61  alter table t2 drop foreign key fk1;
    62  
    63  show create table t2;
    64  
    65  --no error
    66  insert into t2 values (7);
    67  
    68  --error. 7 is not in t1
    69  alter table t2 add constraint fk1 foreign key (a) references t1(a);
    70  
    71  --error. column self refer
    72  alter table t2 add constraint fk1 foreign key (a) references t2(a);
    73  
    74  delete from t2 where a = 7;
    75  
    76  --no error
    77  alter table t2 add constraint fk1 foreign key (a) references t1(a);
    78  
    79  --error
    80  update t2 set a = 7 where a = 6;
    81  
    82  select * from t1;
    83  
    84  --error. delete row (6,5)
    85  delete from t1 where a = 6;
    86  
    87  update t1 set b = NULL where a = 5;
    88  
    89  select * from t1;
    90  
    91  update t2 set a = NULL where a = 6;
    92  
    93  select * from t2;
    94  
    95  --no error. delete row (6,5)
    96  delete from t1 where a = 6;
    97  
    98  select * from t1;
    99  
   100  --error. t1 referred by the t2
   101  drop table t1;
   102  
   103  --no error
   104  drop table t2;
   105  
   106  --no error. t1 has only self referred now.
   107  drop table t1;
   108  
   109  create table t1(a int primary key ,b int);
   110  
   111  alter table t1 add constraint `fk1` foreign key (b) references t1(a);
   112  alter table t1 add constraint `fk2` foreign key (b) references t1(a);
   113  alter table t1 add constraint `fk3` foreign key (b) references t1(a);
   114  alter table t1 add constraint `fk4` foreign key (b) references t1(a);
   115  alter table t1 add constraint `fk5` foreign key (b) references t1(a);
   116  
   117  show create table t1;
   118  
   119  
   120  -- no error
   121  insert into t1 values (1,4),(2,3),(3,2),(4,1),(5,5);
   122  
   123  --error
   124  delete from t1 where a = 4;
   125  
   126  --error
   127  delete from t1 where a = 5;
   128  
   129  
   130  alter table t1 drop foreign key fk1;
   131  alter table t1 drop foreign key fk2;
   132  alter table t1 drop foreign key fk3;
   133  alter table t1 drop foreign key fk4;
   134  
   135  --error
   136  delete from t1 where a = 4;
   137  
   138  --error
   139  delete from t1 where a = 5;
   140  
   141  alter table t1 drop foreign key fk5;
   142  
   143  --no error
   144  delete from t1 where a = 4;
   145  
   146  --no error
   147  delete from t1 where a = 5;
   148  
   149  --no error
   150  delete from t1 where a = 1;
   151  
   152  alter table t1 add constraint `fk1` foreign key (b) references t1(a);
   153  
   154  --error fk2 does not exist
   155  alter table t1 drop foreign key fk1, drop foreign key fk2, drop foreign key fk1;
   156  
   157  --error duplicate fk1
   158  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);
   159  
   160  --no error
   161  alter table t1 drop foreign key fk1, drop foreign key fk1, drop foreign key fk1;
   162  
   163  --error fk1 does not exist
   164  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);
   165  
   166  --error. fk1 duplicate in new add constraint
   167  alter table t1 add constraint fk1 foreign key (b) references t1(a), add constraint fk1 foreign key (b) references t1(a);
   168  
   169  --no error
   170  alter table t1 add constraint `fk1` foreign key (b) references t1(a);
   171  
   172  --no error
   173  alter table t1 drop constraint fk1;
   174  
   175  drop database if exists fk_self_refer4;