github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/foreign_key/foreign_key.sql (about)

     1  --foreign key is varchar,default ON DELETE/update option
     2  create table fk_01(col1 varchar(30) not null primary key,col2 int);
     3  create table fk_02(col1 int,col2 varchar(25),col3 tinyint,constraint ck foreign key(col2) REFERENCES fk_01(col1));
     4  show create table fk_02;
     5  insert into fk_01 values ('90',5983),('100',734),('190',50);
     6  insert into fk_02(col2,col3) values ('90',5),('90',4),('100',0),(NULL,80);
     7  select * from fk_01;
     8  select * from fk_02;
     9  insert into fk_02(col2,col3) values ('200',5);
    10  update fk_02 set col2='80' where col2='90';
    11  update fk_01 set col1=5 where col2=734;
    12  delete from fk_01 where col1='90';
    13  delete from fk_01 where col1='190';
    14  select * from fk_01;
    15  select * from fk_02;
    16  update fk_01 set col2=500 where col2=734;
    17  delete from fk_02 where col2='100';
    18  select * from fk_01;
    19  select * from fk_02;
    20  drop table fk_02;
    21  drop table fk_01;
    22  
    23  --foreign key is char,default ON DELETE/update option
    24  create table fk_01(col1 char(30) primary key,col2 int);
    25  create table fk_02(col1 int,col2 char(25),col3 tinyint,constraint ck foreign key(col2) REFERENCES fk_01(col1));
    26  insert into fk_01 values ('90',5983),('100',734),('190',50);
    27  insert into fk_02(col2,col3) values ('90',5),('90',4),('100',0),('',3);
    28  insert into fk_02(col2,col3) values ('90',5),('90',4),('100',0);
    29  truncate table fk_01;
    30  select * from fk_01;
    31  select * from fk_02;
    32  drop table fk_02;
    33  drop table fk_01;
    34  
    35  --foreign key is tinyint references int
    36  create table fk_01(col1 int auto_increment primary key,col2 varchar(25),col3 tinyint);
    37  create table fk_02(col1 int,col2 varchar(25),col3 tinyint,primary key (col1),key col2 (col2) ,constraint ck foreign key(col3) REFERENCES fk_01(col1)on delete RESTRICT on update RESTRICT);
    38  drop table fk_01;
    39  
    40  --foreign key is int,on delete/update RESTRICT
    41  create table fk_01(col1 int primary key,col2 varchar(25),col3 tinyint);
    42  create table fk_02(col1 int,col2 varchar(25),col3 tinyint,constraint ck foreign key(col1) REFERENCES fk_01(col1) on delete RESTRICT on update RESTRICT);
    43  insert into fk_01 values (2,'yellow',20),(10,'apple',50),(11,'opppo',51);
    44  insert into fk_02 values(2,'score',1),(2,'student',4),(10,'goods',2);
    45  insert into fk_02 values(NULL,NULL,NULL);
    46  select * from fk_01;
    47  select * from fk_02;
    48  update fk_02 set col1=10 where col3=4;
    49  select * from fk_02;
    50  update fk_02 set col1=20 where col3=4;
    51  insert into fk_02 values(15,'ssss',10);
    52  delete from fk_01 where col1=11;
    53  select * from fk_01;
    54  select * from fk_02;
    55  update fk_01 set col3=110 where col1=10;
    56  delete from fk_01 where col1=2;
    57  truncate table fk_01;
    58  truncate table fk_02;
    59  select * from fk_02;
    60  drop table fk_01;
    61  drop table fk_02;
    62  drop table fk_01;
    63  
    64  --foreign key are two column int varchar,on delete/update RESTRICT
    65  create table fk_01(col1 int,col2 varchar(20),col3 tinyint,primary key(col1,col2));
    66  create table fk_02(col1 int,col2 varchar(25),col3 tinyint,constraint ck foreign key(col1,col2) REFERENCES fk_01(col1,col2) on delete RESTRICT on update RESTRICT);
    67  insert into fk_01 values (2,'yellow',20),(2,'apple',50),(1,'opppo',51);
    68  insert into fk_02 values(2,'apple',1),(2,'apple',4),(1,'opppo',2);
    69  insert into fk_02 values(20,'score',1),(12,'apple',4),(1,'yellow',2);
    70  select * from fk_01;
    71  select * from fk_02;
    72  update fk_02 set col1=3 where col1=2;
    73  delete from fk_01 where col1=2 and col2='apple';
    74  update fk_01 set col1=3 where col1=2;
    75  update fk_01 set col1=1 where col1=2;
    76  select * from fk_01;
    77  select * from fk_02;
    78  delete from fk_01 where col1=1;
    79  delete from fk_02;
    80  select * from fk_01;
    81  select * from fk_02;
    82  drop table fk_02;
    83  drop table fk_01;
    84  
    85  --foreign key is bigint,on delete/update cascade
    86  create table fk_01(col1 bigint primary key,col2 varchar(25),col3 tinyint);
    87  create table fk_02(col1 bigint,col2 varchar(25),col3 tinyint,constraint ck foreign key(col1) REFERENCES fk_01(col1) on delete CASCADE on update CASCADE);
    88  insert into fk_01 values (1,'yellow',20),(2,'apple',50);
    89  insert into fk_02 values(1,'score',1),(2,'student',NULL),(3,'goods',2);
    90  insert into fk_02 values(4,'age',3);
    91  insert into fk_02 values(1,'score',1),(2,'student',NULL);
    92  select * from fk_01;
    93  select * from fk_02;
    94  update fk_02 set col3=4 where col3=1;
    95  select * from fk_01;
    96  delete from fk_01 where col1=1;
    97  select * from fk_02;
    98  update fk_01 set col1=5 where col2='apple';
    99  select * from fk_01;
   100  select * from fk_02;
   101  delete from fk_02 where col1=5;
   102  select * from fk_02;
   103  select * from fk_01;
   104  drop table fk_02;
   105  drop table fk_01;
   106  
   107  --foreign key are more column int,decimal,date,on delete /update SET NULL
   108  create table fk_01(col1 decimal(38,18),col2 char(25),col3 int,col4 date,primary key(col1,col3,col4));
   109  create table fk_02(col1 decimal(38,18),col2 char(25),col3 int,col4 date,constraint ck foreign key(col1,col3,col4) REFERENCES fk_01(col1,col3,col4) on delete SET NULL on update SET NULL);
   110  insert into fk_01 values(23.10,'a',20,'2022-10-01'),(23.10,'a',21,'2022-10-01'),(23.10,'a',20,'2022-10-02');
   111  insert into fk_02 values(23.10,'a',20,'2022-10-01'),(23.10,'a',21,'2022-10-01'),(23.10,'a',20,'2022-10-02');
   112  insert into fk_02 values(23.10,'a',20,'2022-10-01'),(0.1,'b',22,'2022-10-01'),(23.10,'c',30,'2022-10-02');
   113  insert into fk_02 values(0.001,'b',20,'2022-10-01'),(4.5,'a',21,'2022-10-01'),(56,'a',20,'2022-10-02');
   114  select * from fk_01;
   115  select * from fk_02;
   116  update fk_02 set col3=19 where col3=20;
   117  delete from fk_01  where col3=20;
   118  select * from fk_01;
   119  select * from fk_02;
   120  update fk_01 set col3=19 where col2='a';
   121  select * from fk_01;
   122  select * from fk_02;
   123  drop table fk_02;
   124  drop table fk_01;
   125  
   126  --foreign key int,on delete/update NO ACTION
   127  create table fk_01(col1 int primary key auto_increment,col2 varchar(25),col3 varchar(50));
   128  create table fk_02(col1 int primary key auto_increment,col2 varchar(25),col3 char(5) default 'm',col4 int,constraint ck foreign key(col4) REFERENCES fk_01(col1) on delete NO ACTION on update NO ACTION);
   129  insert into fk_01(col2,col3) values ('non-failing','deli'),('safer','prow'),('ultra','strong');
   130  insert into fk_02(col2,col3,col4) values('aa','bb',2),('cc','dd',1),('ee','ff',1);
   131  insert into fk_02(col2,col3,col4) values('aa','bb',4);
   132  update fk_02 set col4=5 where col3='ff';
   133  delete from fk_01 where col1=1;
   134  select * from fk_01;
   135  select * from fk_02;
   136  delete from fk_02 where col4=1;
   137  delete from fk_01 where col1=1;
   138  select * from fk_01;
   139  select * from fk_02;
   140  update fk_01 set col1=8 where col1=2;
   141  select * from fk_01;
   142  select * from fk_02;
   143  truncate table fk_01;
   144  select * from fk_01;
   145  drop table fk_01;
   146  drop table fk_02;
   147  drop table fk_01;
   148  
   149  --foreign key int,on delete/update SET DEFAULT
   150  create table fk_01(col1 int primary key auto_increment,col2 varchar(25),col3 varchar(50));
   151  create table fk_02(col1 int primary key auto_increment,col2 varchar(25),col3 char(5) default 'm',col4 int,constraint ck foreign key(col4) REFERENCES fk_01(col1) on delete SET DEFAULT on update SET DEFAULT);
   152  insert into fk_01(col2,col3) values ('non-failing','deli'),('safer','prow'),('ultra','strong'),('aaa','bbb');
   153  insert into fk_02(col2,col3,col4) values('aa','bb',2),('cc','dd',1),('ee','ff',1);
   154  insert into fk_02(col2,col3,col4) values('aa','bb',3);
   155  select * from fk_01;
   156  select * from fk_02;
   157  delete from fk_01 where col1=1;
   158  delete from fk_02 where col4=1;
   159  select * from fk_02;
   160  select * from fk_01;
   161  update fk_01 set col1=8 where col1=2;
   162  update fk_02 set col4=5 where col3='ff';
   163  select * from fk_01;
   164  select * from fk_02;
   165  update fk_01 set col2='window' where col1=1;
   166  delete from fk_01 where col1=3;
   167  select * from fk_01;
   168  select * from fk_02;
   169  truncate table fk_01;
   170  insert into fk_01(col2,col3) values ('zhi','gao'),('er','li');
   171  select * from fk_01;
   172  select * from fk_02;
   173  drop table fk_01;
   174  drop table fk_02;
   175  drop table fk_01;
   176  
   177  --foreign key references unique index,on delete/update SET DEFAULT
   178  create table fk_01(col1 bigint unique key,col2 varchar(25),col3 tinyint);
   179  create table fk_02(col1 bigint,col2 varchar(25),col3 tinyint,constraint ck foreign key(col1) REFERENCES fk_01(col1) on delete CASCADE on update CASCADE);
   180  insert into fk_01 values (1,'yellow',20),(2,'apple',50);
   181  insert into fk_02 values(1,'score',1),(2,'student',NULL),(3,'goods',2);
   182  insert into fk_02 values(4,'age',3);
   183  insert into fk_02 values(1,'score',1),(2,'student',NULL);
   184  select * from fk_01;
   185  select * from fk_02;
   186  update fk_02 set col3=4 where col3=1;
   187  select * from fk_01;
   188  delete from fk_01 where col1=1;
   189  select * from fk_02;
   190  update fk_01 set col1=5 where col2='apple';
   191  select * from fk_01;
   192  select * from fk_02;
   193  delete from fk_02 where col1=5;
   194  select * from fk_02;
   195  select * from fk_01;
   196  drop table fk_02;
   197  drop table fk_01;
   198  
   199  --more foreign key
   200  create table fk_01(id int primary key auto_increment,title varchar(25));
   201  create table fk_02(id int primary key auto_increment,name varchar(25));
   202  create table fk_03(id int primary key auto_increment,book_id int,foreign key(book_id) REFERENCES fk_01(id) on delete cascade on update cascade,author_id int,foreign key(author_id) REFERENCES fk_02(id) on delete cascade on update cascade);
   203  insert into fk_01(title) values ('self'),('method'),('console');
   204  insert into fk_02(name) values ('daisy'),('wulan');
   205  insert into fk_03(book_id,author_id) values (1,2),(2,2),(3,1);
   206  insert into fk_03(book_id,author_id) values (4,3);
   207  update fk_03 set book_id=6 where book_id=2;
   208  update fk_03 set book_id=3 where book_id=2;
   209  select * from fk_03;
   210  update fk_01 set id=5 where title='self';
   211  select * from fk_03;
   212  select * from fk_01;
   213  delete from fk_02 where id=1;
   214  select * from fk_02;
   215  select * from fk_03;
   216  delete from fk_03;
   217  drop table fk_01;
   218  select * from fk_03;
   219  drop table fk_02;
   220  drop table fk_03;
   221  drop table fk_01;
   222  drop table fk_02;
   223  
   224  --foreign key is datetime,timestamp
   225  create table fk_01(col1 int,col2 datetime,col3 timestamp,primary key(col1,col2,col3));
   226  create table fk_02(col1 int,col2 datetime,col3 char(25),col4 timestamp ,constraint ck foreign key(col1,col2,col4) REFERENCES fk_01(col1,col2,col3));
   227  insert into fk_01 values (9,'2001-10-19','2001-10-09 01:00:09'),(10,'2011-12-09','2001-10-09 01:00:09'),(11,'2011-12-09','2001-10-09 01:00:09');
   228  insert into fk_02 values (9,'2001-10-19','left','2001-10-09 01:00:09'),(11,'2011-12-09','right','2001-10-09 01:00:09');
   229  insert into fk_02 values (5,'2001-10-19','left','2001-10-09 01:00:09');
   230  delete from fk_01 where col3='2001-10-09 01:00:09';
   231  select * from fk_01;
   232  select * from fk_02;
   233  drop table fk_02;
   234  drop table fk_01;
   235  
   236  --Abnormal test
   237  --foreign key is not a related data type
   238  create table fk_an_01(col1 int,col2 varchar(25),col3 tinyint,primary key(col2));
   239  create table fk_an_02(col1 bigint,col2 char(25),constraint ck foreign key(col1) REFERENCES fk_an_01(col2));
   240  --foreign key references table not exists
   241  create table fk_an_03(col1 int,col2 char(25),constraint ck foreign key(col1) REFERENCES fk_an_05(col1));
   242  --references not primary key
   243  create table fk_an_04(col1 bigint,col2 varchar(25),col3 tinyint);
   244  create table fk_an_05(col1 bigint,col2 varchar(25),col3 tinyint,constraint ck foreign key(col1) REFERENCES fk_an_04(col1) on delete CASCADE on update CASCADE);