github.com/matrixorigin/matrixone@v1.2.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 order by col1;
   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);
   245  
   246  create table f1 (fa int primary key);
   247  CREATE TABLE c1 (ca INT, cb INT);
   248  ALTER TABLE c1 ADD CONSTRAINT ffa FOREIGN KEY (ca) REFERENCES f1(fa);
   249  desc c1;
   250  drop table if exists c1;
   251  drop table if exists f1;
   252  create table f1 (a int, b int, c int, d int, e int, primary key(a,b), unique key(c,d));
   253  insert into f1 values (1,1,1,1,1),(2,2,2,2,2),(3,3,3,3,3),(2,1,2,1,1),(3,1,3,1,1),(4,1,4,1,1),(1,2,1,2,1);
   254  create table c1 (a int primary key, f_a int, f_b int, f_c int, f_d int, constraint ck foreign key(f_a) REFERENCES f1(a));
   255  insert into c1 values (1,1,1,1,1);
   256  insert into c1 values (2,5,1,1,1);
   257  drop table c1;
   258  create table c1 (a int primary key, f_a int, f_b int, f_c int, f_d int, constraint ck foreign key(f_a) REFERENCES f1(a) on delete CASCADE on update CASCADE);
   259  insert into c1 values (1,1,1,1,1);
   260  delete from f1 where a=1 and b=2;
   261  select * from c1;
   262  drop table c1;
   263  create table c1 (a int primary key, f_a int, f_b int, f_c int, f_d int, constraint ck foreign key(f_a,f_b) REFERENCES f1(a,b));
   264  drop table c1;
   265  create table c1 (a int primary key, f_a int, f_b int, f_c int, f_d int, constraint ck foreign key(f_a,f_c) REFERENCES f1(a,c)); 
   266  create table c1 (a int primary key, f_a int, f_b int, f_c int, f_d int, constraint ck foreign key(f_c,f_d) REFERENCES f1(c,d));
   267  
   268  create table fk_01(col1 decimal(38,3),col2 char(25),col3 int,col4 date,primary key(col1,col3,col4));
   269  create table fk_02(col1 decimal(38,3),col2 char(25),col3 int,col4 date,constraint ck foreign key(col1) REFERENCES fk_01(col1) on delete SET NULL on update SET NULL);
   270  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');
   271  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');
   272  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');
   273  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');
   274  select * from fk_01;
   275  select * from fk_02;
   276  update fk_02 set col3=19 where col3=20;
   277  select * from fk_02;
   278  delete from fk_01 where col3=20;
   279  select * from fk_01;
   280  select * from fk_02;
   281  
   282  drop table if exists c1;
   283  drop table if exists f1;
   284  create table f1(a int, b int, c int, primary key(a,b));
   285  insert into f1 values (2,1,1), (2,2,2),(2,3,3);
   286  create table c1(a int primary key, c_a int, c_b int, constraint c1_ck foreign key(c_a) REFERENCES f1(a) on delete CASCADE on update CASCADE);
   287  insert into c1 values (1,2,1),(2,2,2),(3,2,3);
   288  update f1 set a=111 where b=1;
   289  select * from f1;
   290  select * from c1;
   291  delete from c1;
   292  insert into c1 values (1,2,1),(2,2,2),(3,2,3);
   293  delete from f1 where b=2;
   294  select * from f1;
   295  select * from c1;
   296  
   297  drop table if exists fk_02;
   298  drop table if exists fk_01;
   299  -- foreign key is one of pk,on delete /update SET NULL
   300  create table fk_01(col1 decimal(38,3),col2 char(25),col3 int,col4 date,primary key(col1,col3,col4));
   301  create table fk_02(col1 decimal(38,3),col2 char(25),col3 int,col4 date,constraint ck foreign key(col1) REFERENCES fk_01(col1) on delete SET NULL on update SET NULL);
   302  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');
   303  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');
   304  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');
   305  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');
   306  select * from fk_01;
   307  select * from fk_02;
   308  update fk_02 set col3=19 where col3=20;
   309  select * from fk_02;
   310  delete from fk_01  where col3=19;
   311  select * from fk_01;
   312  select * from fk_02;
   313  drop table fk_02;
   314  drop table fk_01;
   315  
   316  -- foreign key is one of pk and unique key,on delete /update CASCADE
   317  create table fk_01(col1 decimal(38,3),col2 char(25),col3 int,col4 date,primary key(col1,col2), unique key(col3));
   318  create table fk_02(col1 decimal(38,3),col2 char(25),col3 int,col4 date,constraint ck foreign key(col1,col3) REFERENCES fk_01(col1,col3) on delete CASCADE on update CASCADE);
   319  drop table fk_01;
   320  
   321  -- foreign key is one of unique key,on delete /update CASCADE
   322  create table fk_01(col1 decimal(38,3),col2 char(25),col3 int,col4 date, unique key(col1,col2));
   323  create table fk_02(col1 decimal(38,3),col2 char(25),col3 int,col4 date,constraint ck foreign key(col1) REFERENCES fk_01(col1) on delete CASCADE on update CASCADE);
   324  insert into fk_01 values(8.9,'a',20,'2022-10-01'),(6.0,'a',21,'2022-10-01'),(4.3,'c',20,'2022-10-02');
   325  insert into fk_02 values(8.9,'a',20,'2022-10-01'),(8.9,'a',21,'2022-10-01'),(6.0,'a',20,'2022-10-02');
   326  insert into fk_02 values(8.9,'c',20,'2022-10-01'),(null,'a',21,'2022-10-01');
   327  insert into fk_02 values(3.5,'e',20,'2022-10-01'),(8.9,'a',21,'2022-10-01');
   328  select * from fk_01;
   329  select * from fk_02;
   330  update fk_02 set col1=6.0 where col3=21;
   331  select * from fk_02;
   332  update fk_01 set col2='d' where col1=6.0;
   333  select * from fk_01;
   334  select * from fk_02;
   335  
   336  set autocommit=0;
   337  create table t1(a int primary key);
   338  insert into t1 values (1);
   339  create table t2(id int primary key, a int, CONSTRAINT `t1_a` FOREIGN KEY (`a`) REFERENCES `t1` (`a`) ON DELETE CASCADE ON UPDATE RESTRICT);
   340  insert into t2 values (1,1);
   341  commit;
   342  drop table if exists t2;
   343  drop table if exists t1;
   344  drop table if exists c1;
   345  drop table if exists fk_02;
   346  drop table if exists f1;
   347  drop table if exists fk_01;
   348  set autocommit=1;