github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/exec/execbuilder/testdata/cascade (about) 1 # LogicTest: local 2 3 # The tests in this file target the legacy FK paths. 4 statement ok 5 SET optimizer_foreign_keys = false 6 7 subtest DeleteCascade_Basic 8 ### Basic Delete Cascade 9 # a 10 # / \ 11 # b1 b2 12 # / \ \ 13 # c1 c2 c3 14 15 statement ok 16 CREATE TABLE a ( 17 id STRING PRIMARY KEY 18 ); 19 20 statement ok 21 CREATE TABLE b1 ( 22 id STRING PRIMARY KEY 23 ,delete_cascade STRING NOT NULL REFERENCES a ON DELETE CASCADE 24 ); 25 26 statement ok 27 CREATE TABLE b2 ( 28 id STRING PRIMARY KEY 29 ,delete_cascade STRING NOT NULL REFERENCES a ON DELETE CASCADE 30 ); 31 32 statement ok 33 CREATE TABLE c1 ( 34 id STRING PRIMARY KEY 35 ,delete_cascade STRING NOT NULL REFERENCES b1 ON DELETE CASCADE 36 ); 37 38 statement ok 39 CREATE TABLE c2 ( 40 id STRING PRIMARY KEY 41 ,delete_cascade STRING NOT NULL REFERENCES b1 ON DELETE CASCADE 42 ); 43 44 statement ok 45 CREATE TABLE c3 ( 46 id STRING PRIMARY KEY REFERENCES b2 ON DELETE CASCADE 47 ); 48 49 statement ok 50 INSERT INTO a VALUES ('a-pk1'); 51 INSERT INTO b1 VALUES ('b1-pk1', 'a-pk1'), ('b1-pk2', 'a-pk1'); 52 INSERT INTO b2 VALUES ('b2-pk1', 'a-pk1'), ('b2-pk2', 'a-pk1'); 53 INSERT INTO c1 VALUES 54 ('c1-pk1-b1-pk1', 'b1-pk1') 55 ,('c1-pk2-b1-pk1', 'b1-pk1') 56 ,('c1-pk3-b1-pk2', 'b1-pk2') 57 ,('c1-pk4-b1-pk2', 'b1-pk2') 58 ; 59 INSERT INTO c2 VALUES 60 ('c2-pk1-b1-pk1', 'b1-pk1') 61 ,('c2-pk2-b1-pk1', 'b1-pk1') 62 ,('c2-pk3-b1-pk2', 'b1-pk2') 63 ,('c2-pk4-b1-pk2', 'b1-pk2') 64 ; 65 INSERT INTO c3 VALUES ('b2-pk1'), ('b2-pk2'); 66 67 statement ok 68 SET tracing = on,kv,results; DELETE FROM a WHERE id = 'a-pk1'; SET tracing = off 69 70 query I 71 SELECT count(*) FROM [SHOW KV TRACE FOR SESSION] WHERE message LIKE 'cascading %'; 72 ---- 73 5 74 75 # Clean up after the test. 76 statement ok 77 DROP TABLE c3, c2, c1, b2, b1, a; 78 79 subtest UpdateCascade_Basic 80 ### Basic Update Cascade 81 # a 82 # / \ 83 # b1 b2 84 # / \ \ 85 # c1 c2 c3 86 87 statement ok 88 CREATE TABLE a ( 89 id STRING PRIMARY KEY 90 ); 91 92 statement ok 93 CREATE TABLE b1 ( 94 id STRING PRIMARY KEY 95 ,update_cascade STRING NOT NULL UNIQUE REFERENCES a ON UPDATE CASCADE 96 ); 97 98 statement ok 99 CREATE TABLE b2 ( 100 id STRING PRIMARY KEY 101 ,update_cascade STRING NOT NULL UNIQUE REFERENCES a ON UPDATE CASCADE 102 ); 103 104 statement ok 105 CREATE TABLE c1 ( 106 id STRING PRIMARY KEY 107 ,update_cascade STRING NOT NULL REFERENCES b1 (update_cascade) ON UPDATE CASCADE 108 ); 109 110 statement ok 111 CREATE TABLE c2 ( 112 id STRING PRIMARY KEY 113 ,update_cascade STRING NOT NULL REFERENCES b1 (update_cascade) ON UPDATE CASCADE 114 ); 115 116 statement ok 117 CREATE TABLE c3 ( 118 id STRING PRIMARY KEY REFERENCES b2(update_cascade) ON UPDATE CASCADE 119 ); 120 121 statement ok 122 INSERT INTO a VALUES ('original'); 123 INSERT INTO b1 VALUES ('b1-pk1', 'original'); 124 INSERT INTO b2 VALUES ('b2-pk1', 'original'); 125 INSERT INTO c1 VALUES 126 ('c1-pk1', 'original') 127 ,('c1-pk2', 'original') 128 ,('c1-pk3', 'original') 129 ,('c1-pk4', 'original') 130 ; 131 INSERT INTO c2 VALUES 132 ('c2-pk1', 'original') 133 ,('c2-pk2', 'original') 134 ,('c2-pk3', 'original') 135 ,('c2-pk4', 'original') 136 ; 137 INSERT INTO c3 VALUES ('original'); 138 139 # ON UPDATE CASCADE 140 statement ok 141 UPDATE a SET id = 'updated' WHERE id = 'original'; 142 143 statement ok 144 SET tracing = on,kv,results; UPDATE a SET id = 'updated2' WHERE id = 'updated'; SET tracing = off 145 146 query I 147 SELECT count(*) FROM [SHOW KV TRACE FOR SESSION] WHERE message LIKE 'cascading %'; 148 ---- 149 5 150 151 # Clean up after the test. 152 statement ok 153 DROP TABLE c3, c2, c1, b2, b1, a; 154 155 subtest DeleteSetNull_Basic1 156 ### Basic Delete Set Null 157 # a 158 # // \\ 159 # / | | \ 160 # b1 b2 b3 b4 161 162 statement ok 163 CREATE TABLE a ( 164 id STRING PRIMARY KEY 165 ); 166 CREATE TABLE b1 ( 167 id STRING PRIMARY KEY 168 ,delete_set_null STRING REFERENCES a ON DELETE SET NULL 169 ); 170 CREATE TABLE b2 ( 171 id STRING PRIMARY KEY 172 ,delete_set_null STRING REFERENCES a ON DELETE SET NULL 173 ); 174 CREATE TABLE b3 ( 175 id STRING PRIMARY KEY 176 ,delete_set_null STRING REFERENCES a ON DELETE SET NULL 177 ); 178 CREATE TABLE b4 ( 179 id STRING PRIMARY KEY 180 ,delete_set_null STRING REFERENCES a ON DELETE SET NULL 181 ); 182 183 statement ok 184 INSERT INTO a VALUES ('delete_me'), ('untouched'); 185 INSERT INTO b1 VALUES ('b1-pk1', 'untouched'), ('b1-pk2', 'untouched'); 186 INSERT INTO b2 VALUES ('b2-pk1', 'untouched'), ('b2-pk2', 'delete_me'); 187 INSERT INTO b3 VALUES ('b3-pk1', 'delete_me'), ('b3-pk2', 'untouched'); 188 INSERT INTO b4 VALUES ('b4-pk1', 'delete_me'), ('b4-pk2', 'delete_me'); 189 190 # Ensure that show trace adds a cascade message for each of the tables that is 191 # cascaded into. 192 statement ok 193 SET tracing = on,kv,results; DELETE FROM a WHERE id = 'delete_me'; SET tracing = off 194 195 query I 196 SELECT count(*) FROM [SHOW KV TRACE FOR SESSION] WHERE message LIKE 'cascading %'; 197 ---- 198 4 199 200 # Clean up after the test. 201 statement ok 202 DROP TABLE b4, b3, b2, b1, a; 203 204 subtest DeleteSetNull_Basic2 205 ### Basic Delete Set Null 206 # a 207 # / \ 208 # b1 b2 209 # / \ \ 210 # c1 c2 c3 211 212 statement ok 213 CREATE TABLE a ( 214 id STRING PRIMARY KEY 215 ); 216 CREATE TABLE b1 ( 217 id STRING PRIMARY KEY 218 ,delete_cascade STRING NOT NULL REFERENCES a ON DELETE CASCADE 219 ); 220 CREATE TABLE b2 ( 221 id STRING PRIMARY KEY 222 ,delete_cascade STRING NOT NULL REFERENCES a ON DELETE CASCADE 223 ); 224 CREATE TABLE c1 ( 225 id STRING PRIMARY KEY 226 ,delete_set_null STRING REFERENCES b1 ON DELETE SET NULL 227 ); 228 CREATE TABLE c2 ( 229 id STRING PRIMARY KEY 230 ,delete_set_null STRING REFERENCES b1 ON DELETE SET NULL 231 ); 232 CREATE TABLE c3 ( 233 id STRING PRIMARY KEY 234 ,delete_set_null STRING REFERENCES b2 ON DELETE SET NULL 235 ); 236 237 statement ok 238 INSERT INTO a VALUES ('a-pk1'); 239 INSERT INTO b1 VALUES ('b1-pk1', 'a-pk1'), ('b1-pk2', 'a-pk1'); 240 INSERT INTO b2 VALUES ('b2-pk1', 'a-pk1'), ('b2-pk2', 'a-pk1'); 241 INSERT INTO c1 VALUES 242 ('c1-pk1-b1-pk1', 'b1-pk1') 243 ,('c1-pk2-b1-pk1', 'b1-pk1') 244 ,('c1-pk3-b1-pk2', 'b1-pk2') 245 ,('c1-pk4-b1-pk2', 'b1-pk2') 246 ; 247 INSERT INTO c2 VALUES 248 ('c2-pk1-b1-pk1', 'b1-pk1') 249 ,('c2-pk2-b1-pk1', 'b1-pk1') 250 ,('c2-pk3-b1-pk2', 'b1-pk2') 251 ,('c2-pk4-b1-pk2', 'b1-pk2') 252 ; 253 INSERT INTO c3 VALUES 254 ('c3-pk1-b2-pk1', 'b2-pk1') 255 ,('c3-pk2-b2-pk1', 'b2-pk1') 256 ,('c3-pk3-b2-pk2', 'b2-pk2') 257 ,('c3-pk4-b2-pk2', 'b2-pk2') 258 ; 259 260 statement ok 261 SET tracing = on,kv,results; DELETE FROM a WHERE id = 'a-pk1'; SET tracing = off 262 263 query I 264 SELECT count(*) FROM [SHOW KV TRACE FOR SESSION] WHERE message LIKE 'cascading %'; 265 ---- 266 5 267 268 # Clean up after the test. 269 statement ok 270 DROP TABLE c3, c2, c1, b2, b1, a; 271 272 subtest UpdateSetNull_Basic1 273 ### Basic Update Set Null 274 # a 275 # // \\ 276 # / | | \ 277 # b1 b2 b3 b4 278 279 statement ok 280 CREATE TABLE a ( 281 id STRING PRIMARY KEY 282 ); 283 CREATE TABLE b1 ( 284 id STRING PRIMARY KEY 285 ,update_set_null STRING REFERENCES a ON UPDATE SET NULL 286 ); 287 CREATE TABLE b2 ( 288 id STRING PRIMARY KEY 289 ,update_set_null STRING REFERENCES a ON UPDATE SET NULL 290 ); 291 CREATE TABLE b3 ( 292 id STRING PRIMARY KEY 293 ,update_set_null STRING REFERENCES a ON UPDATE SET NULL 294 ); 295 CREATE TABLE b4 ( 296 id STRING PRIMARY KEY 297 ,update_set_null STRING REFERENCES a ON UPDATE SET NULL 298 ); 299 300 statement ok 301 INSERT INTO a VALUES ('original'), ('untouched'); 302 INSERT INTO b1 VALUES ('b1-pk1', 'untouched'), ('b1-pk2', 'untouched'); 303 INSERT INTO b2 VALUES ('b2-pk1', 'untouched'), ('b2-pk2', 'original'); 304 INSERT INTO b3 VALUES ('b3-pk1', 'original'), ('b3-pk2', 'untouched'); 305 INSERT INTO b3 VALUES ('b4-pk1', 'original'), ('b4-pk2', 'original'); 306 307 # Ensure that show trace adds a cascade message for each of the tables that is 308 # cascaded into. 309 statement ok 310 SET tracing = on,kv,results; UPDATE a SET id = 'updated' WHERE id = 'original'; SET tracing = off 311 312 query I 313 SELECT count(*) FROM [SHOW KV TRACE FOR SESSION] WHERE message LIKE 'cascading %'; 314 ---- 315 4 316 317 # Clean up after the test. 318 statement ok 319 DROP TABLE b4, b3, b2, b1, a; 320 321 subtest UpdateSetNull_Basic2 322 ### Basic Update Set Null 323 # a 324 # / \ 325 # b1 b2 326 # / \ \ 327 # c1 c2 c3 328 329 statement ok 330 CREATE TABLE a ( 331 id STRING PRIMARY KEY 332 ); 333 CREATE TABLE b1 ( 334 id STRING PRIMARY KEY 335 ,update_cascade STRING UNIQUE NOT NULL REFERENCES a ON UPDATE CASCADE 336 ); 337 CREATE TABLE b2 ( 338 id STRING PRIMARY KEY 339 ,update_cascade STRING UNIQUE NOT NULL REFERENCES a ON UPDATE CASCADE 340 ); 341 CREATE TABLE c1 ( 342 id STRING PRIMARY KEY 343 ,update_set_null STRING REFERENCES b1(update_cascade) ON UPDATE SET NULL 344 ); 345 CREATE TABLE c2 ( 346 id STRING PRIMARY KEY 347 ,update_set_null STRING REFERENCES b1(update_cascade) ON UPDATE SET NULL 348 ); 349 CREATE TABLE c3 ( 350 id STRING PRIMARY KEY 351 ,update_set_null STRING REFERENCES b2(update_cascade) ON UPDATE SET NULL 352 ); 353 354 statement ok 355 INSERT INTO a VALUES ('original'), ('untouched'); 356 INSERT INTO b1 VALUES ('b1-pk1', 'original'), ('b1-pk2', 'untouched'); 357 INSERT INTO b2 VALUES ('b2-pk1', 'original'), ('b2-pk2', 'untouched'); 358 INSERT INTO c1 VALUES 359 ('c1-pk1-b1-pk1', 'original') 360 ,('c1-pk2-b1-pk1', 'original') 361 ,('c1-pk3-b1-pk2', 'untouched') 362 ,('c1-pk4-b1-pk2', 'untouched') 363 ; 364 INSERT INTO c2 VALUES 365 ('c2-pk1-b1-pk1', 'original') 366 ,('c2-pk2-b1-pk1', 'original') 367 ,('c2-pk3-b1-pk2', 'untouched') 368 ,('c2-pk4-b1-pk2', 'untouched') 369 ; 370 INSERT INTO c3 VALUES 371 ('c3-pk1-b2-pk1', 'original') 372 ,('c3-pk2-b2-pk1', 'original') 373 ,('c3-pk3-b2-pk2', 'untouched') 374 ,('c3-pk4-b2-pk2', 'untouched') 375 ; 376 377 # Ensure that show trace adds a cascade message for each of the tables that is 378 # cascaded into. 379 statement ok 380 SET tracing = on,kv,results; UPDATE a SET id = 'updated' WHERE id = 'original'; SET tracing = off 381 382 query I 383 SELECT count(*) FROM [SHOW KV TRACE FOR SESSION] WHERE message LIKE 'cascading %'; 384 ---- 385 5 386 387 # Clean up after the test. 388 statement ok 389 DROP TABLE c3, c2, c1, b2, b1, a; 390 391 ############## 392 393 subtest DeleteSetDefault_Basic1 394 ### Basic Delete Set Default 395 # a 396 # // \\ 397 # / | | \ 398 # b1 b2 b3 b4 399 400 statement ok 401 CREATE TABLE a ( 402 id STRING PRIMARY KEY 403 ); 404 CREATE TABLE b1 ( 405 id STRING PRIMARY KEY 406 ,delete_set_default STRING DEFAULT 'b1-default' REFERENCES a ON DELETE SET DEFAULT 407 ); 408 CREATE TABLE b2 ( 409 id STRING PRIMARY KEY 410 ,delete_set_default STRING DEFAULT 'b2-default' REFERENCES a ON DELETE SET DEFAULT 411 ); 412 CREATE TABLE b3 ( 413 id STRING PRIMARY KEY 414 ,delete_set_default STRING DEFAULT 'b3-default' REFERENCES a ON DELETE SET DEFAULT 415 ); 416 CREATE TABLE b4 ( 417 id STRING PRIMARY KEY 418 ,delete_set_default STRING DEFAULT 'b4-default' REFERENCES a ON DELETE SET DEFAULT 419 ); 420 421 statement ok 422 INSERT INTO a VALUES ('delete_me'), ('untouched'), ('b2-default'), ('b3-default'), ('b4-default'); 423 INSERT INTO b1 VALUES ('b1-pk1', 'untouched'), ('b1-pk2', 'untouched'); 424 INSERT INTO b2 VALUES ('b2-pk1', 'untouched'), ('b2-pk2', 'delete_me'); 425 INSERT INTO b3 VALUES ('b3-pk1', 'delete_me'), ('b3-pk2', 'untouched'); 426 INSERT INTO b4 VALUES ('b4-pk1', 'delete_me'), ('b4-pk2', 'delete_me'); 427 428 # Ensure that show trace adds a cascade message for each of the tables that is 429 # cascaded into. 430 statement ok 431 SET tracing = on,kv,results; DELETE FROM a WHERE id = 'delete_me'; SET tracing = off 432 433 query I 434 SELECT count(*) FROM [SHOW KV TRACE FOR SESSION] WHERE message LIKE 'cascading %'; 435 ---- 436 4 437 438 # Clean up after the test. 439 statement ok 440 DROP TABLE b4, b3, b2, b1, a; 441 442 subtest DeleteSetDefault_Basic2 443 ### Basic Delete Set Null via an ON DELETE CASCADE 444 # a 445 # / \ 446 # b1 b2 447 # / \ \ 448 # c1 c2 c3 449 450 statement ok 451 CREATE TABLE a ( 452 id STRING PRIMARY KEY 453 ); 454 CREATE TABLE b1 ( 455 id STRING PRIMARY KEY 456 ,delete_cascade STRING NOT NULL REFERENCES a ON DELETE CASCADE 457 ); 458 CREATE TABLE b2 ( 459 id STRING PRIMARY KEY 460 ,delete_cascade STRING NOT NULL REFERENCES a ON DELETE CASCADE 461 ); 462 CREATE TABLE c1 ( 463 id STRING PRIMARY KEY 464 ,delete_set_default STRING DEFAULT 'b1-default' REFERENCES b1 ON DELETE SET DEFAULT 465 ); 466 CREATE TABLE c2 ( 467 id STRING PRIMARY KEY 468 ,delete_set_default STRING DEFAULT 'b1-default' REFERENCES b1 ON DELETE SET DEFAULT 469 ); 470 CREATE TABLE c3 ( 471 id STRING PRIMARY KEY 472 ,delete_set_default STRING DEFAULT 'b2-default' REFERENCES b2 ON DELETE SET DEFAULT 473 ); 474 475 statement ok 476 INSERT INTO a VALUES ('a-pk1'), ('a-default'); 477 INSERT INTO b1 VALUES ('b1-pk1', 'a-pk1'), ('b1-pk2', 'a-pk1'), ('b1-default', 'a-default'); 478 INSERT INTO b2 VALUES ('b2-pk1', 'a-pk1'), ('b2-pk2', 'a-pk1'), ('b2-default', 'a-default'); 479 INSERT INTO c1 VALUES 480 ('c1-pk1-b1-pk1', 'b1-pk1') 481 ,('c1-pk2-b1-pk1', 'b1-pk1') 482 ,('c1-pk3-b1-pk2', 'b1-pk2') 483 ,('c1-pk4-b1-pk2', 'b1-pk2') 484 ; 485 INSERT INTO c2 VALUES 486 ('c2-pk1-b1-pk1', 'b1-pk1') 487 ,('c2-pk2-b1-pk1', 'b1-pk1') 488 ,('c2-pk3-b1-pk2', 'b1-pk2') 489 ,('c2-pk4-b1-pk2', 'b1-pk2') 490 ; 491 INSERT INTO c3 VALUES 492 ('c3-pk1-b2-pk1', 'b2-pk1') 493 ,('c3-pk2-b2-pk1', 'b2-pk1') 494 ,('c3-pk3-b2-pk2', 'b2-pk2') 495 ,('c3-pk4-b2-pk2', 'b2-pk2') 496 ; 497 498 statement ok 499 SET tracing = on,kv,results; DELETE FROM a WHERE id = 'a-pk1'; SET tracing = off 500 501 query I 502 SELECT count(*) FROM [SHOW KV TRACE FOR SESSION] WHERE message LIKE 'cascading %'; 503 ---- 504 5 505 506 # Clean up after the test. 507 statement ok 508 DROP TABLE c3, c2, c1, b2, b1, a; 509 510 subtest UpdateSetDefault_Basic1 511 ### Basic Update Set Default 512 # a 513 # // \\ 514 # / | | \ 515 # b1 b2 b3 b4 516 517 statement ok 518 CREATE TABLE a ( 519 id STRING PRIMARY KEY 520 ); 521 CREATE TABLE b1 ( 522 id STRING PRIMARY KEY 523 ,update_set_null STRING DEFAULT 'b1-default' REFERENCES a ON UPDATE SET DEFAULT 524 ); 525 CREATE TABLE b2 ( 526 id STRING PRIMARY KEY 527 ,update_set_null STRING DEFAULT 'b2-default' REFERENCES a ON UPDATE SET DEFAULT 528 ); 529 CREATE TABLE b3 ( 530 id STRING PRIMARY KEY 531 ,update_set_null STRING DEFAULT 'b3-default' REFERENCES a ON UPDATE SET DEFAULT 532 ); 533 CREATE TABLE b4 ( 534 id STRING PRIMARY KEY 535 ,update_set_null STRING DEFAULT 'b4-default' REFERENCES a ON UPDATE SET DEFAULT 536 ); 537 538 statement ok 539 INSERT INTO a VALUES ('original'), ('untouched'), ('b1-default'), ('b2-default'), ('b3-default'), ('b4-default'); 540 INSERT INTO b1 VALUES ('b1-pk1', 'untouched'), ('b1-pk2', 'untouched'); 541 INSERT INTO b2 VALUES ('b2-pk1', 'untouched'), ('b2-pk2', 'original'); 542 INSERT INTO b3 VALUES ('b3-pk1', 'original'), ('b3-pk2', 'untouched'); 543 INSERT INTO b3 VALUES ('b4-pk1', 'original'), ('b4-pk2', 'original'); 544 545 # Ensure that show trace adds a cascade message for each of the tables that is 546 # cascaded into. 547 statement ok 548 SET tracing = on,kv,results; UPDATE a SET id = 'updated' WHERE id = 'original'; SET tracing = off 549 550 query I 551 SELECT count(*) FROM [SHOW KV TRACE FOR SESSION] WHERE message LIKE 'cascading %'; 552 ---- 553 4 554 555 # Clean up after the test. 556 statement ok 557 DROP TABLE b4, b3, b2, b1, a; 558 559 subtest UpdateSetDefault_Basic2 560 ### Basic UPDATE SET DEFAULT via an UPDATE CASCADE 561 # a 562 # / \ 563 # b1 b2 564 # / \ \ 565 # c1 c2 c3 566 567 statement ok 568 CREATE TABLE a ( 569 id STRING PRIMARY KEY 570 ); 571 CREATE TABLE b1 ( 572 id STRING PRIMARY KEY 573 ,update_cascade STRING UNIQUE NOT NULL REFERENCES a ON UPDATE CASCADE 574 ); 575 CREATE TABLE b2 ( 576 id STRING PRIMARY KEY 577 ,update_cascade STRING UNIQUE NOT NULL REFERENCES a ON UPDATE CASCADE 578 ); 579 CREATE TABLE c1 ( 580 id STRING PRIMARY KEY 581 ,update_set_null STRING DEFAULT 'b1-default' REFERENCES b1(update_cascade) ON UPDATE SET DEFAULT 582 ); 583 CREATE TABLE c2 ( 584 id STRING PRIMARY KEY 585 ,update_set_null STRING DEFAULT 'b1-default' REFERENCES b1(update_cascade) ON UPDATE SET DEFAULT 586 ); 587 CREATE TABLE c3 ( 588 id STRING PRIMARY KEY 589 ,update_set_null STRING DEFAULT 'b2-default' REFERENCES b2(update_cascade) ON UPDATE SET DEFAULT 590 ); 591 592 statement ok 593 INSERT INTO a VALUES ('original'), ('untouched'), ('b1-default'), ('b2-default'); 594 INSERT INTO b1 VALUES ('b1-pk1', 'original'), ('b1-pk2', 'untouched'), ('b1-default', 'b1-default'); 595 INSERT INTO b2 VALUES ('b2-pk1', 'original'), ('b2-pk2', 'untouched'), ('b2-default', 'b2-default'); 596 INSERT INTO c1 VALUES 597 ('c1-pk1-b1-pk1', 'original') 598 ,('c1-pk2-b1-pk1', 'original') 599 ,('c1-pk3-b1-pk2', 'untouched') 600 ,('c1-pk4-b1-pk2', 'untouched') 601 ; 602 INSERT INTO c2 VALUES 603 ('c2-pk1-b1-pk1', 'original') 604 ,('c2-pk2-b1-pk1', 'original') 605 ,('c2-pk3-b1-pk2', 'untouched') 606 ,('c2-pk4-b1-pk2', 'untouched') 607 ; 608 INSERT INTO c3 VALUES 609 ('c3-pk1-b2-pk1', 'original') 610 ,('c3-pk2-b2-pk1', 'original') 611 ,('c3-pk3-b2-pk2', 'untouched') 612 ,('c3-pk4-b2-pk2', 'untouched') 613 ; 614 615 # Ensure that show trace adds a cascade message for each of the tables that is 616 # cascaded into. 617 statement ok 618 SET tracing = on,kv,results; UPDATE a SET id = 'updated' WHERE id = 'original'; SET tracing = off 619 620 query I 621 SELECT count(*) FROM [SHOW KV TRACE FOR SESSION] WHERE message LIKE 'cascading %'; 622 ---- 623 5 624 625 # Clean up after the test. 626 statement ok 627 DROP TABLE c3, c2, c1, b2, b1, a; 628 629 # Regression for #46094. 630 631 statement ok 632 CREATE TABLE parent (x INT PRIMARY KEY); 633 CREATE TABLE child1 ( 634 id INT PRIMARY KEY, 635 x INT REFERENCES parent (x) ON DELETE CASCADE, 636 FAMILY (id, x) 637 ); 638 CREATE TABLE child2 ( 639 id INT PRIMARY KEY, 640 x INT REFERENCES parent (x) ON DELETE SET NULL, 641 FAMILY (id, x) 642 ); 643 INSERT INTO parent VALUES (1), (2); 644 INSERT INTO child1 VALUES (1, 1), (2, 1); 645 INSERT INTO child2 VALUES (1, 1), (2, 1) 646 647 # Here we ensure that after the cascaded deletes we don't need 648 # to perform additional and unneeded FKScan operations after 649 # cascade deleting or setting null to referencing rows. 650 query T kvtrace(Del,FKScan) 651 DELETE FROM parent WHERE x = 1 652 ---- 653 Del /Table/109/1/1/0 654 Del /Table/110/2/1/1/0 655 Del /Table/110/1/1/0 656 Del /Table/110/2/1/2/0 657 Del /Table/110/1/2/0 658 Del /Table/111/2/1/1/0 659 Del /Table/111/2/1/2/0