github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/foreign_key/foreign_key.result (about) 1 create table fk_01(col1 varchar(30) not null primary key,col2 int); 2 create table fk_02(col1 int,col2 varchar(25),col3 tinyint,constraint ck foreign key(col2) REFERENCES fk_01(col1)); 3 show create table fk_02; 4 Table Create Table 5 fk_02 CREATE TABLE `fk_02` (\n`col1` INT DEFAULT NULL,\n`col2` VARCHAR(25) DEFAULT NULL,\n`col3` TINYINT DEFAULT NULL,\nCONSTRAINT `ck` FOREIGN KEY (`col2`) REFERENCES `fk_01` (`col1`) ON DELETE RESTRICT ON UPDATE RESTRICT\n) 6 insert into fk_01 values ('90',5983),('100',734),('190',50); 7 insert into fk_02(col2,col3) values ('90',5),('90',4),('100',0),(NULL,80); 8 select * from fk_01; 9 col1 col2 10 100 734 11 190 50 12 90 5983 13 select * from fk_02; 14 col1 col2 col3 15 null 90 5 16 null 90 4 17 null 100 0 18 null null 80 19 insert into fk_02(col2,col3) values ('200',5); 20 internal error: Cannot add or update a child row: a foreign key constraint fails 21 update fk_02 set col2='80' where col2='90'; 22 internal error: Cannot add or update a child row: a foreign key constraint fails 23 update fk_01 set col1=5 where col2=734; 24 internal error: Cannot delete or update a parent row: a foreign key constraint fails 25 delete from fk_01 where col1='90'; 26 internal error: Cannot delete or update a parent row: a foreign key constraint fails 27 delete from fk_01 where col1='190'; 28 select * from fk_01; 29 col1 col2 30 100 734 31 90 5983 32 select * from fk_02; 33 col1 col2 col3 34 null 90 5 35 null 90 4 36 null 100 0 37 null null 80 38 update fk_01 set col2=500 where col2=734; 39 delete from fk_02 where col2='100'; 40 select * from fk_01; 41 col1 col2 42 100 500 43 90 5983 44 select * from fk_02; 45 col1 col2 col3 46 null 90 5 47 null 90 4 48 null null 80 49 drop table fk_02; 50 drop table fk_01; 51 create table fk_01(col1 char(30) primary key,col2 int); 52 create table fk_02(col1 int,col2 char(25),col3 tinyint,constraint ck foreign key(col2) REFERENCES fk_01(col1)); 53 insert into fk_01 values ('90',5983),('100',734),('190',50); 54 insert into fk_02(col2,col3) values ('90',5),('90',4),('100',0),('',3); 55 internal error: Cannot add or update a child row: a foreign key constraint fails 56 insert into fk_02(col2,col3) values ('90',5),('90',4),('100',0); 57 truncate table fk_01; 58 internal error: can not truncate table 'fk_01' referenced by some foreign key constraint 59 select * from fk_01; 60 col1 col2 61 100 734 62 190 50 63 90 5983 64 select * from fk_02; 65 col1 col2 col3 66 null 90 5 67 null 90 4 68 null 100 0 69 drop table fk_02; 70 drop table fk_01; 71 create table fk_01(col1 int auto_increment primary key,col2 varchar(25),col3 tinyint); 72 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); 73 internal error: type of reference column 'col1' is not match for column 'col3' 74 drop table fk_01; 75 create table fk_01(col1 int primary key,col2 varchar(25),col3 tinyint); 76 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); 77 insert into fk_01 values (2,'yellow',20),(10,'apple',50),(11,'opppo',51); 78 insert into fk_02 values(2,'score',1),(2,'student',4),(10,'goods',2); 79 insert into fk_02 values(NULL,NULL,NULL); 80 select * from fk_01; 81 col1 col2 col3 82 2 yellow 20 83 10 apple 50 84 11 opppo 51 85 select * from fk_02; 86 col1 col2 col3 87 2 score 1 88 2 student 4 89 10 goods 2 90 null null null 91 update fk_02 set col1=10 where col3=4; 92 select * from fk_02; 93 col1 col2 col3 94 2 score 1 95 10 student 4 96 10 goods 2 97 null null null 98 update fk_02 set col1=20 where col3=4; 99 internal error: Cannot add or update a child row: a foreign key constraint fails 100 insert into fk_02 values(15,'ssss',10); 101 internal error: Cannot add or update a child row: a foreign key constraint fails 102 delete from fk_01 where col1=11; 103 select * from fk_01; 104 col1 col2 col3 105 2 yellow 20 106 10 apple 50 107 select * from fk_02; 108 col1 col2 col3 109 2 score 1 110 10 student 4 111 10 goods 2 112 null null null 113 update fk_01 set col3=110 where col1=10; 114 delete from fk_01 where col1=2; 115 internal error: Cannot delete or update a parent row: a foreign key constraint fails 116 truncate table fk_01; 117 internal error: can not truncate table 'fk_01' referenced by some foreign key constraint 118 truncate table fk_02; 119 select * from fk_02; 120 col1 col2 col3 121 drop table fk_01; 122 internal error: can not drop table 'fk_01' referenced by some foreign key constraint 123 drop table fk_02; 124 drop table fk_01; 125 create table fk_01(col1 int,col2 varchar(20),col3 tinyint,primary key(col1,col2)); 126 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); 127 insert into fk_01 values (2,'yellow',20),(2,'apple',50),(1,'opppo',51); 128 insert into fk_02 values(2,'apple',1),(2,'apple',4),(1,'opppo',2); 129 insert into fk_02 values(20,'score',1),(12,'apple',4),(1,'yellow',2); 130 internal error: Cannot add or update a child row: a foreign key constraint fails 131 select * from fk_01; 132 col1 col2 col3 133 1 opppo 51 134 2 apple 50 135 2 yellow 20 136 select * from fk_02; 137 col1 col2 col3 138 2 apple 1 139 2 apple 4 140 1 opppo 2 141 update fk_02 set col1=3 where col1=2; 142 internal error: Cannot add or update a child row: a foreign key constraint fails 143 delete from fk_01 where col1=2 and col2='apple'; 144 internal error: Cannot delete or update a parent row: a foreign key constraint fails 145 update fk_01 set col1=3 where col1=2; 146 internal error: Cannot delete or update a parent row: a foreign key constraint fails 147 update fk_01 set col1=1 where col1=2; 148 internal error: Cannot delete or update a parent row: a foreign key constraint fails 149 select * from fk_01; 150 col1 col2 col3 151 1 opppo 51 152 2 apple 50 153 2 yellow 20 154 select * from fk_02; 155 col1 col2 col3 156 2 apple 1 157 2 apple 4 158 1 opppo 2 159 delete from fk_01 where col1=1; 160 internal error: Cannot delete or update a parent row: a foreign key constraint fails 161 delete from fk_02; 162 select * from fk_01; 163 col1 col2 col3 164 1 opppo 51 165 2 apple 50 166 2 yellow 20 167 select * from fk_02; 168 col1 col2 col3 169 drop table fk_02; 170 drop table fk_01; 171 create table fk_01(col1 bigint primary key,col2 varchar(25),col3 tinyint); 172 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); 173 insert into fk_01 values (1,'yellow',20),(2,'apple',50); 174 insert into fk_02 values(1,'score',1),(2,'student',NULL),(3,'goods',2); 175 internal error: Cannot add or update a child row: a foreign key constraint fails 176 insert into fk_02 values(4,'age',3); 177 internal error: Cannot add or update a child row: a foreign key constraint fails 178 insert into fk_02 values(1,'score',1),(2,'student',NULL); 179 select * from fk_01; 180 col1 col2 col3 181 1 yellow 20 182 2 apple 50 183 select * from fk_02; 184 col1 col2 col3 185 1 score 1 186 2 student null 187 update fk_02 set col3=4 where col3=1; 188 select * from fk_01; 189 col1 col2 col3 190 1 yellow 20 191 2 apple 50 192 delete from fk_01 where col1=1; 193 select * from fk_02; 194 col1 col2 col3 195 2 student null 196 update fk_01 set col1=5 where col2='apple'; 197 select * from fk_01; 198 col1 col2 col3 199 5 apple 50 200 select * from fk_02; 201 col1 col2 col3 202 5 student null 203 delete from fk_02 where col1=5; 204 select * from fk_02; 205 col1 col2 col3 206 select * from fk_01; 207 col1 col2 col3 208 5 apple 50 209 drop table fk_02; 210 drop table fk_01; 211 create table fk_01(col1 decimal(38,18),col2 char(25),col3 int,col4 date,primary key(col1,col3,col4)); 212 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); 213 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'); 214 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'); 215 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'); 216 internal error: Cannot add or update a child row: a foreign key constraint fails 217 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'); 218 internal error: Cannot add or update a child row: a foreign key constraint fails 219 select * from fk_01; 220 col1 col2 col3 col4 221 23.100000000000000000 a 20 2022-10-01 222 23.100000000000000000 a 20 2022-10-02 223 23.100000000000000000 a 21 2022-10-01 224 select * from fk_02; 225 col1 col2 col3 col4 226 23.100000000000000000 a 20 2022-10-01 227 23.100000000000000000 a 21 2022-10-01 228 23.100000000000000000 a 20 2022-10-02 229 update fk_02 set col3=19 where col3=20; 230 internal error: Cannot add or update a child row: a foreign key constraint fails 231 delete from fk_01 where col3=20; 232 select * from fk_01; 233 col1 col2 col3 col4 234 23.100000000000000000 a 21 2022-10-01 235 select * from fk_02; 236 col1 col2 col3 col4 237 null a null null 238 23.100000000000000000 a 21 2022-10-01 239 null a null null 240 update fk_01 set col3=19 where col2='a'; 241 select * from fk_01; 242 col1 col2 col3 col4 243 23.100000000000000000 a 19 2022-10-01 244 select * from fk_02; 245 col1 col2 col3 col4 246 null a null null 247 null a null null 248 null a null null 249 drop table fk_02; 250 drop table fk_01; 251 create table fk_01(col1 int primary key auto_increment,col2 varchar(25),col3 varchar(50)); 252 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); 253 insert into fk_01(col2,col3) values ('non-failing','deli'),('safer','prow'),('ultra','strong'); 254 insert into fk_02(col2,col3,col4) values('aa','bb',2),('cc','dd',1),('ee','ff',1); 255 insert into fk_02(col2,col3,col4) values('aa','bb',4); 256 internal error: Cannot add or update a child row: a foreign key constraint fails 257 update fk_02 set col4=5 where col3='ff'; 258 internal error: Cannot add or update a child row: a foreign key constraint fails 259 delete from fk_01 where col1=1; 260 internal error: Cannot delete or update a parent row: a foreign key constraint fails 261 select * from fk_01; 262 col1 col2 col3 263 1 non-failing deli 264 2 safer prow 265 3 ultra strong 266 select * from fk_02; 267 col1 col2 col3 col4 268 1 aa bb 2 269 2 cc dd 1 270 3 ee ff 1 271 delete from fk_02 where col4=1; 272 delete from fk_01 where col1=1; 273 select * from fk_01; 274 col1 col2 col3 275 2 safer prow 276 3 ultra strong 277 select * from fk_02; 278 col1 col2 col3 col4 279 1 aa bb 2 280 update fk_01 set col1=8 where col1=2; 281 internal error: Cannot delete or update a parent row: a foreign key constraint fails 282 select * from fk_01; 283 col1 col2 col3 284 2 safer prow 285 3 ultra strong 286 select * from fk_02; 287 col1 col2 col3 col4 288 1 aa bb 2 289 truncate table fk_01; 290 internal error: can not truncate table 'fk_01' referenced by some foreign key constraint 291 select * from fk_01; 292 col1 col2 col3 293 2 safer prow 294 3 ultra strong 295 drop table fk_01; 296 internal error: can not drop table 'fk_01' referenced by some foreign key constraint 297 drop table fk_02; 298 drop table fk_01; 299 create table fk_01(col1 int primary key auto_increment,col2 varchar(25),col3 varchar(50)); 300 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); 301 insert into fk_01(col2,col3) values ('non-failing','deli'),('safer','prow'),('ultra','strong'),('aaa','bbb'); 302 insert into fk_02(col2,col3,col4) values('aa','bb',2),('cc','dd',1),('ee','ff',1); 303 insert into fk_02(col2,col3,col4) values('aa','bb',3); 304 select * from fk_01; 305 col1 col2 col3 306 1 non-failing deli 307 2 safer prow 308 3 ultra strong 309 4 aaa bbb 310 select * from fk_02; 311 col1 col2 col3 col4 312 1 aa bb 2 313 2 cc dd 1 314 3 ee ff 1 315 4 aa bb 3 316 delete from fk_01 where col1=1; 317 internal error: Cannot delete or update a parent row: a foreign key constraint fails 318 delete from fk_02 where col4=1; 319 select * from fk_02; 320 col1 col2 col3 col4 321 1 aa bb 2 322 4 aa bb 3 323 select * from fk_01; 324 col1 col2 col3 325 1 non-failing deli 326 2 safer prow 327 3 ultra strong 328 4 aaa bbb 329 update fk_01 set col1=8 where col1=2; 330 internal error: Cannot delete or update a parent row: a foreign key constraint fails 331 update fk_02 set col4=5 where col3='ff'; 332 select * from fk_01; 333 col1 col2 col3 334 1 non-failing deli 335 2 safer prow 336 3 ultra strong 337 4 aaa bbb 338 select * from fk_02; 339 col1 col2 col3 col4 340 1 aa bb 2 341 4 aa bb 3 342 update fk_01 set col2='window' where col1=1; 343 delete from fk_01 where col1=3; 344 internal error: Cannot delete or update a parent row: a foreign key constraint fails 345 select * from fk_01; 346 col1 col2 col3 347 1 window deli 348 2 safer prow 349 3 ultra strong 350 4 aaa bbb 351 select * from fk_02; 352 col1 col2 col3 col4 353 1 aa bb 2 354 4 aa bb 3 355 truncate table fk_01; 356 internal error: can not truncate table 'fk_01' referenced by some foreign key constraint 357 insert into fk_01(col2,col3) values ('zhi','gao'),('er','li'); 358 select * from fk_01; 359 col1 col2 col3 360 1 window deli 361 2 safer prow 362 3 ultra strong 363 4 aaa bbb 364 5 zhi gao 365 6 er li 366 select * from fk_02; 367 col1 col2 col3 col4 368 1 aa bb 2 369 4 aa bb 3 370 drop table fk_01; 371 internal error: can not drop table 'fk_01' referenced by some foreign key constraint 372 drop table fk_02; 373 drop table fk_01; 374 create table fk_01(col1 bigint unique key,col2 varchar(25),col3 tinyint); 375 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); 376 insert into fk_01 values (1,'yellow',20),(2,'apple',50); 377 insert into fk_02 values(1,'score',1),(2,'student',NULL),(3,'goods',2); 378 internal error: Cannot add or update a child row: a foreign key constraint fails 379 insert into fk_02 values(4,'age',3); 380 internal error: Cannot add or update a child row: a foreign key constraint fails 381 insert into fk_02 values(1,'score',1),(2,'student',NULL); 382 select * from fk_01; 383 col1 col2 col3 384 1 yellow 20 385 2 apple 50 386 select * from fk_02; 387 col1 col2 col3 388 1 score 1 389 2 student null 390 update fk_02 set col3=4 where col3=1; 391 select * from fk_01; 392 col1 col2 col3 393 1 yellow 20 394 2 apple 50 395 delete from fk_01 where col1=1; 396 select * from fk_02; 397 col1 col2 col3 398 2 student null 399 update fk_01 set col1=5 where col2='apple'; 400 select * from fk_01; 401 col1 col2 col3 402 5 apple 50 403 select * from fk_02; 404 col1 col2 col3 405 5 student null 406 delete from fk_02 where col1=5; 407 select * from fk_02; 408 col1 col2 col3 409 select * from fk_01; 410 col1 col2 col3 411 5 apple 50 412 drop table fk_02; 413 drop table fk_01; 414 create table fk_01(id int primary key auto_increment,title varchar(25)); 415 create table fk_02(id int primary key auto_increment,name varchar(25)); 416 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); 417 insert into fk_01(title) values ('self'),('method'),('console'); 418 insert into fk_02(name) values ('daisy'),('wulan'); 419 insert into fk_03(book_id,author_id) values (1,2),(2,2),(3,1); 420 insert into fk_03(book_id,author_id) values (4,3); 421 internal error: Cannot add or update a child row: a foreign key constraint fails 422 update fk_03 set book_id=6 where book_id=2; 423 internal error: Cannot add or update a child row: a foreign key constraint fails 424 update fk_03 set book_id=3 where book_id=2; 425 select * from fk_03; 426 id book_id author_id 427 1 1 2 428 2 3 2 429 3 3 1 430 update fk_01 set id=5 where title='self'; 431 select * from fk_03; 432 id book_id author_id 433 1 5 2 434 2 3 2 435 3 3 1 436 select * from fk_01; 437 id title 438 2 method 439 3 console 440 5 self 441 delete from fk_02 where id=1; 442 select * from fk_02; 443 id name 444 2 wulan 445 select * from fk_03; 446 id book_id author_id 447 1 5 2 448 2 3 2 449 delete from fk_03; 450 drop table fk_01; 451 internal error: can not drop table 'fk_01' referenced by some foreign key constraint 452 select * from fk_03; 453 id book_id author_id 454 drop table fk_02; 455 internal error: can not drop table 'fk_02' referenced by some foreign key constraint 456 drop table fk_03; 457 drop table fk_01; 458 drop table fk_02; 459 create table fk_01(col1 int,col2 datetime,col3 timestamp,primary key(col1,col2,col3)); 460 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)); 461 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'); 462 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'); 463 insert into fk_02 values (5,'2001-10-19','left','2001-10-09 01:00:09'); 464 internal error: Cannot add or update a child row: a foreign key constraint fails 465 delete from fk_01 where col3='2001-10-09 01:00:09'; 466 internal error: Cannot delete or update a parent row: a foreign key constraint fails 467 select * from fk_01; 468 col1 col2 col3 469 9 2001-10-19 00:00:00 2001-10-09 01:00:09 470 10 2011-12-09 00:00:00 2001-10-09 01:00:09 471 11 2011-12-09 00:00:00 2001-10-09 01:00:09 472 select * from fk_02; 473 col1 col2 col3 col4 474 9 2001-10-19 00:00:00 left 2001-10-09 01:00:09 475 11 2011-12-09 00:00:00 right 2001-10-09 01:00:09 476 drop table fk_02; 477 drop table fk_01; 478 create table fk_an_01(col1 int,col2 varchar(25),col3 tinyint,primary key(col2)); 479 create table fk_an_02(col1 bigint,col2 char(25),constraint ck foreign key(col1) REFERENCES fk_an_01(col2)); 480 internal error: type of reference column 'col2' is not match for column 'col1' 481 create table fk_an_03(col1 int,col2 char(25),constraint ck foreign key(col1) REFERENCES fk_an_05(col1)); 482 no such table foreign_key.fk_an_05 483 create table fk_an_04(col1 bigint,col2 varchar(25),col3 tinyint); 484 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); 485 internal error: reference column 'col1' is not unique constraint(Unique index or Primary Key)