github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/cascade_opt (about) 1 # This test times out when run with 3node-tenant. 2 # LogicTest: !3node-tenant 3 4 # The tests in this file target the new optimizer-driven FK paths (with 5 # fall back on the legacy paths for unsupported cases). 6 statement ok 7 SET optimizer_foreign_keys = true 8 9 statement ok 10 SET experimental_optimizer_foreign_key_cascades = true 11 12 # Tests for the experimental opt-driven cascades. 13 subtest OptDriven 14 15 # Single delete cascade. 16 statement ok 17 CREATE TABLE parent (p INT PRIMARY KEY); 18 CREATE TABLE child ( 19 c INT PRIMARY KEY, 20 p INT NOT NULL REFERENCES parent(p) ON DELETE CASCADE 21 ); 22 INSERT INTO parent VALUES (1), (2); 23 INSERT INTO child VALUES (1, 1), (2, 2), (10, 1), (20, 2); 24 25 query II rowsort 26 SELECT * FROM child 27 ---- 28 1 1 29 2 2 30 10 1 31 20 2 32 33 statement ok 34 DELETE FROM parent WHERE p >= 2 35 36 query II rowsort 37 SELECT * FROM child 38 ---- 39 1 1 40 10 1 41 42 statement ok 43 DELETE FROM parent WHERE p <= 2 44 45 query II 46 SELECT * FROM child 47 ---- 48 49 # Delete cascade which itself has a check. 50 statement ok 51 CREATE TABLE grandchild ( 52 g INT PRIMARY KEY, 53 c INT REFERENCES child(c) 54 ); 55 INSERT INTO parent VALUES (1), (2); 56 INSERT INTO child VALUES (10, 1), (11, 1), (20, 2), (21, 2); 57 INSERT INTO grandchild VALUES (100, 10), (101, 10), (110, 11); 58 59 statement ok 60 DELETE FROM parent WHERE p = 2 61 62 statement error delete on table "child" violates foreign key constraint "fk_c_ref_child" on table "grandchild"\nDETAIL: Key \(c\)\=\(1[01]\) is still referenced from table "grandchild" 63 DELETE FROM parent WHERE p = 1 64 65 statement ok 66 DELETE FROM grandchild WHERE c = 10 67 68 statement error delete on table "child" violates foreign key constraint "fk_c_ref_child" on table "grandchild"\nDETAIL: Key \(c\)=\(11\) is still referenced from table "grandchild" 69 DELETE FROM parent WHERE p = 1 70 71 statement ok 72 DELETE FROM grandchild WHERE c = 11 73 74 statement ok 75 DELETE FROM parent WHERE p = 1 76 77 statement ok 78 DROP TABLE grandchild 79 80 # Delete cascade which itself has a cascade. 81 statement ok 82 CREATE TABLE grandchild ( 83 g INT PRIMARY KEY, 84 c INT REFERENCES child(c) ON DELETE CASCADE 85 ); 86 INSERT INTO parent VALUES (1), (2); 87 INSERT INTO child VALUES (10, 1), (11, 1), (20, 2), (21, 2); 88 INSERT INTO grandchild VALUES (100, 10), (101, 10), (110, 11), (200, 20) 89 90 statement ok 91 DELETE FROM parent WHERE p = 1 92 93 query II rowsort 94 SELECT * FROM child 95 ---- 96 20 2 97 21 2 98 99 query II rowsort 100 SELECT * FROM grandchild 101 ---- 102 200 20 103 104 statement ok 105 DELETE FROM parent WHERE p = 2 106 107 query II 108 SELECT * FROM child 109 ---- 110 111 query II 112 SELECT * FROM grandchild 113 ---- 114 115 statement ok 116 DROP TABLE grandchild; 117 DROP TABLE child; 118 DROP TABLE parent 119 120 # Delete cascade with multiple columns and multiple child tables. 121 statement ok 122 CREATE TABLE parent_multi (pa INT, pb INT, pc INT, UNIQUE INDEX (pa,pb,pc)); 123 CREATE TABLE child_multi_1 ( 124 c INT, 125 a INT, 126 b INT, 127 FOREIGN KEY (a,b,c) REFERENCES parent_multi(pa,pb,pc) ON DELETE CASCADE 128 ); 129 CREATE TABLE child_multi_2 ( 130 b INT, 131 c INT, 132 a INT, 133 FOREIGN KEY (a,b,c) REFERENCES parent_multi(pa,pb,pc) ON DELETE CASCADE 134 ) 135 136 statement ok 137 INSERT INTO parent_multi VALUES (1, 10, 100), (2, 20, 200), (3, 30, 300), (NULL, NULL, NULL); 138 INSERT INTO child_multi_1(a,b,c) VALUES (1, 10, 100), (2, 20, 200), (1, 10, 100), (2, 20, 200), (NULL, NULL, NULL); 139 INSERT INTO child_multi_2(a,b,c) VALUES (2, 20, 200), (3, 30, 300) 140 141 query III rowsort 142 SELECT * FROM parent_multi 143 ---- 144 1 10 100 145 2 20 200 146 3 30 300 147 NULL NULL NULL 148 149 query III rowsort 150 SELECT a,b,c FROM child_multi_1 151 ---- 152 1 10 100 153 2 20 200 154 1 10 100 155 2 20 200 156 NULL NULL NULL 157 158 query III rowsort 159 SELECT a,b,c FROM child_multi_2 160 ---- 161 2 20 200 162 3 30 300 163 164 statement ok 165 DELETE FROM parent_multi WHERE pa = 1 166 167 query III rowsort 168 SELECT * FROM parent_multi 169 ---- 170 2 20 200 171 3 30 300 172 NULL NULL NULL 173 174 query III rowsort 175 SELECT a,b,c FROM child_multi_1 176 ---- 177 2 20 200 178 2 20 200 179 NULL NULL NULL 180 181 query III rowsort 182 SELECT a,b,c FROM child_multi_2 183 ---- 184 2 20 200 185 3 30 300 186 187 statement ok 188 DELETE FROM parent_multi WHERE pb = 20 189 190 query III rowsort 191 SELECT * FROM parent_multi 192 ---- 193 3 30 300 194 NULL NULL NULL 195 196 query III rowsort 197 SELECT a,b,c FROM child_multi_1 198 ---- 199 NULL NULL NULL 200 201 query III rowsort 202 SELECT a,b,c FROM child_multi_2 203 ---- 204 3 30 300 205 206 # Deleting NULLs should not cause any changes in a child. 207 statement ok 208 DELETE FROM parent_multi WHERE pa IS NULL 209 210 query III rowsort 211 SELECT * FROM parent_multi 212 ---- 213 3 30 300 214 215 query III rowsort 216 SELECT a,b,c FROM child_multi_1 217 ---- 218 NULL NULL NULL 219 220 query III rowsort 221 SELECT a,b,c FROM child_multi_2 222 ---- 223 3 30 300 224 225 statement ok 226 DROP TABLE child_multi_1; 227 DROP TABLE child_multi_2; 228 DROP TABLE parent_multi 229 230 # Self-referencing cascade. 231 statement ok 232 CREATE TABLE self (a INT PRIMARY KEY, b INT REFERENCES self(a) ON DELETE CASCADE) 233 234 statement ok 235 INSERT INTO self VALUES (1, NULL); 236 INSERT INTO self SELECT x, x-1 FROM generate_series(2, 10) AS g(x) 237 238 statement ok 239 DELETE FROM self WHERE a = 4 240 241 query II rowsort 242 SELECT * FROM self 243 ---- 244 1 NULL 245 2 1 246 3 2 247 248 statement ok 249 DELETE FROM self WHERE a = 1 250 251 query II 252 SELECT * FROM self 253 ---- 254 255 # Test cascade limit setting. 256 statement ok 257 INSERT INTO self VALUES (1, NULL); 258 INSERT INTO self SELECT x, x-1 FROM generate_series(2, 20) AS g(x) 259 260 statement ok 261 SET foreign_key_cascades_limit = 10 262 263 statement error cascades limit \(10\) reached 264 DELETE FROM self WHERE a = 1 265 266 statement ok 267 RESET foreign_key_cascades_limit 268 269 statement ok 270 DROP TABLE self 271 272 subtest AllCascadingActions 273 ### A test of all cascading actions in their most basic form. 274 # A 275 # | 276 # B 277 278 statement ok 279 CREATE TABLE a ( 280 id INT PRIMARY KEY 281 ); 282 283 statement ok 284 CREATE TABLE b ( 285 delete_no_action INT NOT NULL REFERENCES a ON DELETE NO ACTION 286 ,update_no_action INT NOT NULL REFERENCES a ON UPDATE NO ACTION 287 ,delete_restrict INT NOT NULL REFERENCES a ON DELETE RESTRICT 288 ,update_restrict INT NOT NULL REFERENCES a ON UPDATE RESTRICT 289 ,delete_cascade INT NOT NULL REFERENCES a ON DELETE CASCADE 290 ,update_cascade INT NOT NULL REFERENCES a ON UPDATE CASCADE 291 ,delete_null INT REFERENCES a ON DELETE SET NULL 292 ,update_null INT REFERENCES a ON UPDATE SET NULL 293 ,delete_default INT DEFAULT 109 REFERENCES a ON DELETE SET DEFAULT 294 ,update_default INT DEFAULT 110 REFERENCES a ON UPDATE SET DEFAULT 295 ); 296 297 statement ok 298 INSERT INTO a (id) VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (109), (110); 299 INSERT INTO b VALUES (1, 2, 3, 4, 5, 6, 7, 8, 9, 10); 300 301 query IIIIIIIIII 302 SELECT * FROM b; 303 ---- 304 1 2 3 4 5 6 7 8 9 10 305 306 # 1. ON DELETE NO ACTION 307 statement error pq: delete on table "a" violates foreign key constraint "fk_delete_no_action_ref_a" on table "b"\nDETAIL: Key \(id\)=\(1\) is still referenced from table "b"\. 308 DELETE FROM a WHERE id = 1; 309 310 # 2. ON UPDATE NO ACTION 311 statement error pq: update on table "a" violates foreign key constraint "fk_update_no_action_ref_a" on table "b"\nDETAIL: Key \(id\)=\(2\) is still referenced from table "b"\. 312 UPDATE a SET id = 1000 WHERE id = 2; 313 314 # 3. ON DELETE RESTRICT 315 statement error pq: delete on table "a" violates foreign key constraint "fk_delete_restrict_ref_a" on table "b"\nDETAIL: Key \(id\)=\(3\) is still referenced from table "b"\. 316 DELETE FROM a WHERE id = 3; 317 318 # 4. ON UPDATE RESTRICT 319 statement error pq: update on table "a" violates foreign key constraint "fk_update_restrict_ref_a" on table "b"\nDETAIL: Key \(id\)=\(4\) is still referenced from table "b"\. 320 UPDATE a SET id = 1000 WHERE id = 4; 321 322 # 5. ON DELETE CASCADE 323 statement ok 324 DELETE FROM a WHERE id = 5; 325 326 query I 327 SELECT count(*) FROM b; 328 ---- 329 0 330 331 statement ok 332 INSERT INTO a VALUES (5); 333 INSERT INTO b VALUES (1, 2, 3, 4, 5, 6, 7, 8, 9, 10); 334 335 # 6. ON UPDATE CASCADE 336 statement ok 337 UPDATE a SET id = 1006 WHERE id = 6; 338 339 query IIIIIIIIII 340 SELECT * FROM b; 341 ---- 342 1 2 3 4 5 1006 7 8 9 10 343 344 # Also ensure that normal errors are still correctly wrapped even if cascading. 345 statement error pq: duplicate key value \(id\)=\(1\) violates unique constraint "primary" 346 UPDATE a SET id = 1 WHERE id = 1006; 347 348 # 7. ON DELETE SET NULL 349 statement ok 350 DELETE FROM a WHERE id = 7; 351 352 query IIIIIIIIII 353 SELECT * FROM b; 354 ---- 355 1 2 3 4 5 1006 NULL 8 9 10 356 357 # 8. ON UPDATE SET NULL 358 statement ok 359 UPDATE a SET id = 1008 WHERE id = 8; 360 361 query IIIIIIIIII 362 SELECT * FROM b; 363 ---- 364 1 2 3 4 5 1006 NULL NULL 9 10 365 366 # 9. ON DELETE SET DEFAULT 367 statement ok 368 DELETE FROM a WHERE id = 9 369 370 query IIIIIIIIII 371 SELECT * FROM b; 372 ---- 373 1 2 3 4 5 1006 NULL NULL 109 10 374 375 # 10. ON UPDATE SET DEFAULT 376 statement ok 377 UPDATE a SET id = 1010 WHERE id = 10; 378 379 query IIIIIIIIII 380 SELECT * FROM b; 381 ---- 382 1 2 3 4 5 1006 NULL NULL 109 110 383 384 # Post Test Clean up 385 statement ok 386 DROP TABLE b, a; 387 388 subtest DeleteCascade_Basic 389 ### Basic Delete Cascade 390 # a 391 # / \ 392 # b1 b2 393 # / \ \ 394 # c1 c2 c3 395 396 statement ok 397 CREATE TABLE a ( 398 id STRING PRIMARY KEY 399 ); 400 401 statement ok 402 CREATE TABLE b1 ( 403 id STRING PRIMARY KEY 404 ,delete_cascade STRING NOT NULL REFERENCES a ON DELETE CASCADE 405 ); 406 407 statement ok 408 CREATE TABLE b2 ( 409 id STRING PRIMARY KEY 410 ,delete_cascade STRING NOT NULL REFERENCES a ON DELETE CASCADE 411 ); 412 413 statement ok 414 CREATE TABLE c1 ( 415 id STRING PRIMARY KEY 416 ,delete_cascade STRING NOT NULL REFERENCES b1 ON DELETE CASCADE 417 ); 418 419 statement ok 420 CREATE TABLE c2 ( 421 id STRING PRIMARY KEY 422 ,delete_cascade STRING NOT NULL REFERENCES b1 ON DELETE CASCADE 423 ); 424 425 statement ok 426 CREATE TABLE c3 ( 427 id STRING PRIMARY KEY REFERENCES b2 ON DELETE CASCADE 428 ); 429 430 statement ok 431 INSERT INTO a VALUES ('a-pk1'); 432 INSERT INTO b1 VALUES ('b1-pk1', 'a-pk1'), ('b1-pk2', 'a-pk1'); 433 INSERT INTO b2 VALUES ('b2-pk1', 'a-pk1'), ('b2-pk2', 'a-pk1'); 434 INSERT INTO c1 VALUES 435 ('c1-pk1-b1-pk1', 'b1-pk1') 436 ,('c1-pk2-b1-pk1', 'b1-pk1') 437 ,('c1-pk3-b1-pk2', 'b1-pk2') 438 ,('c1-pk4-b1-pk2', 'b1-pk2') 439 ; 440 INSERT INTO c2 VALUES 441 ('c2-pk1-b1-pk1', 'b1-pk1') 442 ,('c2-pk2-b1-pk1', 'b1-pk1') 443 ,('c2-pk3-b1-pk2', 'b1-pk2') 444 ,('c2-pk4-b1-pk2', 'b1-pk2') 445 ; 446 INSERT INTO c3 VALUES ('b2-pk1'), ('b2-pk2'); 447 448 # ON DELETE CASCADE 449 statement ok 450 DELETE FROM a WHERE id = 'a-pk1'; 451 452 query IIIIII 453 SELECT 454 (SELECT count(*) FROM a) 455 ,(SELECT count(*) FROM b1) 456 ,(SELECT count(*) FROM b2) 457 ,(SELECT count(*) FROM c1) 458 ,(SELECT count(*) FROM c2) 459 ,(SELECT count(*) FROM c3) 460 ; 461 ---- 462 0 0 0 0 0 0 463 464 # Clean up after the test. 465 statement ok 466 DROP TABLE c3, c2, c1, b2, b1, a; 467 468 subtest DeleteCascade_PrimaryKeys 469 ### Basic Delete Cascade using primary keys 470 # a 471 # / \ 472 # b1 b2 473 # / \ 474 # c1 c2 475 476 statement ok 477 CREATE TABLE a ( 478 id STRING PRIMARY KEY 479 ); 480 481 statement ok 482 CREATE TABLE b1 ( 483 id STRING PRIMARY KEY REFERENCES a ON DELETE CASCADE 484 ); 485 486 statement ok 487 CREATE TABLE b2 ( 488 id STRING PRIMARY KEY REFERENCES a ON DELETE CASCADE 489 ); 490 491 statement ok 492 CREATE TABLE c1 ( 493 id STRING PRIMARY KEY REFERENCES b1 ON DELETE CASCADE 494 ); 495 496 statement ok 497 CREATE TABLE c2 ( 498 id STRING PRIMARY KEY REFERENCES b1 ON DELETE CASCADE 499 ); 500 501 statement ok 502 INSERT INTO a VALUES ('pk1'); 503 INSERT INTO b1 VALUES ('pk1'); 504 INSERT INTO b2 VALUES ('pk1'); 505 INSERT INTO c1 VALUES ('pk1'); 506 INSERT INTO c2 VALUES ('pk1'); 507 508 # ON DELETE CASCADE 509 statement ok 510 DELETE FROM a WHERE id = 'pk1'; 511 512 query IIIII 513 SELECT 514 (SELECT count(*) FROM a) 515 ,(SELECT count(*) FROM b1) 516 ,(SELECT count(*) FROM b2) 517 ,(SELECT count(*) FROM c1) 518 ,(SELECT count(*) FROM c2) 519 ; 520 ---- 521 0 0 0 0 0 522 523 # Clean up after the test. 524 statement ok 525 DROP TABLE c2, c1, b2, b1, a; 526 527 subtest DeleteCascade_CompositeFKs_MatchSimple 528 ### Basic Delete Cascade with composite FKs 529 # a 530 # / \ 531 # b1 b2 532 # / \ 533 # c1 c2 534 535 statement ok 536 CREATE TABLE a ( 537 id STRING PRIMARY KEY 538 ,x INT 539 ,UNIQUE (id, x) 540 ); 541 542 statement ok 543 CREATE TABLE b1 ( 544 id STRING PRIMARY KEY 545 ,a_id STRING 546 ,x INT 547 ,y INT 548 ,INDEX (a_id, x, y) 549 ,FOREIGN KEY (a_id, x) REFERENCES a (id, x) ON DELETE CASCADE 550 ,UNIQUE (id, x) 551 ); 552 553 statement ok 554 CREATE TABLE b2 ( 555 id STRING PRIMARY KEY 556 ,a_id STRING 557 ,x INT 558 ,y INT 559 ,INDEX (a_id, x, y) 560 ,FOREIGN KEY (a_id, x) REFERENCES a (id, x) ON DELETE CASCADE 561 ,UNIQUE (id, x) 562 ); 563 564 statement ok 565 CREATE TABLE c1 ( 566 id STRING PRIMARY KEY 567 ,b_id STRING 568 ,x INT 569 ,FOREIGN KEY (b_id, x) REFERENCES b1 (id, x) ON DELETE CASCADE 570 ); 571 572 statement ok 573 CREATE TABLE c2 ( 574 id STRING PRIMARY KEY 575 ,b_id STRING 576 ,x INT 577 ,FOREIGN KEY (b_id, x) REFERENCES b1 (id, x) ON DELETE CASCADE 578 ); 579 580 statement ok 581 INSERT INTO a VALUES ('a-pk1', 1); 582 INSERT INTO b1 VALUES ('b1-pk1', 'a-pk1', 1, 1), ('b1-pk2', 'a-pk1', 1, 2); 583 INSERT INTO b2 VALUES ('b2-pk1', 'a-pk1', 1, 1), ('b2-pk2', 'a-pk1', 1, 2); 584 INSERT INTO c1 VALUES 585 ('c1-pk1-b1-pk1', 'b1-pk1', 1) 586 ,('c1-pk2-b1-pk1', 'b1-pk1', 1) 587 ,('c1-pk3-b1-pk2', 'b1-pk2', 1) 588 ,('c1-pk4-b1-pk2', 'b1-pk2', 1) 589 ; 590 INSERT INTO c2 VALUES 591 ('c2-pk1-b1-pk1', 'b1-pk1', 1) 592 ,('c2-pk2-b1-pk1', 'b1-pk1', 1) 593 ,('c2-pk3-b1-pk2', 'b1-pk2', 1) 594 ,('c2-pk4-b1-pk2', 'b1-pk2', 1) 595 ; 596 597 # ON DELETE CASCADE 598 statement ok 599 DELETE FROM a WHERE id = 'a-pk1'; 600 601 query IIIII 602 SELECT 603 (SELECT count(*) FROM a) 604 ,(SELECT count(*) FROM b1) 605 ,(SELECT count(*) FROM b2) 606 ,(SELECT count(*) FROM c1) 607 ,(SELECT count(*) FROM c2) 608 ; 609 ---- 610 0 0 0 0 0 611 612 # Clean up after the test. 613 statement ok 614 DROP TABLE c2, c1, b2, b1, a; 615 616 subtest DeleteCascade_CompositeFKs_MatchFull 617 ### Basic Delete Cascade with composite FKs 618 # a 619 # / \ 620 # b1 b2 621 # / \ 622 # c1 c2 623 624 statement ok 625 CREATE TABLE a ( 626 id STRING PRIMARY KEY 627 ,x INT 628 ,UNIQUE (id, x) 629 ); 630 631 statement ok 632 CREATE TABLE b1 ( 633 id STRING PRIMARY KEY 634 ,a_id STRING 635 ,x INT 636 ,y INT 637 ,INDEX (a_id, x, y) 638 ,FOREIGN KEY (a_id, x) REFERENCES a (id, x) MATCH FULL ON DELETE CASCADE 639 ,UNIQUE (id, x) 640 ); 641 642 statement ok 643 CREATE TABLE b2 ( 644 id STRING PRIMARY KEY 645 ,a_id STRING 646 ,x INT 647 ,y INT 648 ,INDEX (a_id, x, y) 649 ,FOREIGN KEY (a_id, x) REFERENCES a (id, x) MATCH FULL ON DELETE CASCADE 650 ,UNIQUE (id, x) 651 ); 652 653 statement ok 654 CREATE TABLE c1 ( 655 id STRING PRIMARY KEY 656 ,b_id STRING 657 ,x INT 658 ,FOREIGN KEY (b_id, x) REFERENCES b1 (id, x) MATCH FULL ON DELETE CASCADE 659 ); 660 661 statement ok 662 CREATE TABLE c2 ( 663 id STRING PRIMARY KEY 664 ,b_id STRING 665 ,x INT 666 ,FOREIGN KEY (b_id, x) REFERENCES b1 (id, x) MATCH FULL ON DELETE CASCADE 667 ); 668 669 statement ok 670 INSERT INTO a VALUES ('a-pk1', 1); 671 INSERT INTO b1 VALUES ('b1-pk1', 'a-pk1', 1, 1), ('b1-pk2', 'a-pk1', 1, 2); 672 INSERT INTO b2 VALUES ('b2-pk1', 'a-pk1', 1, 1), ('b2-pk2', 'a-pk1', 1, 2); 673 INSERT INTO c1 VALUES 674 ('c1-pk1-b1-pk1', 'b1-pk1', 1) 675 ,('c1-pk2-b1-pk1', 'b1-pk1', 1) 676 ,('c1-pk3-b1-pk2', 'b1-pk2', 1) 677 ,('c1-pk4-b1-pk2', 'b1-pk2', 1) 678 ; 679 INSERT INTO c2 VALUES 680 ('c2-pk1-b1-pk1', 'b1-pk1', 1) 681 ,('c2-pk2-b1-pk1', 'b1-pk1', 1) 682 ,('c2-pk3-b1-pk2', 'b1-pk2', 1) 683 ,('c2-pk4-b1-pk2', 'b1-pk2', 1) 684 ; 685 686 # ON DELETE CASCADE 687 statement ok 688 DELETE FROM a WHERE id = 'a-pk1'; 689 690 query IIIII 691 SELECT 692 (SELECT count(*) FROM a) 693 ,(SELECT count(*) FROM b1) 694 ,(SELECT count(*) FROM b2) 695 ,(SELECT count(*) FROM c1) 696 ,(SELECT count(*) FROM c2) 697 ; 698 ---- 699 0 0 0 0 0 700 701 # Clean up after the test. 702 statement ok 703 DROP TABLE c2, c1, b2, b1, a; 704 705 subtest DeleteCascade_Restrict 706 ### Basic Delete Cascade with Restrict 707 # a 708 # / \ 709 # b1 b2 710 # / \ 711 # c1 c2 712 # | 713 # d 714 715 statement ok 716 CREATE TABLE a ( 717 id STRING PRIMARY KEY 718 ); 719 720 statement ok 721 CREATE TABLE b1 ( 722 id STRING PRIMARY KEY 723 ,delete_cascade STRING NOT NULL REFERENCES a ON DELETE CASCADE 724 ); 725 726 statement ok 727 CREATE TABLE b2 ( 728 id STRING PRIMARY KEY 729 ,delete_cascade STRING NOT NULL REFERENCES a ON DELETE CASCADE 730 ); 731 732 statement ok 733 CREATE TABLE c1 ( 734 id STRING PRIMARY KEY 735 ,delete_cascade STRING NOT NULL REFERENCES b1 ON DELETE CASCADE 736 ); 737 738 statement ok 739 CREATE TABLE c2 ( 740 id STRING PRIMARY KEY 741 ,delete_cascade STRING NOT NULL REFERENCES b1 ON DELETE CASCADE 742 ); 743 744 statement ok 745 CREATE TABLE d ( 746 id STRING PRIMARY KEY 747 ,delete_restrict STRING NOT NULL REFERENCES c2 ON DELETE RESTRICT 748 ); 749 750 statement ok 751 INSERT INTO a VALUES ('a-pk1'); 752 INSERT INTO b1 VALUES ('b1-pk1', 'a-pk1'), ('b1-pk2', 'a-pk1'); 753 INSERT INTO b2 VALUES ('b2-pk1', 'a-pk1'), ('b2-pk2', 'a-pk1'); 754 INSERT INTO c1 VALUES 755 ('c1-pk1-b1-pk1', 'b1-pk1') 756 ,('c1-pk2-b1-pk1', 'b1-pk1') 757 ,('c1-pk3-b1-pk2', 'b1-pk2') 758 ,('c1-pk4-b1-pk2', 'b1-pk2') 759 ; 760 INSERT INTO c2 VALUES 761 ('c2-pk1-b1-pk1', 'b1-pk1') 762 ,('c2-pk2-b1-pk1', 'b1-pk1') 763 ,('c2-pk3-b1-pk2', 'b1-pk2') 764 ,('c2-pk4-b1-pk2', 'b1-pk2') 765 ; 766 INSERT INTO d VALUES ('d-pk1-c2-pk4-b1-pk2', 'c2-pk4-b1-pk2'); 767 768 # ON DELETE CASCADE 769 statement error delete on table "c2" violates foreign key constraint "fk_delete_restrict_ref_c2" on table "d"\nDETAIL: Key \(id\)=\('c2-pk4-b1-pk2'\) is still referenced from table "d"\. 770 DELETE FROM a WHERE id = 'a-pk1'; 771 772 # Clean up after the test. 773 statement ok 774 DROP TABLE d, c2, c1, b2, b1, a; 775 776 subtest DeleteCascade_Interleaved 777 ### Basic Delete Cascade with Interleaved Tables 778 # a 779 # / \ 780 # b1 b2 781 # / \ \ 782 # c1 c2 c3 783 784 statement ok 785 CREATE TABLE a ( 786 id STRING PRIMARY KEY 787 ); 788 789 statement ok 790 CREATE TABLE b1 ( 791 id STRING PRIMARY KEY REFERENCES a ON DELETE CASCADE 792 ) INTERLEAVE IN PARENT a (id); 793 794 statement ok 795 CREATE TABLE b2 ( 796 id STRING PRIMARY KEY REFERENCES a ON DELETE CASCADE 797 ) INTERLEAVE IN PARENT a (id); 798 799 statement ok 800 CREATE TABLE c1 ( 801 id STRING PRIMARY KEY REFERENCES b1 ON DELETE CASCADE 802 ) INTERLEAVE IN PARENT b1 (id); 803 804 statement ok 805 CREATE TABLE c2 ( 806 id STRING PRIMARY KEY REFERENCES b1 ON DELETE CASCADE 807 ) INTERLEAVE IN PARENT b1 (id); 808 809 statement ok 810 CREATE TABLE c3 ( 811 id STRING PRIMARY KEY REFERENCES b2 ON DELETE CASCADE 812 ) INTERLEAVE IN PARENT b2 (id); 813 814 statement ok 815 INSERT INTO a VALUES ('pk1'), ('pk2'); 816 INSERT INTO b1 VALUES ('pk1'), ('pk2'); 817 INSERT INTO b2 VALUES ('pk1'), ('pk2'); 818 INSERT INTO c1 VALUES ('pk1'), ('pk2'); 819 INSERT INTO c2 VALUES ('pk1'), ('pk2'); 820 INSERT INTO c3 VALUES ('pk1'), ('pk2'); 821 822 # ON DELETE CASCADE from b1 downward 823 statement ok 824 DELETE FROM b1 WHERE id = 'pk2'; 825 826 query IIIIII 827 SELECT 828 (SELECT count(*) FROM a) 829 ,(SELECT count(*) FROM b1) 830 ,(SELECT count(*) FROM b2) 831 ,(SELECT count(*) FROM c1) 832 ,(SELECT count(*) FROM c2) 833 ,(SELECT count(*) FROM c3) 834 ; 835 ---- 836 2 1 2 1 1 2 837 838 # ON DELETE CASCADE 839 statement ok 840 DELETE FROM a WHERE id = 'pk1'; 841 842 query IIIIII 843 SELECT 844 (SELECT count(*) FROM a) 845 ,(SELECT count(*) FROM b1) 846 ,(SELECT count(*) FROM b2) 847 ,(SELECT count(*) FROM c1) 848 ,(SELECT count(*) FROM c2) 849 ,(SELECT count(*) FROM c3) 850 ; 851 ---- 852 1 0 1 0 0 1 853 854 # ON DELETE CASCADE for the rest 855 statement ok 856 DELETE FROM a WHERE id = 'pk2'; 857 858 query IIIIII 859 SELECT 860 (SELECT count(*) FROM a) 861 ,(SELECT count(*) FROM b1) 862 ,(SELECT count(*) FROM b2) 863 ,(SELECT count(*) FROM c1) 864 ,(SELECT count(*) FROM c2) 865 ,(SELECT count(*) FROM c3) 866 ; 867 ---- 868 0 0 0 0 0 0 869 870 # Clean up after the test. 871 statement ok 872 DROP TABLE c3, c2, c1, b2, b1, a; 873 874 subtest DeleteCascade_InterleavedRestrict 875 ### Basic Delete Cascade with Interleaved Tables To Restrict 876 # a 877 # / \ 878 # b1 b2 879 # / \ \ 880 # c1 c2 c3 881 # 882 # In this test, c3 is restricted, so deleting from a should fail, but from b1 883 # should be ok. 884 885 statement ok 886 CREATE TABLE a ( 887 id STRING PRIMARY KEY 888 ); 889 890 statement ok 891 CREATE TABLE b1 ( 892 id STRING PRIMARY KEY REFERENCES a ON DELETE CASCADE 893 ) INTERLEAVE IN PARENT a (id); 894 895 statement ok 896 CREATE TABLE b2 ( 897 id STRING PRIMARY KEY REFERENCES a ON DELETE CASCADE 898 ) INTERLEAVE IN PARENT a (id); 899 900 statement ok 901 CREATE TABLE c1 ( 902 id STRING PRIMARY KEY REFERENCES b1 ON DELETE CASCADE 903 ) INTERLEAVE IN PARENT b1 (id); 904 905 statement ok 906 CREATE TABLE c2 ( 907 id STRING PRIMARY KEY REFERENCES b1 ON DELETE CASCADE 908 ) INTERLEAVE IN PARENT b1 (id); 909 910 statement ok 911 CREATE TABLE c3 ( 912 id STRING PRIMARY KEY REFERENCES b2 ON DELETE RESTRICT 913 ) INTERLEAVE IN PARENT b2 (id); 914 915 statement ok 916 INSERT INTO a VALUES ('pk1'), ('pk2'); 917 INSERT INTO b1 VALUES ('pk1'), ('pk2'); 918 INSERT INTO b2 VALUES ('pk1'), ('pk2'); 919 INSERT INTO c1 VALUES ('pk1'), ('pk2'); 920 INSERT INTO c2 VALUES ('pk1'), ('pk2'); 921 INSERT INTO c3 VALUES ('pk1'), ('pk2'); 922 923 # ON DELETE CASCADE from b1 downward 924 statement ok 925 DELETE FROM b1 WHERE id = 'pk2'; 926 927 query IIIIII 928 SELECT 929 (SELECT count(*) FROM a) 930 ,(SELECT count(*) FROM b1) 931 ,(SELECT count(*) FROM b2) 932 ,(SELECT count(*) FROM c1) 933 ,(SELECT count(*) FROM c2) 934 ,(SELECT count(*) FROM c3) 935 ; 936 ---- 937 2 1 2 1 1 2 938 939 # ON DELETE CASCADE 940 statement error delete on table "b2" violates foreign key constraint "fk_id_ref_b2" on table "c3"\nDETAIL: Key \(id\)=\('pk1'\) is still referenced from table "c3"\. 941 DELETE FROM a WHERE id = 'pk1'; 942 943 # Clean up after the test. 944 statement ok 945 DROP TABLE c3, c2, c1, b2, b1, a; 946 947 subtest DeleteCascade_SelfReference 948 ### Self Reference Delete Cascade 949 # self <- self 950 951 statement ok 952 CREATE TABLE self ( 953 id INT PRIMARY KEY 954 ,other_id INT REFERENCES self ON DELETE CASCADE 955 ); 956 957 statement ok 958 INSERT INTO self VALUES (1, NULL); 959 INSERT INTO self VALUES (2, 1); 960 INSERT INTO self VALUES (3, 2); 961 INSERT INTO self VALUES (4, 3); 962 963 statement ok 964 DELETE FROM self WHERE id = 1; 965 966 query I 967 SELECT count(*) FROM self 968 ---- 969 0 970 971 # Clean up after the test. 972 statement ok 973 DROP TABLE self; 974 975 subtest DeleteCascade_SelfReferenceCycle 976 ### Self Reference Delete Cascade Cycle 977 # self <- self 978 979 statement ok 980 CREATE TABLE self ( 981 id INT PRIMARY KEY 982 ,other_id INT REFERENCES self ON DELETE CASCADE 983 ); 984 985 statement ok 986 INSERT INTO self VALUES (1, NULL); 987 INSERT INTO self VALUES (2, 1); 988 INSERT INTO self VALUES (3, 2); 989 INSERT INTO self VALUES (4, 3); 990 991 statement ok 992 UPDATE self SET other_id = 4 WHERE id = 1; 993 994 statement ok 995 DELETE FROM self WHERE id = 1; 996 997 query I 998 SELECT count(*) FROM self 999 ---- 1000 0 1001 1002 # Clean up after the test. 1003 statement ok 1004 DROP TABLE self; 1005 1006 subtest DeleteCascade_TwoTableLoop 1007 ### Delete cascade loop between two tables 1008 # loop_a <- loop_b 1009 # loop_b <- loop_a 1010 1011 statement ok 1012 CREATE TABLE loop_a ( 1013 id STRING PRIMARY KEY 1014 ,cascade_delete STRING 1015 ,INDEX(cascade_delete) 1016 ); 1017 1018 statement ok 1019 CREATE TABLE loop_b ( 1020 id STRING PRIMARY KEY 1021 ,cascade_delete STRING REFERENCES loop_a ON DELETE CASCADE 1022 ); 1023 1024 statement ok 1025 ALTER TABLE loop_a ADD CONSTRAINT cascade_delete_constraint 1026 FOREIGN KEY (cascade_delete) REFERENCES loop_b (id) 1027 ON DELETE CASCADE; 1028 1029 statement ok 1030 INSERT INTO loop_a (id, cascade_delete) VALUES ('loop_a-pk1', NULL); 1031 INSERT INTO loop_b (id, cascade_delete) VALUES ('loop_b-pk1', 'loop_a-pk1'); 1032 INSERT INTO loop_a (id, cascade_delete) VALUES ('loop_a-pk2', 'loop_b-pk1'); 1033 INSERT INTO loop_b (id, cascade_delete) VALUES ('loop_b-pk2', 'loop_a-pk2'); 1034 INSERT INTO loop_a (id, cascade_delete) VALUES ('loop_a-pk3', 'loop_b-pk2'); 1035 INSERT INTO loop_b (id, cascade_delete) VALUES ('loop_b-pk3', 'loop_a-pk3'); 1036 1037 statement ok 1038 UPDATE loop_a SET cascade_delete = 'loop_b-pk3' WHERE id = 'loop_a-pk1'; 1039 1040 statement ok 1041 DELETE FROM loop_a WHERE id = 'loop_a-pk1'; 1042 1043 query II 1044 SELECT 1045 (SELECT count(*) FROM loop_a) 1046 ,(SELECT count(*) FROM loop_b) 1047 ; 1048 ---- 1049 0 0 1050 1051 # Clean up after the test. 1052 statement ok 1053 DROP TABLE loop_a, loop_b; 1054 1055 subtest DeleteCascade_TwoTableLoopCycle 1056 ### Delete cascade loop between two tables with cycle 1057 # loop_a <- loop_b 1058 # loop_b <- loop_a 1059 1060 statement ok 1061 CREATE TABLE loop_a ( 1062 id STRING PRIMARY KEY 1063 ,cascade_delete STRING 1064 ,INDEX(cascade_delete) 1065 ); 1066 1067 statement ok 1068 CREATE TABLE loop_b ( 1069 id STRING PRIMARY KEY 1070 ,cascade_delete STRING REFERENCES loop_a ON DELETE CASCADE 1071 ); 1072 1073 statement ok 1074 ALTER TABLE loop_a ADD CONSTRAINT cascade_delete_constraint 1075 FOREIGN KEY (cascade_delete) REFERENCES loop_b (id) 1076 ON DELETE CASCADE; 1077 1078 statement ok 1079 INSERT INTO loop_a (id, cascade_delete) VALUES ('loop_a-pk1', NULL); 1080 INSERT INTO loop_b (id, cascade_delete) VALUES ('loop_b-pk1', 'loop_a-pk1'); 1081 INSERT INTO loop_a (id, cascade_delete) VALUES ('loop_a-pk2', 'loop_b-pk1'); 1082 INSERT INTO loop_b (id, cascade_delete) VALUES ('loop_b-pk2', 'loop_a-pk2'); 1083 INSERT INTO loop_a (id, cascade_delete) VALUES ('loop_a-pk3', 'loop_b-pk2'); 1084 INSERT INTO loop_b (id, cascade_delete) VALUES ('loop_b-pk3', 'loop_a-pk3'); 1085 1086 statement ok 1087 DELETE FROM loop_a WHERE id = 'loop_a-pk1'; 1088 1089 query II 1090 SELECT 1091 (SELECT count(*) FROM loop_a) 1092 ,(SELECT count(*) FROM loop_b) 1093 ; 1094 ---- 1095 0 0 1096 1097 # Clean up after the test. 1098 statement ok 1099 DROP TABLE loop_a, loop_b; 1100 1101 subtest DeleteCascade_DoubleSelfReference 1102 ### Delete cascade double self reference 1103 # self_x2 (x) <- (y) 1104 # self_x2 (y) <- (z) 1105 1106 statement ok 1107 CREATE TABLE self_x2 ( 1108 x STRING PRIMARY KEY 1109 ,y STRING UNIQUE REFERENCES self_x2(x) ON DELETE CASCADE 1110 ,z STRING REFERENCES self_x2(y) ON DELETE CASCADE 1111 ); 1112 1113 statement ok 1114 INSERT INTO self_x2 (x, y, z) VALUES ('pk1', NULL, NULL); 1115 INSERT INTO self_x2 (x, y, z) VALUES ('pk2', 'pk1', NULL); 1116 INSERT INTO self_x2 (x, y, z) VALUES ('pk3', 'pk2', 'pk1'); 1117 1118 statement ok 1119 DELETE FROM self_x2 WHERE x = 'pk1'; 1120 1121 query I 1122 SELECT count(*) FROM self_x2 1123 ---- 1124 0 1125 1126 # Clean up after the test. 1127 statement ok 1128 DROP TABLE self_x2; 1129 1130 subtest DeleteCascade_Race 1131 ### Delete cascade race 1132 # a 1133 # / \ 1134 # b c 1135 # | | 1136 # | d 1137 # \ / 1138 # e 1139 statement ok 1140 CREATE TABLE a ( 1141 id STRING PRIMARY KEY 1142 ); 1143 1144 statement ok 1145 CREATE TABLE b ( 1146 id STRING PRIMARY KEY 1147 ,a_id STRING REFERENCES a ON DELETE CASCADE 1148 ); 1149 1150 statement ok 1151 CREATE TABLE c ( 1152 id STRING PRIMARY KEY 1153 ,a_id STRING REFERENCES a ON DELETE CASCADE 1154 ); 1155 1156 statement ok 1157 CREATE TABLE d ( 1158 id STRING PRIMARY KEY 1159 ,c_id STRING REFERENCES c ON DELETE CASCADE 1160 ); 1161 1162 statement ok 1163 CREATE TABLE e ( 1164 id STRING PRIMARY KEY 1165 ,b_id STRING REFERENCES b ON DELETE CASCADE 1166 ,d_id STRING REFERENCES d ON DELETE CASCADE 1167 ); 1168 1169 statement ok 1170 INSERT INTO a (id) VALUES ('a1'); 1171 INSERT INTO b (id, a_id) VALUES ('b1', 'a1'); 1172 INSERT INTO c (id, a_id) VALUES ('c1', 'a1'); 1173 INSERT INTO d (id, c_id) VALUES ('d1', 'c1'); 1174 INSERT INTO e (id, b_id, d_id) VALUES ('e1', 'b1', 'd1'); 1175 1176 statement ok 1177 DELETE FROM a WHERE id = 'a1'; 1178 1179 query IIIII 1180 SELECT 1181 (SELECT count(*) FROM a) 1182 ,(SELECT count(*) FROM b) 1183 ,(SELECT count(*) FROM c) 1184 ,(SELECT count(*) FROM d) 1185 ,(SELECT count(*) FROM e) 1186 ; 1187 ---- 1188 0 0 0 0 0 1189 1190 # Clean up after the test. 1191 statement ok 1192 DROP TABLE e, d, c, b, a; 1193 1194 subtest DeleteCascade_Multi 1195 # Ensures that the cascader can be reused. See #21563. 1196 1197 statement ok 1198 CREATE TABLE a ( 1199 id INT PRIMARY KEY 1200 ); 1201 CREATE TABLE b ( 1202 id INT PRIMARY KEY 1203 ,a_id INT REFERENCES a ON DELETE CASCADE 1204 ) 1205 1206 statement ok 1207 INSERT INTO a VALUES (1), (2), (3); 1208 INSERT INTO b VALUES (1, 1), (2, NULL), (3, 2), (4, 1), (5, NULL); 1209 1210 statement ok 1211 DELETE FROM a; 1212 1213 query II rowsort 1214 SELECT id, a_id FROM b; 1215 ---- 1216 2 NULL 1217 5 NULL 1218 1219 # Clean up. 1220 statement ok 1221 DROP TABLE b, a; 1222 1223 subtest UpdateCascade_Basic 1224 ### Basic Update Cascade 1225 # a 1226 # / \ 1227 # b1 b2 1228 # / \ \ 1229 # c1 c2 c3 1230 1231 statement ok 1232 CREATE TABLE a ( 1233 id STRING PRIMARY KEY 1234 ); 1235 1236 statement ok 1237 CREATE TABLE b1 ( 1238 id STRING PRIMARY KEY 1239 ,update_cascade STRING NOT NULL UNIQUE REFERENCES a ON UPDATE CASCADE 1240 ); 1241 1242 statement ok 1243 CREATE TABLE b2 ( 1244 id STRING PRIMARY KEY 1245 ,update_cascade STRING NOT NULL UNIQUE REFERENCES a ON UPDATE CASCADE 1246 ); 1247 1248 statement ok 1249 CREATE TABLE c1 ( 1250 id STRING PRIMARY KEY 1251 ,update_cascade STRING NOT NULL REFERENCES b1 (update_cascade) ON UPDATE CASCADE 1252 ); 1253 1254 statement ok 1255 CREATE TABLE c2 ( 1256 id STRING PRIMARY KEY 1257 ,update_cascade STRING NOT NULL REFERENCES b1 (update_cascade) ON UPDATE CASCADE 1258 ); 1259 1260 statement ok 1261 CREATE TABLE c3 ( 1262 id STRING PRIMARY KEY REFERENCES b2(update_cascade) ON UPDATE CASCADE 1263 ); 1264 1265 statement ok 1266 INSERT INTO a VALUES ('original'); 1267 INSERT INTO b1 VALUES ('b1-pk1', 'original'); 1268 INSERT INTO b2 VALUES ('b2-pk1', 'original'); 1269 INSERT INTO c1 VALUES 1270 ('c1-pk1', 'original') 1271 ,('c1-pk2', 'original') 1272 ,('c1-pk3', 'original') 1273 ,('c1-pk4', 'original') 1274 ; 1275 INSERT INTO c2 VALUES 1276 ('c2-pk1', 'original') 1277 ,('c2-pk2', 'original') 1278 ,('c2-pk3', 'original') 1279 ,('c2-pk4', 'original') 1280 ; 1281 INSERT INTO c3 VALUES ('original'); 1282 1283 # ON UPDATE CASCADE 1284 statement ok 1285 UPDATE a SET id = 'updated' WHERE id = 'original'; 1286 1287 query T 1288 SELECT * FROM a; 1289 ---- 1290 updated 1291 1292 query TT 1293 SELECT * FROM b1; 1294 ---- 1295 b1-pk1 updated 1296 1297 query TT 1298 SELECT * FROM b2; 1299 ---- 1300 b2-pk1 updated 1301 1302 query TT rowsort 1303 SELECT * FROM c1; 1304 ---- 1305 c1-pk1 updated 1306 c1-pk2 updated 1307 c1-pk3 updated 1308 c1-pk4 updated 1309 1310 query TT rowsort 1311 SELECT * FROM c2; 1312 ---- 1313 c2-pk1 updated 1314 c2-pk2 updated 1315 c2-pk3 updated 1316 c2-pk4 updated 1317 1318 # Clean up after the test. 1319 statement ok 1320 DROP TABLE c3, c2, c1, b2, b1, a; 1321 1322 subtest UpdateCascade_PrimaryKeys 1323 ### Basic Update Cascade using only primary keys 1324 # a 1325 # / \ 1326 # b1 b2 1327 # / \ 1328 # c1 c2 1329 1330 statement ok 1331 CREATE TABLE a ( 1332 id STRING PRIMARY KEY 1333 ); 1334 1335 statement ok 1336 CREATE TABLE b1 ( 1337 id STRING PRIMARY KEY REFERENCES a ON UPDATE CASCADE 1338 ); 1339 1340 statement ok 1341 CREATE TABLE b2 ( 1342 id STRING PRIMARY KEY REFERENCES a ON UPDATE CASCADE 1343 ); 1344 1345 statement ok 1346 CREATE TABLE c1 ( 1347 id STRING PRIMARY KEY REFERENCES b1 ON UPDATE CASCADE 1348 ); 1349 1350 statement ok 1351 CREATE TABLE c2 ( 1352 id STRING PRIMARY KEY REFERENCES b1 ON UPDATE CASCADE 1353 ); 1354 1355 statement ok 1356 INSERT INTO a VALUES ('original'); 1357 INSERT INTO b1 VALUES ('original'); 1358 INSERT INTO b2 VALUES ('original'); 1359 INSERT INTO c1 VALUES ('original'); 1360 INSERT INTO c2 VALUES ('original'); 1361 1362 # ON UPDATE CASCADE 1363 statement ok 1364 UPDATE a SET id = 'updated' WHERE id = 'original'; 1365 1366 query TTTTT 1367 SELECT 1368 (SELECT id FROM a) 1369 ,(SELECT id FROM b1) 1370 ,(SELECT id FROM b2) 1371 ,(SELECT id FROM c1) 1372 ,(SELECT id FROM c2) 1373 ; 1374 ---- 1375 updated updated updated updated updated 1376 1377 # Clean up after the test. 1378 statement ok 1379 DROP TABLE c2, c1, b2, b1, a; 1380 1381 subtest UpdateCascade_CompositeFKs_MatchSimple 1382 ### Basic Update Cascade with composite FKs 1383 # a 1384 # / \ 1385 # b1 b2 1386 # / \ 1387 # c1 c2 1388 1389 statement ok 1390 CREATE TABLE a ( 1391 id STRING PRIMARY KEY 1392 ,x INT 1393 ,UNIQUE (id, x) 1394 ); 1395 1396 statement ok 1397 CREATE TABLE b1 ( 1398 id STRING PRIMARY KEY 1399 ,a_id STRING 1400 ,x INT 1401 ,y INT 1402 ,INDEX (a_id, x, y) 1403 ,FOREIGN KEY (a_id, x) REFERENCES a (id, x) ON UPDATE CASCADE 1404 ,UNIQUE (id, x) 1405 ); 1406 1407 statement ok 1408 CREATE TABLE b2 ( 1409 id STRING PRIMARY KEY 1410 ,a_id STRING 1411 ,x INT 1412 ,y INT 1413 ,INDEX (a_id, x, y) 1414 ,FOREIGN KEY (a_id, x) REFERENCES a (id, x) ON UPDATE CASCADE 1415 ,UNIQUE (id, x) 1416 ); 1417 1418 statement ok 1419 CREATE TABLE c1 ( 1420 id STRING PRIMARY KEY 1421 ,b_id STRING 1422 ,x INT 1423 ,FOREIGN KEY (b_id, x) REFERENCES b1 (id, x) ON UPDATE CASCADE 1424 ); 1425 1426 statement ok 1427 CREATE TABLE c2 ( 1428 id STRING PRIMARY KEY 1429 ,b_id STRING 1430 ,x INT 1431 ,FOREIGN KEY (b_id, x) REFERENCES b1 (id, x) ON UPDATE CASCADE 1432 ); 1433 1434 statement ok 1435 INSERT INTO a VALUES ('a-pk1', 1); 1436 INSERT INTO b1 VALUES ('b1-pk1', 'a-pk1', 1, 1), ('b1-pk2', 'a-pk1', 1, 2); 1437 INSERT INTO b2 VALUES ('b2-pk1', 'a-pk1', 1, 1), ('b2-pk2', 'a-pk1', 1, 2); 1438 INSERT INTO c1 VALUES 1439 ('c1-pk1-b1-pk1', 'b1-pk1', 1) 1440 ,('c1-pk2-b1-pk1', 'b1-pk1', 1) 1441 ,('c1-pk3-b1-pk2', 'b1-pk2', 1) 1442 ,('c1-pk4-b1-pk2', 'b1-pk2', 1) 1443 ; 1444 INSERT INTO c2 VALUES 1445 ('c2-pk1-b1-pk1', 'b1-pk1', 1) 1446 ,('c2-pk2-b1-pk1', 'b1-pk1', 1) 1447 ,('c2-pk3-b1-pk2', 'b1-pk2', 1) 1448 ,('c2-pk4-b1-pk2', 'b1-pk2', 1) 1449 ; 1450 1451 # ON UPDATE CASCADE 1452 statement ok 1453 UPDATE a SET x = 2 WHERE x = 1; 1454 1455 query TI 1456 SELECT * FROM a; 1457 ---- 1458 a-pk1 2 1459 1460 query TTII rowsort 1461 SELECT * FROM b1; 1462 ---- 1463 b1-pk1 a-pk1 2 1 1464 b1-pk2 a-pk1 2 2 1465 1466 query TTII rowsort 1467 SELECT * FROM b2; 1468 ---- 1469 b2-pk1 a-pk1 2 1 1470 b2-pk2 a-pk1 2 2 1471 1472 query TTI rowsort 1473 SELECT * FROM c1; 1474 ---- 1475 c1-pk1-b1-pk1 b1-pk1 2 1476 c1-pk2-b1-pk1 b1-pk1 2 1477 c1-pk3-b1-pk2 b1-pk2 2 1478 c1-pk4-b1-pk2 b1-pk2 2 1479 1480 query TTI rowsort 1481 SELECT * FROM c2; 1482 ---- 1483 c2-pk1-b1-pk1 b1-pk1 2 1484 c2-pk2-b1-pk1 b1-pk1 2 1485 c2-pk3-b1-pk2 b1-pk2 2 1486 c2-pk4-b1-pk2 b1-pk2 2 1487 1488 # Clean up after the test. 1489 statement ok 1490 DROP TABLE c2, c1, b2, b1, a; 1491 1492 subtest UpdateCascade_CompositeFKs_MatchFull 1493 ### Basic Update Cascade with composite FKs 1494 # a 1495 # / \ 1496 # b1 b2 1497 # / \ 1498 # c1 c2 1499 1500 statement ok 1501 CREATE TABLE a ( 1502 id STRING PRIMARY KEY 1503 ,x INT 1504 ,UNIQUE (id, x) 1505 ); 1506 1507 statement ok 1508 CREATE TABLE b1 ( 1509 id STRING PRIMARY KEY 1510 ,a_id STRING 1511 ,x INT 1512 ,y INT 1513 ,INDEX (a_id, x, y) 1514 ,FOREIGN KEY (a_id, x) REFERENCES a (id, x) MATCH FULL ON UPDATE CASCADE 1515 ,UNIQUE (id, x) 1516 ); 1517 1518 statement ok 1519 CREATE TABLE b2 ( 1520 id STRING PRIMARY KEY 1521 ,a_id STRING 1522 ,x INT 1523 ,y INT 1524 ,INDEX (a_id, x, y) 1525 ,FOREIGN KEY (a_id, x) REFERENCES a (id, x) MATCH FULL ON UPDATE CASCADE 1526 ,UNIQUE (id, x) 1527 ); 1528 1529 statement ok 1530 CREATE TABLE c1 ( 1531 id STRING PRIMARY KEY 1532 ,b_id STRING 1533 ,x INT 1534 ,FOREIGN KEY (b_id, x) REFERENCES b1 (id, x) MATCH FULL ON UPDATE CASCADE 1535 ); 1536 1537 statement ok 1538 CREATE TABLE c2 ( 1539 id STRING PRIMARY KEY 1540 ,b_id STRING 1541 ,x INT 1542 ,FOREIGN KEY (b_id, x) REFERENCES b1 (id, x) MATCH FULL ON UPDATE CASCADE 1543 ); 1544 1545 statement ok 1546 INSERT INTO a VALUES ('a-pk1', 1); 1547 INSERT INTO b1 VALUES ('b1-pk1', 'a-pk1', 1, 1), ('b1-pk2', 'a-pk1', 1, 2); 1548 INSERT INTO b2 VALUES ('b2-pk1', 'a-pk1', 1, 1), ('b2-pk2', 'a-pk1', 1, 2); 1549 INSERT INTO c1 VALUES 1550 ('c1-pk1-b1-pk1', 'b1-pk1', 1) 1551 ,('c1-pk2-b1-pk1', 'b1-pk1', 1) 1552 ,('c1-pk3-b1-pk2', 'b1-pk2', 1) 1553 ,('c1-pk4-b1-pk2', 'b1-pk2', 1) 1554 ; 1555 INSERT INTO c2 VALUES 1556 ('c2-pk1-b1-pk1', 'b1-pk1', 1) 1557 ,('c2-pk2-b1-pk1', 'b1-pk1', 1) 1558 ,('c2-pk3-b1-pk2', 'b1-pk2', 1) 1559 ,('c2-pk4-b1-pk2', 'b1-pk2', 1) 1560 ; 1561 1562 # ON UPDATE CASCADE 1563 statement ok 1564 UPDATE a SET x = 2 WHERE x = 1; 1565 1566 query TI 1567 SELECT * FROM a; 1568 ---- 1569 a-pk1 2 1570 1571 query TTII rowsort 1572 SELECT * FROM b1; 1573 ---- 1574 b1-pk1 a-pk1 2 1 1575 b1-pk2 a-pk1 2 2 1576 1577 query TTII rowsort 1578 SELECT * FROM b2; 1579 ---- 1580 b2-pk1 a-pk1 2 1 1581 b2-pk2 a-pk1 2 2 1582 1583 query TTI rowsort 1584 SELECT * FROM c1; 1585 ---- 1586 c1-pk1-b1-pk1 b1-pk1 2 1587 c1-pk2-b1-pk1 b1-pk1 2 1588 c1-pk3-b1-pk2 b1-pk2 2 1589 c1-pk4-b1-pk2 b1-pk2 2 1590 1591 query TTI rowsort 1592 SELECT * FROM c2; 1593 ---- 1594 c2-pk1-b1-pk1 b1-pk1 2 1595 c2-pk2-b1-pk1 b1-pk1 2 1596 c2-pk3-b1-pk2 b1-pk2 2 1597 c2-pk4-b1-pk2 b1-pk2 2 1598 1599 # Clean up after the test. 1600 statement ok 1601 DROP TABLE c2, c1, b2, b1, a; 1602 1603 subtest UpdateCascade_Restrict 1604 ### Basic Update Cascade with Restrict 1605 # This test has a restrict on both d tables and tests both. 1606 # c3 and d2 use primary keys to match while the rest use non-primary keys. 1607 # Both restricts are tested. 1608 # a 1609 # / \ 1610 # b1 b2 1611 # / \ \ 1612 # c1 c2 c3 1613 # | | 1614 # d1 d2 1615 1616 statement ok 1617 CREATE TABLE a ( 1618 id STRING PRIMARY KEY 1619 ); 1620 1621 statement ok 1622 CREATE TABLE b1 ( 1623 id STRING PRIMARY KEY 1624 ,update_cascade STRING NOT NULL UNIQUE REFERENCES a ON UPDATE CASCADE 1625 ); 1626 1627 statement ok 1628 CREATE TABLE b2 ( 1629 id STRING PRIMARY KEY 1630 ,update_cascade STRING NOT NULL UNIQUE REFERENCES a ON UPDATE CASCADE 1631 ); 1632 1633 statement ok 1634 CREATE TABLE c1 ( 1635 id STRING PRIMARY KEY 1636 ,update_cascade STRING NOT NULL REFERENCES b1 (update_cascade) ON UPDATE CASCADE 1637 ); 1638 1639 statement ok 1640 CREATE TABLE c2 ( 1641 id STRING PRIMARY KEY 1642 ,update_cascade STRING NOT NULL UNIQUE REFERENCES b1 (update_cascade) ON UPDATE CASCADE 1643 ); 1644 1645 statement ok 1646 CREATE TABLE c3 ( 1647 id STRING PRIMARY KEY REFERENCES b2(update_cascade) ON UPDATE CASCADE 1648 ); 1649 1650 statement ok 1651 CREATE TABLE d1 ( 1652 id STRING PRIMARY KEY 1653 ,update_restrict STRING NOT NULL REFERENCES c2 (update_cascade) ON UPDATE RESTRICT 1654 ); 1655 1656 statement ok 1657 CREATE TABLE d2 ( 1658 id STRING PRIMARY KEY REFERENCES c3 ON UPDATE RESTRICT 1659 ); 1660 1661 statement ok 1662 INSERT INTO a VALUES ('original'); 1663 INSERT INTO b1 VALUES ('b1-pk1', 'original'); 1664 INSERT INTO b2 VALUES ('b2-pk1', 'original'); 1665 INSERT INTO c1 VALUES 1666 ('c1-pk1', 'original') 1667 ,('c1-pk2', 'original') 1668 ,('c1-pk3', 'original') 1669 ,('c1-pk4', 'original') 1670 ; 1671 INSERT INTO c2 VALUES ('c2-pk1', 'original'); 1672 INSERT INTO c3 VALUES ('original'); 1673 1674 # Test non-primary key restrict. 1675 statement ok 1676 INSERT INTO d1 VALUES ('d1-pk1', 'original'); 1677 1678 # ON UPDATE CASCADE 1679 statement error pq: update on table "c2" violates foreign key constraint "fk_update_restrict_ref_c2" on table "d1"\nDETAIL: Key \(update_cascade\)=\('original'\) is still referenced from table "d1"\. 1680 UPDATE a SET id = 'updated' WHERE id = 'original'; 1681 1682 statement ok 1683 DELETE FROM d1 WHERE id = 'd1-pk1'; 1684 1685 # Test a primary key restrict. 1686 statement ok 1687 INSERT INTO d2 VALUES ('original'); 1688 1689 # ON UPDATE CASCADE 1690 statement error pq: update on table "c3" violates foreign key constraint "fk_id_ref_c3" on table "d2"\nDETAIL: Key \(id\)=\('original'\) is still referenced from table "d2"\. 1691 UPDATE a SET id = 'updated' WHERE id = 'original'; 1692 1693 # Clean up after the test. 1694 statement ok 1695 DROP TABLE d2, d1, c3, c2, c1, b2, b1, a; 1696 1697 subtest UpdateCascade_Interleaved 1698 ### Basic Update Cascade with Interleaved Tables 1699 # a 1700 # / \ 1701 # b1 b2 1702 # / \ \ 1703 # c1 c2 c3 1704 1705 statement ok 1706 CREATE TABLE a ( 1707 id STRING PRIMARY KEY 1708 ); 1709 1710 statement ok 1711 CREATE TABLE b1 ( 1712 id STRING PRIMARY KEY REFERENCES a ON UPDATE CASCADE 1713 ) INTERLEAVE IN PARENT a (id); 1714 1715 statement ok 1716 CREATE TABLE b2 ( 1717 id STRING PRIMARY KEY REFERENCES a ON UPDATE CASCADE 1718 ) INTERLEAVE IN PARENT a (id); 1719 1720 statement ok 1721 CREATE TABLE c1 ( 1722 id STRING PRIMARY KEY REFERENCES b1 ON UPDATE CASCADE 1723 ) INTERLEAVE IN PARENT b1 (id); 1724 1725 statement ok 1726 CREATE TABLE c2 ( 1727 id STRING PRIMARY KEY REFERENCES b1 ON UPDATE CASCADE 1728 ) INTERLEAVE IN PARENT b1 (id); 1729 1730 statement ok 1731 CREATE TABLE c3 ( 1732 id STRING PRIMARY KEY REFERENCES b2 ON UPDATE CASCADE 1733 ) INTERLEAVE IN PARENT b2 (id); 1734 1735 statement ok 1736 INSERT INTO a VALUES ('original'), ('updated'); 1737 INSERT INTO b1 VALUES ('original'); 1738 INSERT INTO b2 VALUES ('original'); 1739 INSERT INTO c1 VALUES ('original'); 1740 INSERT INTO c2 VALUES ('original'); 1741 INSERT INTO c3 VALUES ('original'); 1742 1743 # ON UPDATE CASCADE from b1 downward 1744 statement ok 1745 UPDATE b1 SET id = 'updated' WHERE id = 'original'; 1746 1747 query T rowsort 1748 SELECT * FROM a; 1749 ---- 1750 original 1751 updated 1752 1753 query TTTTT 1754 SELECT 1755 (SELECT id FROM b1) 1756 ,(SELECT id FROM b2) 1757 ,(SELECT id FROM c1) 1758 ,(SELECT id FROM c2) 1759 ,(SELECT id FROM c3) 1760 ; 1761 ---- 1762 updated original updated updated original 1763 1764 # ON UPDATE CASCADE from a downward 1765 statement ok 1766 UPDATE a SET id = 'updated2' WHERE id = 'original'; 1767 1768 query T rowsort 1769 SELECT * FROM a; 1770 ---- 1771 updated 1772 updated2 1773 1774 query TTTTT 1775 SELECT 1776 (SELECT id FROM b1) 1777 ,(SELECT id FROM b2) 1778 ,(SELECT id FROM c1) 1779 ,(SELECT id FROM c2) 1780 ,(SELECT id FROM c3) 1781 ; 1782 ---- 1783 updated updated2 updated updated updated2 1784 1785 # Clean up after the test. 1786 statement ok 1787 DROP TABLE c3, c2, c1, b2, b1, a; 1788 1789 subtest UpdateCascade_InterleavedRestrict 1790 ### Basic Update Cascade with Interleaved Tables To Restrict 1791 # a 1792 # / \ 1793 # b1 b2 1794 # / \ \ 1795 # c1 c2 c3 1796 1797 statement ok 1798 CREATE TABLE a ( 1799 id STRING PRIMARY KEY 1800 ); 1801 1802 statement ok 1803 CREATE TABLE b1 ( 1804 id STRING PRIMARY KEY REFERENCES a ON UPDATE CASCADE 1805 ) INTERLEAVE IN PARENT a (id); 1806 1807 statement ok 1808 CREATE TABLE b2 ( 1809 id STRING PRIMARY KEY REFERENCES a ON UPDATE CASCADE 1810 ) INTERLEAVE IN PARENT a (id); 1811 1812 statement ok 1813 CREATE TABLE c1 ( 1814 id STRING PRIMARY KEY REFERENCES b1 ON UPDATE CASCADE 1815 ) INTERLEAVE IN PARENT b1 (id); 1816 1817 statement ok 1818 CREATE TABLE c2 ( 1819 id STRING PRIMARY KEY REFERENCES b1 ON UPDATE CASCADE 1820 ) INTERLEAVE IN PARENT b1 (id); 1821 1822 statement ok 1823 CREATE TABLE c3 ( 1824 id STRING PRIMARY KEY REFERENCES b2 ON UPDATE RESTRICT 1825 ) INTERLEAVE IN PARENT b2 (id); 1826 1827 statement ok 1828 INSERT INTO a VALUES ('original'), ('updated'); 1829 INSERT INTO b1 VALUES ('original'); 1830 INSERT INTO b2 VALUES ('original'); 1831 INSERT INTO c1 VALUES ('original'); 1832 INSERT INTO c2 VALUES ('original'); 1833 INSERT INTO c3 VALUES ('original'); 1834 1835 # ON UPDATE CASCADE from b1 downward 1836 statement ok 1837 UPDATE b1 SET id = 'updated' WHERE id = 'original'; 1838 1839 query T rowsort 1840 SELECT * FROM a; 1841 ---- 1842 original 1843 updated 1844 1845 query TTTTT 1846 SELECT 1847 (SELECT id FROM b1) 1848 ,(SELECT id FROM b2) 1849 ,(SELECT id FROM c1) 1850 ,(SELECT id FROM c2) 1851 ,(SELECT id FROM c3) 1852 ; 1853 ---- 1854 updated original updated updated original 1855 1856 # ON UPDATE CASCADE from a downward 1857 statement error pq: update on table "b2" violates foreign key constraint "fk_id_ref_b2" on table "c3"\nDETAIL: Key \(id\)=\('original'\) is still referenced from table "c3"\. 1858 UPDATE a SET id = 'updated2' WHERE id = 'original'; 1859 1860 # Clean up after the test. 1861 statement ok 1862 DROP TABLE c3, c2, c1, b2, b1, a; 1863 1864 subtest UpdateCascade_SelfReference 1865 ### Self Reference Update Cascade 1866 # self <- self 1867 1868 statement ok 1869 CREATE TABLE self ( 1870 id INT PRIMARY KEY 1871 ,other_id INT REFERENCES self ON UPDATE CASCADE 1872 ); 1873 1874 statement ok 1875 INSERT INTO self VALUES (1, NULL); 1876 INSERT INTO self VALUES (2, 1); 1877 INSERT INTO self VALUES (3, 2); 1878 1879 query II rowsort 1880 SELECT * FROM self; 1881 ---- 1882 1 NULL 1883 2 1 1884 3 2 1885 1886 statement ok 1887 UPDATE self SET id = 4 WHERE id = 2; 1888 1889 query II rowsort 1890 SELECT * FROM self; 1891 ---- 1892 1 NULL 1893 4 1 1894 3 4 1895 1896 # Clean up after the test. 1897 statement ok 1898 DROP TABLE self; 1899 1900 subtest UpdateCascade_TwoTableLoop 1901 ### Delete cascade loop between two tables 1902 # loop_a <- loop_b 1903 # loop_b <- loop_a 1904 1905 statement ok 1906 CREATE TABLE loop_a ( 1907 id STRING PRIMARY KEY 1908 ); 1909 1910 statement ok 1911 CREATE TABLE loop_b ( 1912 id STRING PRIMARY KEY REFERENCES loop_a ON UPDATE CASCADE 1913 ); 1914 1915 statement ok 1916 INSERT INTO loop_a VALUES ('original'); 1917 INSERT INTO loop_b VALUES ('original'); 1918 1919 statement ok 1920 ALTER TABLE loop_a ADD CONSTRAINT cascade_update_constraint 1921 FOREIGN KEY (id) REFERENCES loop_b 1922 ON UPDATE CASCADE; 1923 1924 query TT 1925 SELECT 1926 (SELECT id FROM loop_a) 1927 ,(SELECT id FROM loop_b) 1928 ; 1929 ---- 1930 original original 1931 1932 statement ok 1933 UPDATE loop_a SET id = 'updated' WHERE id = 'original'; 1934 1935 query TT 1936 SELECT 1937 (SELECT id FROM loop_a) 1938 ,(SELECT id FROM loop_b) 1939 ; 1940 ---- 1941 updated updated 1942 1943 statement ok 1944 UPDATE loop_b SET id = 'updated2' WHERE id = 'updated'; 1945 1946 query TT 1947 SELECT 1948 (SELECT id FROM loop_a) 1949 ,(SELECT id FROM loop_b) 1950 ; 1951 ---- 1952 updated2 updated2 1953 1954 # Clean up after the test. 1955 statement ok 1956 DROP TABLE loop_a, loop_b; 1957 1958 subtest UpdateCascade_DoubleSelfReference 1959 ### Update cascade double self reference 1960 # self_x2 (x) <- (y) 1961 # self_x2 (y) <- (z) 1962 1963 statement ok 1964 CREATE TABLE self_x2 ( 1965 x STRING PRIMARY KEY 1966 ,y STRING UNIQUE REFERENCES self_x2(x) ON UPDATE CASCADE 1967 ,z STRING REFERENCES self_x2(y) ON UPDATE CASCADE 1968 ); 1969 1970 statement ok 1971 INSERT INTO self_x2 (x, y, z) VALUES ('pk1', NULL, NULL); 1972 INSERT INTO self_x2 (x, y, z) VALUES ('pk2', 'pk1', NULL); 1973 INSERT INTO self_x2 (x, y, z) VALUES ('pk3', 'pk2', 'pk1'); 1974 1975 # ON UPDATE CASCADE 1976 statement ok 1977 UPDATE self_x2 SET x = 'pk1-updated' WHERE x = 'pk1'; 1978 1979 statement ok 1980 UPDATE self_x2 SET x = 'pk2-updated' WHERE x = 'pk2'; 1981 1982 statement ok 1983 UPDATE self_x2 SET x = 'pk3-updated' WHERE x = 'pk3'; 1984 1985 query TTT rowsort 1986 SELECT * FROM self_x2 1987 ---- 1988 pk1-updated NULL NULL 1989 pk2-updated pk1-updated NULL 1990 pk3-updated pk2-updated pk1-updated 1991 1992 # Clean up after the test. 1993 statement ok 1994 DROP TABLE self_x2; 1995 1996 subtest UpdateCascade_TwoUpdates 1997 ### Update cascade two updates to the same table, then both of those cascade to 1998 # yet another table 1999 # a 2000 # / \ 2001 # b c 2002 # | | 2003 # | d 2004 # \ / 2005 # e 2006 # | 2007 # f 2008 statement ok 2009 CREATE TABLE a ( 2010 id STRING PRIMARY KEY 2011 ); 2012 2013 statement ok 2014 CREATE TABLE b ( 2015 id STRING PRIMARY KEY REFERENCES a ON UPDATE CASCADE 2016 ); 2017 2018 statement ok 2019 CREATE TABLE c ( 2020 id STRING PRIMARY KEY REFERENCES a ON UPDATE CASCADE 2021 ); 2022 2023 statement ok 2024 CREATE TABLE d ( 2025 id STRING PRIMARY KEY REFERENCES c ON UPDATE CASCADE 2026 ); 2027 2028 statement ok 2029 CREATE TABLE e ( 2030 b_id STRING PRIMARY KEY REFERENCES b ON UPDATE CASCADE 2031 ,d_id STRING UNIQUE REFERENCES d ON UPDATE CASCADE 2032 ); 2033 2034 statement ok 2035 CREATE TABLE f ( 2036 e_b_id STRING PRIMARY KEY REFERENCES e (b_id) ON UPDATE CASCADE 2037 ,e_d_id STRING REFERENCES e (d_id) ON UPDATE CASCADE 2038 ); 2039 2040 statement ok 2041 INSERT INTO a (id) VALUES ('original'); 2042 INSERT INTO b (id) VALUES ('original'); 2043 INSERT INTO c (id) VALUES ('original'); 2044 INSERT INTO d (id) VALUES ('original'); 2045 INSERT INTO e (b_id, d_id) VALUES ('original', 'original'); 2046 INSERT INTO f (e_b_id, e_d_id) VALUES ('original', 'original'); 2047 2048 statement ok 2049 UPDATE a SET id = 'updated' WHERE id = 'original'; 2050 2051 query TTTT 2052 SELECT 2053 (SELECT id FROM a) 2054 ,(SELECT id FROM b) 2055 ,(SELECT id FROM c) 2056 ,(SELECT id FROM d) 2057 ; 2058 ---- 2059 updated updated updated updated 2060 2061 query TT 2062 SELECT * FROM e 2063 ---- 2064 updated updated 2065 2066 query TT 2067 SELECT * FROM f 2068 ---- 2069 updated updated 2070 2071 # Clean up after the test. 2072 statement ok 2073 DROP TABLE f, e, d, c, b, a; 2074 2075 subtest UpdateCascade_TwoUpdatesReverse 2076 ### Update cascade two updates to the same table, then both of those cascade to 2077 # yet another table. 2078 # This is a similar test to UpdateCascade_TwoUpdates, but table d is now between 2079 # b and e instead of c and e. 2080 # a 2081 # / \ 2082 # b c 2083 # | | 2084 # d | 2085 # \ / 2086 # e 2087 # | 2088 # f 2089 statement ok 2090 CREATE TABLE a ( 2091 id STRING PRIMARY KEY 2092 ); 2093 2094 statement ok 2095 CREATE TABLE b ( 2096 id STRING PRIMARY KEY REFERENCES a ON UPDATE CASCADE 2097 ); 2098 2099 statement ok 2100 CREATE TABLE c ( 2101 id STRING PRIMARY KEY REFERENCES a ON UPDATE CASCADE 2102 ); 2103 2104 statement ok 2105 CREATE TABLE d ( 2106 id STRING PRIMARY KEY REFERENCES b ON UPDATE CASCADE 2107 ); 2108 2109 statement ok 2110 CREATE TABLE e ( 2111 d_id STRING PRIMARY KEY REFERENCES d ON UPDATE CASCADE 2112 ,c_id STRING UNIQUE REFERENCES c ON UPDATE CASCADE 2113 ); 2114 2115 statement ok 2116 CREATE TABLE f ( 2117 e_d_id STRING PRIMARY KEY REFERENCES e (d_id) ON UPDATE CASCADE 2118 ,e_c_id STRING REFERENCES e (c_id) ON UPDATE CASCADE 2119 ); 2120 2121 statement ok 2122 INSERT INTO a (id) VALUES ('original'); 2123 INSERT INTO b (id) VALUES ('original'); 2124 INSERT INTO c (id) VALUES ('original'); 2125 INSERT INTO d (id) VALUES ('original'); 2126 INSERT INTO e (d_id, c_id) VALUES ('original', 'original'); 2127 INSERT INTO f (e_d_id, e_c_id) VALUES ('original', 'original'); 2128 2129 statement ok 2130 UPDATE a SET id = 'updated' WHERE id = 'original'; 2131 2132 query TTTT 2133 SELECT 2134 (SELECT id FROM a) 2135 ,(SELECT id FROM b) 2136 ,(SELECT id FROM c) 2137 ,(SELECT id FROM d) 2138 ; 2139 ---- 2140 updated updated updated updated 2141 2142 query TT 2143 SELECT * FROM e 2144 ---- 2145 updated updated 2146 2147 query TT 2148 SELECT * FROM f 2149 ---- 2150 updated updated 2151 2152 # Clean up after the test. 2153 statement ok 2154 DROP TABLE f, e, d, c, b, a; 2155 2156 subtest UpdateCascade_Multi 2157 # Ensures that the cascader can be reused. See #21563. 2158 2159 statement ok 2160 CREATE TABLE a ( 2161 id INT PRIMARY KEY 2162 ); 2163 CREATE TABLE b ( 2164 id INT PRIMARY KEY 2165 ,a_id INT REFERENCES a ON UPDATE CASCADE 2166 ) 2167 2168 statement ok 2169 INSERT INTO a VALUES (1), (2), (3); 2170 INSERT INTO b VALUES (1, 1), (2, NULL), (3, 2), (4, 1), (5, NULL); 2171 2172 statement ok 2173 UPDATE a SET id = id + 10; 2174 2175 query II rowsort 2176 SELECT id, a_id FROM b; 2177 ---- 2178 1 11 2179 2 NULL 2180 3 12 2181 4 11 2182 5 NULL 2183 2184 # Clean up. 2185 statement ok 2186 DROP TABLE b, a; 2187 2188 subtest UpdateCascade_WithChecks 2189 ### Check constraints on 3 levels, with each one being more restrictive. 2190 # A 2191 # | 2192 # B 2193 # | 2194 # C 2195 2196 statement ok 2197 CREATE TABLE a ( 2198 id INT PRIMARY KEY 2199 ); 2200 CREATE TABLE b ( 2201 id INT PRIMARY KEY REFERENCES a ON UPDATE CASCADE 2202 ,CONSTRAINT less_than_1000 CHECK (id < 1000) 2203 ); 2204 CREATE TABLE c ( 2205 id INT PRIMARY KEY REFERENCES b ON UPDATE CASCADE 2206 ,CONSTRAINT less_than_100 CHECK (id < 100) 2207 ,CONSTRAINT no_99 CHECK (id != 99) 2208 ); 2209 2210 statement ok 2211 INSERT INTO a VALUES (1), (2), (3); 2212 INSERT INTO b VALUES (1), (2); 2213 INSERT INTO c VALUES (1); 2214 2215 # Perform a standard cascading update. 2216 statement ok 2217 UPDATE a SET id = id*10; 2218 2219 query TI rowsort 2220 SELECT name, id FROM ( 2221 SELECT 'a' AS name, id FROM a 2222 UNION ALL 2223 SELECT 'b' AS name, id FROM b 2224 UNION ALL 2225 SELECT 'c' AS name, id FROM c 2226 ) 2227 ORDER BY name, id 2228 ; 2229 ---- 2230 a 10 2231 a 20 2232 a 30 2233 b 10 2234 b 20 2235 c 10 2236 2237 # Perform another cascading update that should fail c.less_than_100. 2238 statement error pq: failed to satisfy CHECK constraint \(id < 100:::INT8\) 2239 UPDATE a SET id = id*10; 2240 2241 # Perform another cascading update that should fail b.less_than_1000 or 2242 # c.less_than_100. The order of which check fails first is not deterministic. 2243 statement error pq: failed to satisfy CHECK constraint \(id < (100|1000):::INT8\) 2244 UPDATE a SET id = id*1000; 2245 2246 # Perform another cascading update that should fail b.less_than_1000. 2247 statement error pq: failed to satisfy CHECK constraint \(id < 1000:::INT8\) 2248 UPDATE a SET id = id*1000 WHERE id > 10; 2249 2250 # And check another direct cascading constraint c.no_99. 2251 statement error pq: failed to satisfy CHECK constraint \(id != 99:::INT8\) 2252 UPDATE a SET id = 99 WHERE id = 10; 2253 2254 # But it should still be possible to cascade an update that doesn't hit c. 2255 # First check against c.no_99. 2256 statement ok 2257 UPDATE a SET id = 99 WHERE id = 20; 2258 2259 # And for c.less_then_100. 2260 statement ok 2261 UPDATE a SET id = 999 WHERE id = 99; 2262 2263 # And update a value that isn't cascaded at all. 2264 statement ok 2265 UPDATE a SET id = 100000 WHERE id = 30; 2266 2267 query TI rowsort 2268 SELECT name, id FROM ( 2269 SELECT 'a' AS name, id FROM a 2270 UNION ALL 2271 SELECT 'b' AS name, id FROM b 2272 UNION ALL 2273 SELECT 'c' AS name, id FROM c 2274 ) 2275 ORDER BY name, id 2276 ; 2277 ---- 2278 a 10 2279 a 999 2280 a 100000 2281 b 10 2282 b 999 2283 c 10 2284 2285 # Clean up. 2286 statement ok 2287 DROP TABLE c, b, a; 2288 2289 subtest UpdateCascade_WithChecksMultiColumn 2290 ### Check constraints on 3 levels using multi-column constraints. 2291 # A 2292 # | 2293 # B 2294 # | 2295 # C 2296 2297 statement ok 2298 CREATE TABLE a ( 2299 id INT PRIMARY KEY 2300 ); 2301 CREATE TABLE b ( 2302 id1 INT PRIMARY KEY REFERENCES a ON UPDATE CASCADE 2303 ,id2 INT UNIQUE NOT NULL REFERENCES a ON UPDATE CASCADE 2304 ,CONSTRAINT less_than_1000 CHECK (id1 + id2 < 1000) 2305 ); 2306 CREATE TABLE c ( 2307 id1 INT PRIMARY KEY REFERENCES b(id1) ON UPDATE CASCADE 2308 ,id2 INT UNIQUE NOT NULL REFERENCES b(id2) ON UPDATE CASCADE 2309 ,CONSTRAINT less_than_100 CHECK (id1 + id2 < 100) 2310 ); 2311 2312 statement ok 2313 INSERT INTO a VALUES (1), (2), (3), (4), (5); 2314 INSERT INTO b VALUES (1, 1), (2, 2), (3, 4); 2315 INSERT INTO c VALUES (2, 1), (1, 2); 2316 2317 # Perform a standard cascading update. 2318 statement ok 2319 UPDATE a SET id = id*10; 2320 2321 query TII rowsort 2322 SELECT name, id1, id2 FROM ( 2323 SELECT 'a' AS name, id AS id1, 0 AS id2 FROM a 2324 UNION ALL 2325 SELECT 'b' AS name, id1, id2 FROM b 2326 UNION ALL 2327 SELECT 'c' AS name, id1, id2 FROM c 2328 ) ORDER BY name, id1, id2 2329 ; 2330 ---- 2331 a 10 0 2332 a 20 0 2333 a 30 0 2334 a 40 0 2335 a 50 0 2336 b 10 10 2337 b 20 20 2338 b 30 40 2339 c 10 20 2340 c 20 10 2341 2342 # Try to update one value to fail c.less_than_100 2343 statement error pq: failed to satisfy CHECK constraint \(\(id1 \+ id2\) < 100:::INT8\) 2344 UPDATE a SET id = id*10; 2345 2346 # Try to update one value to fail c.less_than_100 or c.less_than_1000 2347 statement error pq: failed to satisfy CHECK constraint \(\(id1 \+ id2\) < 100:::INT8\) 2348 UPDATE a SET id = id*10; 2349 2350 # Try to update one value to fail c.less_than_100 2351 statement error pq: failed to satisfy CHECK constraint \(\(id1 \+ id2\) < 1000:::INT8\) 2352 UPDATE a SET id = 1000 WHERE id = 30; 2353 2354 statement error pq: failed to satisfy CHECK constraint \(\(id1 \+ id2\) < 1000:::INT8\) 2355 UPDATE a SET id = 1000 WHERE id = 40; 2356 2357 # Update a value that would fail the check if it was cascaded, but wasn't. 2358 statement ok 2359 UPDATE a SET id = 100000 WHERE id = 50; 2360 2361 # Clean up. 2362 statement ok 2363 DROP TABLE c, b, a; 2364 2365 subtest DeleteSetNull_Basic1 2366 ### Basic Delete Set Null 2367 # a 2368 # // \\ 2369 # / | | \ 2370 # b1 b2 b3 b4 2371 2372 statement ok 2373 CREATE TABLE a ( 2374 id STRING PRIMARY KEY 2375 ); 2376 CREATE TABLE b1 ( 2377 id STRING PRIMARY KEY 2378 ,delete_set_null STRING REFERENCES a ON DELETE SET NULL 2379 ); 2380 CREATE TABLE b2 ( 2381 id STRING PRIMARY KEY 2382 ,delete_set_null STRING REFERENCES a ON DELETE SET NULL 2383 ); 2384 CREATE TABLE b3 ( 2385 id STRING PRIMARY KEY 2386 ,delete_set_null STRING REFERENCES a ON DELETE SET NULL 2387 ); 2388 CREATE TABLE b4 ( 2389 id STRING PRIMARY KEY 2390 ,delete_set_null STRING REFERENCES a ON DELETE SET NULL 2391 ); 2392 2393 statement ok 2394 INSERT INTO a VALUES ('delete_me'), ('untouched'); 2395 INSERT INTO b1 VALUES ('b1-pk1', 'untouched'), ('b1-pk2', 'untouched'); 2396 INSERT INTO b2 VALUES ('b2-pk1', 'untouched'), ('b2-pk2', 'delete_me'); 2397 INSERT INTO b3 VALUES ('b3-pk1', 'delete_me'), ('b3-pk2', 'untouched'); 2398 INSERT INTO b4 VALUES ('b4-pk1', 'delete_me'), ('b4-pk2', 'delete_me'); 2399 2400 # ON DELETE CASCADE 2401 statement ok 2402 DELETE FROM a WHERE id = 'delete_me'; 2403 2404 query TT rowsort 2405 SELECT id, delete_set_null FROM b1 2406 UNION ALL 2407 SELECT id, delete_set_null FROM b2 2408 UNION ALL 2409 SELECT id, delete_set_null FROM b3 2410 UNION ALL 2411 SELECT id, delete_set_null FROM b4 2412 ; 2413 ---- 2414 b1-pk1 untouched 2415 b1-pk2 untouched 2416 b2-pk1 untouched 2417 b2-pk2 NULL 2418 b3-pk1 NULL 2419 b3-pk2 untouched 2420 b4-pk1 NULL 2421 b4-pk2 NULL 2422 2423 # Clean up after the test. 2424 statement ok 2425 DROP TABLE b4, b3, b2, b1, a; 2426 2427 subtest DeleteSetNull_Basic2 2428 ### Basic Delete Set Null 2429 # a 2430 # / \ 2431 # b1 b2 2432 # / \ \ 2433 # c1 c2 c3 2434 2435 statement ok 2436 CREATE TABLE a ( 2437 id STRING PRIMARY KEY 2438 ); 2439 CREATE TABLE b1 ( 2440 id STRING PRIMARY KEY 2441 ,delete_cascade STRING NOT NULL REFERENCES a ON DELETE CASCADE 2442 ); 2443 CREATE TABLE b2 ( 2444 id STRING PRIMARY KEY 2445 ,delete_cascade STRING NOT NULL REFERENCES a ON DELETE CASCADE 2446 ); 2447 CREATE TABLE c1 ( 2448 id STRING PRIMARY KEY 2449 ,delete_set_null STRING REFERENCES b1 ON DELETE SET NULL 2450 ); 2451 CREATE TABLE c2 ( 2452 id STRING PRIMARY KEY 2453 ,delete_set_null STRING REFERENCES b1 ON DELETE SET NULL 2454 ); 2455 CREATE TABLE c3 ( 2456 id STRING PRIMARY KEY 2457 ,delete_set_null STRING REFERENCES b2 ON DELETE SET NULL 2458 ); 2459 2460 statement ok 2461 INSERT INTO a VALUES ('a-pk1'); 2462 INSERT INTO b1 VALUES ('b1-pk1', 'a-pk1'), ('b1-pk2', 'a-pk1'); 2463 INSERT INTO b2 VALUES ('b2-pk1', 'a-pk1'), ('b2-pk2', 'a-pk1'); 2464 INSERT INTO c1 VALUES 2465 ('c1-pk1-b1-pk1', 'b1-pk1') 2466 ,('c1-pk2-b1-pk1', 'b1-pk1') 2467 ,('c1-pk3-b1-pk2', 'b1-pk2') 2468 ,('c1-pk4-b1-pk2', 'b1-pk2') 2469 ; 2470 INSERT INTO c2 VALUES 2471 ('c2-pk1-b1-pk1', 'b1-pk1') 2472 ,('c2-pk2-b1-pk1', 'b1-pk1') 2473 ,('c2-pk3-b1-pk2', 'b1-pk2') 2474 ,('c2-pk4-b1-pk2', 'b1-pk2') 2475 ; 2476 INSERT INTO c3 VALUES 2477 ('c3-pk1-b2-pk1', 'b2-pk1') 2478 ,('c3-pk2-b2-pk1', 'b2-pk1') 2479 ,('c3-pk3-b2-pk2', 'b2-pk2') 2480 ,('c3-pk4-b2-pk2', 'b2-pk2') 2481 ; 2482 2483 # This query expects to cascade the deletion in a into b1 and b2, but not into 2484 # the c tables which have ON DELETE SET NULL instead. 2485 statement ok 2486 DELETE FROM a WHERE id = 'a-pk1'; 2487 2488 query TT rowsort 2489 SELECT id, 'empty' FROM a 2490 UNION ALL 2491 SELECT id, delete_cascade FROM b1 2492 UNION ALL 2493 SELECT id, delete_cascade FROM b2 2494 UNION ALL 2495 SELECT id, delete_set_null FROM c1 2496 UNION ALL 2497 SELECT id, delete_set_null FROM c2 2498 UNION ALL 2499 SELECT id, delete_set_null FROM c3 2500 ; 2501 ---- 2502 c1-pk1-b1-pk1 NULL 2503 c1-pk2-b1-pk1 NULL 2504 c1-pk3-b1-pk2 NULL 2505 c1-pk4-b1-pk2 NULL 2506 c2-pk1-b1-pk1 NULL 2507 c2-pk2-b1-pk1 NULL 2508 c2-pk3-b1-pk2 NULL 2509 c2-pk4-b1-pk2 NULL 2510 c3-pk1-b2-pk1 NULL 2511 c3-pk2-b2-pk1 NULL 2512 c3-pk3-b2-pk2 NULL 2513 c3-pk4-b2-pk2 NULL 2514 2515 statement ok 2516 TRUNCATE c3, c2, c1, b2, b1, a; 2517 2518 # Clean up after the test. 2519 statement ok 2520 DROP TABLE c3, c2, c1, b2, b1, a; 2521 2522 subtest DeleteSetNull_ToUpdateCascade 2523 ### Cascade a delete in table a, to set null in table b, to an on update cascade 2524 # of that null into table c 2525 # a 2526 # | 2527 # b 2528 # | 2529 # c 2530 2531 statement ok 2532 CREATE TABLE a ( 2533 id STRING PRIMARY KEY 2534 ); 2535 CREATE TABLE b ( 2536 id STRING PRIMARY KEY 2537 ,a_id STRING UNIQUE REFERENCES a ON DELETE SET NULL 2538 ); 2539 CREATE TABLE c ( 2540 id STRING PRIMARY KEY 2541 ,b_a_id STRING REFERENCES b(a_id) ON UPDATE CASCADE 2542 ); 2543 2544 statement oK 2545 INSERT INTO a VALUES ('delete-me'), ('untouched'); 2546 INSERT INTO b VALUES ('b1', 'delete-me'), ('b2', 'untouched'); 2547 INSERT INTO c VALUES 2548 ('c1-b1', 'delete-me') 2549 ,('c2-b1', 'delete-me') 2550 ,('c3-b2', 'untouched') 2551 ,('c4-b2', 'untouched') 2552 ; 2553 2554 statement ok 2555 DELETE FROM a WHERE id = 'delete-me'; 2556 2557 query I 2558 SELECT count(*) FROM a; 2559 ---- 2560 1 2561 2562 query TT rowsort 2563 SELECT id, a_id FROM b 2564 UNION ALL 2565 SELECT id, b_a_id FROM c 2566 ; 2567 ---- 2568 b1 NULL 2569 b2 untouched 2570 c1-b1 NULL 2571 c2-b1 NULL 2572 c3-b2 untouched 2573 c4-b2 untouched 2574 2575 # Clean up after the test. 2576 statement ok 2577 DROP TABLE c, b, a; 2578 2579 subtest DeleteSetNull_ToUpdateCascadeNotNull 2580 ### Cascade a delete in table a, to set null in table b, to an on update cascade 2581 # of that null into table c, but table c's column is NOT NULL 2582 # a 2583 # | 2584 # b 2585 # | 2586 # c 2587 2588 statement ok 2589 CREATE TABLE a ( 2590 id STRING PRIMARY KEY 2591 ); 2592 CREATE TABLE b ( 2593 id STRING PRIMARY KEY 2594 ,a_id STRING UNIQUE REFERENCES a ON DELETE SET NULL 2595 ); 2596 CREATE TABLE c ( 2597 id STRING PRIMARY KEY 2598 ,b_a_id STRING NOT NULL REFERENCES b(a_id) ON UPDATE CASCADE 2599 ); 2600 2601 statement oK 2602 INSERT INTO a VALUES ('delete-me'), ('untouched'); 2603 INSERT INTO b VALUES ('b1', 'delete-me'), ('b2', 'untouched'); 2604 INSERT INTO c VALUES 2605 ('c1-b1', 'delete-me') 2606 ,('c2-b1', 'delete-me') 2607 ,('c3-b2', 'untouched') 2608 ,('c4-b2', 'untouched') 2609 ; 2610 2611 statement error pq: null value in column "b_a_id" violates not-null constraint 2612 DELETE FROM a WHERE id = 'delete-me'; 2613 2614 # Clean up after the test. 2615 statement ok 2616 DROP TABLE c, b, a; 2617 2618 subtest UpdateSetNull_Basic1 2619 ### Basic Update Set Null 2620 # a 2621 # // \\ 2622 # / | | \ 2623 # b1 b2 b3 b4 2624 2625 statement ok 2626 CREATE TABLE a ( 2627 id STRING PRIMARY KEY 2628 ); 2629 CREATE TABLE b1 ( 2630 id STRING PRIMARY KEY 2631 ,update_set_null STRING REFERENCES a ON UPDATE SET NULL 2632 ); 2633 CREATE TABLE b2 ( 2634 id STRING PRIMARY KEY 2635 ,update_set_null STRING REFERENCES a ON UPDATE SET NULL 2636 ); 2637 CREATE TABLE b3 ( 2638 id STRING PRIMARY KEY 2639 ,update_set_null STRING REFERENCES a ON UPDATE SET NULL 2640 ); 2641 CREATE TABLE b4 ( 2642 id STRING PRIMARY KEY 2643 ,update_set_null STRING REFERENCES a ON UPDATE SET NULL 2644 ); 2645 2646 statement ok 2647 INSERT INTO a VALUES ('original'), ('untouched'); 2648 INSERT INTO b1 VALUES ('b1-pk1', 'untouched'), ('b1-pk2', 'untouched'); 2649 INSERT INTO b2 VALUES ('b2-pk1', 'untouched'), ('b2-pk2', 'original'); 2650 INSERT INTO b3 VALUES ('b3-pk1', 'original'), ('b3-pk2', 'untouched'); 2651 INSERT INTO b3 VALUES ('b4-pk1', 'original'), ('b4-pk2', 'original'); 2652 2653 # ON UPDATE CASCADE 2654 statement ok 2655 UPDATE a SET id = 'updated' WHERE id = 'original'; 2656 2657 query TT rowsort 2658 SELECT id, update_set_null FROM b1 2659 UNION ALL 2660 SELECT id, update_set_null FROM b2 2661 UNION ALL 2662 SELECT id, update_set_null FROM b3 2663 UNION ALL 2664 SELECT id, update_set_null FROM b4 2665 ; 2666 ---- 2667 b1-pk1 untouched 2668 b1-pk2 untouched 2669 b2-pk1 untouched 2670 b2-pk2 NULL 2671 b3-pk1 NULL 2672 b3-pk2 untouched 2673 b4-pk1 NULL 2674 b4-pk2 NULL 2675 2676 # Clean up after the test. 2677 statement ok 2678 DROP TABLE b4, b3, b2, b1, a; 2679 2680 subtest UpdateSetNull_Basic2 2681 ### Basic Update Set Null 2682 # a 2683 # / \ 2684 # b1 b2 2685 # / \ \ 2686 # c1 c2 c3 2687 2688 statement ok 2689 CREATE TABLE a ( 2690 id STRING PRIMARY KEY 2691 ); 2692 CREATE TABLE b1 ( 2693 id STRING PRIMARY KEY 2694 ,update_cascade STRING UNIQUE NOT NULL REFERENCES a ON UPDATE CASCADE 2695 ); 2696 CREATE TABLE b2 ( 2697 id STRING PRIMARY KEY 2698 ,update_cascade STRING UNIQUE NOT NULL REFERENCES a ON UPDATE CASCADE 2699 ); 2700 CREATE TABLE c1 ( 2701 id STRING PRIMARY KEY 2702 ,update_set_null STRING REFERENCES b1(update_cascade) ON UPDATE SET NULL 2703 ); 2704 CREATE TABLE c2 ( 2705 id STRING PRIMARY KEY 2706 ,update_set_null STRING REFERENCES b1(update_cascade) ON UPDATE SET NULL 2707 ); 2708 CREATE TABLE c3 ( 2709 id STRING PRIMARY KEY 2710 ,update_set_null STRING REFERENCES b2(update_cascade) ON UPDATE SET NULL 2711 ); 2712 2713 statement ok 2714 INSERT INTO a VALUES ('original'), ('untouched'); 2715 INSERT INTO b1 VALUES ('b1-pk1', 'original'), ('b1-pk2', 'untouched'); 2716 INSERT INTO b2 VALUES ('b2-pk1', 'original'), ('b2-pk2', 'untouched'); 2717 INSERT INTO c1 VALUES 2718 ('c1-pk1-b1-pk1', 'original') 2719 ,('c1-pk2-b1-pk1', 'original') 2720 ,('c1-pk3-b1-pk2', 'untouched') 2721 ,('c1-pk4-b1-pk2', 'untouched') 2722 ; 2723 INSERT INTO c2 VALUES 2724 ('c2-pk1-b1-pk1', 'original') 2725 ,('c2-pk2-b1-pk1', 'original') 2726 ,('c2-pk3-b1-pk2', 'untouched') 2727 ,('c2-pk4-b1-pk2', 'untouched') 2728 ; 2729 INSERT INTO c3 VALUES 2730 ('c3-pk1-b2-pk1', 'original') 2731 ,('c3-pk2-b2-pk1', 'original') 2732 ,('c3-pk3-b2-pk2', 'untouched') 2733 ,('c3-pk4-b2-pk2', 'untouched') 2734 ; 2735 2736 # ON UPDATE CASCADE 2737 statement ok 2738 UPDATE a SET id = 'updated' WHERE id = 'original'; 2739 2740 query TT rowsort 2741 SELECT id, update_cascade FROM b1 2742 UNION ALL 2743 SELECT id, update_cascade FROM b2 2744 UNION ALL 2745 SELECT id, update_set_null FROM c1 2746 UNION ALL 2747 SELECT id, update_set_null FROM c2 2748 UNION ALL 2749 SELECT id, update_set_null FROM c3 2750 ; 2751 ---- 2752 b1-pk1 updated 2753 b1-pk2 untouched 2754 b2-pk1 updated 2755 b2-pk2 untouched 2756 c1-pk1-b1-pk1 NULL 2757 c1-pk2-b1-pk1 NULL 2758 c1-pk3-b1-pk2 untouched 2759 c1-pk4-b1-pk2 untouched 2760 c2-pk1-b1-pk1 NULL 2761 c2-pk2-b1-pk1 NULL 2762 c2-pk3-b1-pk2 untouched 2763 c2-pk4-b1-pk2 untouched 2764 c3-pk1-b2-pk1 NULL 2765 c3-pk2-b2-pk1 NULL 2766 c3-pk3-b2-pk2 untouched 2767 c3-pk4-b2-pk2 untouched 2768 2769 # Clean up after the test. 2770 statement ok 2771 DROP TABLE c3, c2, c1, b2, b1, a; 2772 2773 subtest UpdateSetNull_ToUpdateCascade 2774 ### Cascade an update in table a, to set null in table b, to an on update 2775 # cascade of that null into table c. 2776 # a 2777 # | 2778 # b 2779 # | 2780 # c 2781 2782 statement ok 2783 CREATE TABLE a ( 2784 id STRING PRIMARY KEY 2785 ); 2786 CREATE TABLE b ( 2787 id STRING PRIMARY KEY 2788 ,a_id STRING UNIQUE REFERENCES a ON UPDATE SET NULL 2789 ); 2790 CREATE TABLE c ( 2791 id STRING PRIMARY KEY 2792 ,b_a_id STRING REFERENCES b(a_id) ON UPDATE CASCADE 2793 ); 2794 2795 statement oK 2796 INSERT INTO a VALUES ('original'), ('untouched'); 2797 INSERT INTO b VALUES ('b1', 'original'), ('b2', 'untouched'); 2798 INSERT INTO c VALUES 2799 ('c1-b1', 'original') 2800 ,('c2-b1', 'original') 2801 ,('c3-b2', 'untouched') 2802 ,('c4-b2', 'untouched') 2803 ; 2804 2805 statement ok 2806 UPDATE a SET id = 'updated' WHERE id = 'original'; 2807 2808 query TT rowsort 2809 SELECT id, a_id FROM b 2810 UNION ALL 2811 SELECT id, b_a_id FROM c 2812 ---- 2813 b1 NULL 2814 b2 untouched 2815 c1-b1 NULL 2816 c2-b1 NULL 2817 c3-b2 untouched 2818 c4-b2 untouched 2819 2820 # Clean up after the test. 2821 statement ok 2822 DROP TABLE c, b, a; 2823 2824 subtest UpdateSetNull_ToUpdateCascadeNotNull 2825 ### Cascade a delete in table a, to set null in table b, to an on update cascade 2826 # of that null into table c, but table c's column is NOT NULL. 2827 # a 2828 # | 2829 # b 2830 # | 2831 # c 2832 2833 statement ok 2834 CREATE TABLE a ( 2835 id STRING PRIMARY KEY 2836 ); 2837 CREATE TABLE b ( 2838 id STRING PRIMARY KEY 2839 ,a_id STRING UNIQUE REFERENCES a ON UPDATE SET NULL 2840 ); 2841 CREATE TABLE c ( 2842 id STRING PRIMARY KEY 2843 ,b_a_id STRING NOT NULL REFERENCES b(a_id) ON UPDATE CASCADE 2844 ); 2845 2846 statement oK 2847 INSERT INTO a VALUES ('original'), ('untouched'); 2848 INSERT INTO b VALUES ('b1', 'original'), ('b2', 'untouched'); 2849 INSERT INTO c VALUES 2850 ('c1-b1', 'original') 2851 ,('c2-b1', 'original') 2852 ,('c3-b2', 'untouched') 2853 ,('c4-b2', 'untouched') 2854 ; 2855 2856 statement error null value in column "b_a_id" violates not-null constraint 2857 UPDATE a SET id = 'updated' WHERE id = 'original'; 2858 2859 # Clean up after the test. 2860 statement ok 2861 DROP TABLE c, b, a; 2862 2863 ############## 2864 2865 subtest DeleteSetDefault_Basic1 2866 ### Basic Delete Set Default 2867 # a 2868 # // \\ 2869 # / | | \ 2870 # b1 b2 b3 b4 2871 2872 statement ok 2873 CREATE TABLE a ( 2874 id STRING PRIMARY KEY 2875 ); 2876 CREATE TABLE b1 ( 2877 id STRING PRIMARY KEY 2878 ,delete_set_default STRING DEFAULT 'b1-default' REFERENCES a ON DELETE SET DEFAULT 2879 ); 2880 CREATE TABLE b2 ( 2881 id STRING PRIMARY KEY 2882 ,delete_set_default STRING DEFAULT 'b2-default' REFERENCES a ON DELETE SET DEFAULT 2883 ); 2884 CREATE TABLE b3 ( 2885 id STRING PRIMARY KEY 2886 ,delete_set_default STRING DEFAULT 'b3-default' REFERENCES a ON DELETE SET DEFAULT 2887 ); 2888 CREATE TABLE b4 ( 2889 id STRING PRIMARY KEY 2890 ,delete_set_default STRING DEFAULT 'b4-default' REFERENCES a ON DELETE SET DEFAULT 2891 ); 2892 2893 statement ok 2894 INSERT INTO a VALUES ('delete_me'), ('untouched'), ('b1-default'), ('b2-default'), ('b3-default'), ('b4-default'); 2895 INSERT INTO b1 VALUES ('b1-pk1', 'untouched'), ('b1-pk2', 'untouched'); 2896 INSERT INTO b2 VALUES ('b2-pk1', 'untouched'), ('b2-pk2', 'delete_me'); 2897 INSERT INTO b3 VALUES ('b3-pk1', 'delete_me'), ('b3-pk2', 'untouched'); 2898 INSERT INTO b4 VALUES ('b4-pk1', 'delete_me'), ('b4-pk2', 'delete_me'); 2899 2900 # ON DELETE CASCADE 2901 statement ok 2902 DELETE FROM a WHERE id = 'delete_me'; 2903 2904 query TT rowsort 2905 SELECT id, delete_set_default FROM b1 2906 UNION ALL 2907 SELECT id, delete_set_default FROM b2 2908 UNION ALL 2909 SELECT id, delete_set_default FROM b3 2910 UNION ALL 2911 SELECT id, delete_set_default FROM b4 2912 ; 2913 ---- 2914 b1-pk1 untouched 2915 b1-pk2 untouched 2916 b2-pk1 untouched 2917 b2-pk2 b2-default 2918 b3-pk1 b3-default 2919 b3-pk2 untouched 2920 b4-pk1 b4-default 2921 b4-pk2 b4-default 2922 2923 # Clean up after the test. 2924 statement ok 2925 DROP TABLE b4, b3, b2, b1, a; 2926 2927 subtest DeleteSetDefault_Basic1_WrongDefault 2928 ### The same test as DeleteSetDefault_Basic1 but a default is set to a value 2929 # that does not exist in the table above it. 2930 # a 2931 # // \\ 2932 # / | | \ 2933 # b1 b2 b3 b4 2934 2935 statement ok 2936 CREATE TABLE a ( 2937 id STRING PRIMARY KEY 2938 ); 2939 CREATE TABLE b1 ( 2940 id STRING PRIMARY KEY 2941 ,delete_set_default STRING DEFAULT 'b1-def' REFERENCES a ON DELETE SET DEFAULT 2942 ); 2943 CREATE TABLE b2 ( 2944 id STRING PRIMARY KEY 2945 ,delete_set_default STRING DEFAULT 'b2-def' REFERENCES a ON DELETE SET DEFAULT 2946 ); 2947 CREATE TABLE b3 ( 2948 id STRING PRIMARY KEY 2949 ,delete_set_default STRING DEFAULT 'missing' REFERENCES a ON DELETE SET DEFAULT 2950 ); 2951 CREATE TABLE b4 ( 2952 id STRING PRIMARY KEY 2953 ,delete_set_default STRING DEFAULT 'b4-def' REFERENCES a ON DELETE SET DEFAULT 2954 ); 2955 2956 statement ok 2957 INSERT INTO a VALUES ('delete_me'), ('untouched'), ('b1-def'), ('b2-def'), ('b3-def'), ('b4-def'); 2958 INSERT INTO b1 VALUES ('b1-pk1', 'untouched'), ('b1-pk2', 'untouched'); 2959 INSERT INTO b2 VALUES ('b2-pk1', 'untouched'), ('b2-pk2', 'delete_me'); 2960 INSERT INTO b3 VALUES ('b3-pk1', 'delete_me'), ('b3-pk2', 'untouched'); 2961 INSERT INTO b4 VALUES ('b4-pk1', 'delete_me'), ('b4-pk2', 'delete_me'); 2962 2963 # ON DELETE CASCADE, which should fail since the value 'missing' is not in a. 2964 statement error pq: update on table "b3" violates foreign key constraint "fk_delete_set_default_ref_a"\nDETAIL: Key \(delete_set_default\)=\('missing'\) is not present in table "a"\. 2965 DELETE FROM a WHERE id = 'delete_me'; 2966 2967 # Clean up after the test. 2968 statement ok 2969 DROP TABLE b4, b3, b2, b1, a; 2970 2971 subtest DeleteSetDefault_Basic2 2972 ### Basic Delete Set Null via an ON DELETE CASCADE 2973 # a 2974 # / \ 2975 # b1 b2 2976 # / \ \ 2977 # c1 c2 c3 2978 2979 statement ok 2980 CREATE TABLE a ( 2981 id STRING PRIMARY KEY 2982 ); 2983 CREATE TABLE b1 ( 2984 id STRING PRIMARY KEY 2985 ,delete_cascade STRING NOT NULL REFERENCES a ON DELETE CASCADE 2986 ); 2987 CREATE TABLE b2 ( 2988 id STRING PRIMARY KEY 2989 ,delete_cascade STRING NOT NULL REFERENCES a ON DELETE CASCADE 2990 ); 2991 CREATE TABLE c1 ( 2992 id STRING PRIMARY KEY 2993 ,delete_set_default STRING DEFAULT 'b1-default' REFERENCES b1 ON DELETE SET DEFAULT 2994 ); 2995 CREATE TABLE c2 ( 2996 id STRING PRIMARY KEY 2997 ,delete_set_default STRING DEFAULT 'b1-default' REFERENCES b1 ON DELETE SET DEFAULT 2998 ); 2999 CREATE TABLE c3 ( 3000 id STRING PRIMARY KEY 3001 ,delete_set_default STRING DEFAULT 'b2-default' REFERENCES b2 ON DELETE SET DEFAULT 3002 ); 3003 3004 statement ok 3005 INSERT INTO a VALUES ('a-pk1'), ('a-default'); 3006 INSERT INTO b1 VALUES ('b1-pk1', 'a-pk1'), ('b1-pk2', 'a-pk1'), ('b1-default', 'a-default'); 3007 INSERT INTO b2 VALUES ('b2-pk1', 'a-pk1'), ('b2-pk2', 'a-pk1'), ('b2-default', 'a-default'); 3008 INSERT INTO c1 VALUES 3009 ('c1-pk1-b1-pk1', 'b1-pk1') 3010 ,('c1-pk2-b1-pk1', 'b1-pk1') 3011 ,('c1-pk3-b1-pk2', 'b1-pk2') 3012 ,('c1-pk4-b1-pk2', 'b1-pk2') 3013 ; 3014 INSERT INTO c2 VALUES 3015 ('c2-pk1-b1-pk1', 'b1-pk1') 3016 ,('c2-pk2-b1-pk1', 'b1-pk1') 3017 ,('c2-pk3-b1-pk2', 'b1-pk2') 3018 ,('c2-pk4-b1-pk2', 'b1-pk2') 3019 ; 3020 INSERT INTO c3 VALUES 3021 ('c3-pk1-b2-pk1', 'b2-pk1') 3022 ,('c3-pk2-b2-pk1', 'b2-pk1') 3023 ,('c3-pk3-b2-pk2', 'b2-pk2') 3024 ,('c3-pk4-b2-pk2', 'b2-pk2') 3025 ; 3026 3027 # This query expects to cascade the deletion in a into b1 and b2, but not into 3028 # the c tables which have ON DELETE SET DEFAULT instead. 3029 statement ok 3030 DELETE FROM a WHERE id = 'a-pk1'; 3031 3032 query TT rowsort 3033 SELECT id, 'empty' FROM a 3034 UNION ALL 3035 SELECT id, delete_cascade FROM b1 3036 UNION ALL 3037 SELECT id, delete_cascade FROM b2 3038 UNION ALL 3039 SELECT id, delete_set_default FROM c1 3040 UNION ALL 3041 SELECT id, delete_set_default FROM c2 3042 UNION ALL 3043 SELECT id, delete_set_default FROM c3 3044 ; 3045 ---- 3046 a-default empty 3047 b1-default a-default 3048 b2-default a-default 3049 c1-pk1-b1-pk1 b1-default 3050 c1-pk2-b1-pk1 b1-default 3051 c1-pk3-b1-pk2 b1-default 3052 c1-pk4-b1-pk2 b1-default 3053 c2-pk1-b1-pk1 b1-default 3054 c2-pk2-b1-pk1 b1-default 3055 c2-pk3-b1-pk2 b1-default 3056 c2-pk4-b1-pk2 b1-default 3057 c3-pk1-b2-pk1 b2-default 3058 c3-pk2-b2-pk1 b2-default 3059 c3-pk3-b2-pk2 b2-default 3060 c3-pk4-b2-pk2 b2-default 3061 3062 statement ok 3063 TRUNCATE c3, c2, c1, b2, b1, a; 3064 3065 # Clean up after the test. 3066 statement ok 3067 DROP TABLE c3, c2, c1, b2, b1, a; 3068 3069 subtest DeleteSetDefault_Basic2_WrongDefault 3070 ### The same test as DeleteSetDefault_Basic2 but a default is set to a value 3071 # that does not exist in the table above it. 3072 # a 3073 # / \ 3074 # b1 b2 3075 # / \ \ 3076 # c1 c2 c3 3077 3078 statement ok 3079 CREATE TABLE a ( 3080 id STRING PRIMARY KEY 3081 ); 3082 CREATE TABLE b1 ( 3083 id STRING PRIMARY KEY 3084 ,delete_cascade STRING NOT NULL REFERENCES a ON DELETE CASCADE 3085 ); 3086 CREATE TABLE b2 ( 3087 id STRING PRIMARY KEY 3088 ,delete_cascade STRING NOT NULL REFERENCES a ON DELETE CASCADE 3089 ); 3090 CREATE TABLE c1 ( 3091 id STRING PRIMARY KEY 3092 ,delete_set_default STRING DEFAULT 'b1-default' REFERENCES b1 ON DELETE SET DEFAULT 3093 ); 3094 CREATE TABLE c2 ( 3095 id STRING PRIMARY KEY 3096 ,delete_set_default STRING DEFAULT 'missing' REFERENCES b1 ON DELETE SET DEFAULT 3097 ); 3098 CREATE TABLE c3 ( 3099 id STRING PRIMARY KEY 3100 ,delete_set_default STRING DEFAULT 'b2-default' REFERENCES b2 ON DELETE SET DEFAULT 3101 ); 3102 3103 statement ok 3104 INSERT INTO a VALUES ('a-pk1'), ('a-default'); 3105 INSERT INTO b1 VALUES ('b1-pk1', 'a-pk1'), ('b1-pk2', 'a-pk1'), ('b1-default', 'a-default'); 3106 INSERT INTO b2 VALUES ('b2-pk1', 'a-pk1'), ('b2-pk2', 'a-pk1'), ('b2-default', 'a-default'); 3107 INSERT INTO c1 VALUES 3108 ('c1-pk1-b1-pk1', 'b1-pk1') 3109 ,('c1-pk2-b1-pk1', 'b1-pk1') 3110 ,('c1-pk3-b1-pk2', 'b1-pk2') 3111 ,('c1-pk4-b1-pk2', 'b1-pk2') 3112 ; 3113 INSERT INTO c2 VALUES 3114 ('c2-pk1-b1-pk1', 'b1-pk1') 3115 ,('c2-pk2-b1-pk1', 'b1-pk1') 3116 ,('c2-pk3-b1-pk2', 'b1-pk2') 3117 ,('c2-pk4-b1-pk2', 'b1-pk2') 3118 ; 3119 INSERT INTO c3 VALUES 3120 ('c3-pk1-b2-pk1', 'b2-pk1') 3121 ,('c3-pk2-b2-pk1', 'b2-pk1') 3122 ,('c3-pk3-b2-pk2', 'b2-pk2') 3123 ,('c3-pk4-b2-pk2', 'b2-pk2') 3124 ; 3125 3126 # This query expects to cascade the deletion in a into b1 and b2, but not into 3127 # the c tables which have ON DELETE SET DEFAULT instead. And ultimately fail 3128 # since the default value 'missing' is not present in b1. 3129 statement error pq: update on table "c2" violates foreign key constraint "fk_delete_set_default_ref_b1"\nDETAIL: Key \(delete_set_default\)=\('missing'\) is not present in table "b1"\. 3130 DELETE FROM a WHERE id = 'a-pk1'; 3131 3132 # Clean up after the test. 3133 statement ok 3134 DROP TABLE c3, c2, c1, b2, b1, a; 3135 3136 subtest DeleteSetDefault_ToUpdateCascade 3137 ### Cascade a delete in table a, to a SET DEFAULT in table b, to an ON UPDATE 3138 # CASCADE of that default value into table c. 3139 # a 3140 # | 3141 # b 3142 # | 3143 # c 3144 3145 statement ok 3146 CREATE TABLE a ( 3147 id STRING PRIMARY KEY 3148 ); 3149 CREATE TABLE b ( 3150 id STRING PRIMARY KEY 3151 ,a_id STRING DEFAULT 'default' UNIQUE REFERENCES a ON DELETE SET DEFAULT 3152 ); 3153 CREATE TABLE c ( 3154 id STRING PRIMARY KEY 3155 ,b_a_id STRING REFERENCES b(a_id) ON UPDATE CASCADE 3156 ); 3157 3158 statement oK 3159 INSERT INTO a VALUES ('delete-me'), ('untouched'), ('default'); 3160 INSERT INTO b VALUES ('b1', 'delete-me'), ('b2', 'untouched'); 3161 INSERT INTO c VALUES 3162 ('c1-b1', 'delete-me') 3163 ,('c2-b1', 'delete-me') 3164 ,('c3-b2', 'untouched') 3165 ,('c4-b2', 'untouched') 3166 ; 3167 3168 statement ok 3169 DELETE FROM a WHERE id = 'delete-me'; 3170 3171 query T rowsort 3172 SELECT id FROM a; 3173 ---- 3174 default 3175 untouched 3176 3177 query TT rowsort 3178 SELECT id, a_id FROM b 3179 UNION ALL 3180 SELECT id, b_a_id FROM c 3181 ; 3182 ---- 3183 b1 default 3184 b2 untouched 3185 c1-b1 default 3186 c2-b1 default 3187 c3-b2 untouched 3188 c4-b2 untouched 3189 3190 # Clean up after the test. 3191 statement ok 3192 DROP TABLE c, b, a; 3193 3194 subtest DeleteSetDefault_ToUpdateCascade 3195 ### Cascade a delete in table a, to a SET DEFAULT in table b (of a NULL), to an 3196 # ON UPDATE CASCADE of that null into table c. 3197 # a 3198 # | 3199 # b 3200 # | 3201 # c 3202 3203 statement ok 3204 CREATE TABLE a ( 3205 id STRING PRIMARY KEY 3206 ); 3207 CREATE TABLE b ( 3208 id STRING PRIMARY KEY 3209 ,a_id STRING DEFAULT NULL UNIQUE REFERENCES a ON DELETE SET DEFAULT 3210 ); 3211 CREATE TABLE c ( 3212 id STRING PRIMARY KEY 3213 ,b_a_id STRING REFERENCES b(a_id) ON UPDATE CASCADE 3214 ); 3215 3216 statement oK 3217 INSERT INTO a VALUES ('delete-me'), ('untouched'); 3218 INSERT INTO b VALUES ('b1', 'delete-me'), ('b2', 'untouched'); 3219 INSERT INTO c VALUES 3220 ('c1-b1', 'delete-me') 3221 ,('c2-b1', 'delete-me') 3222 ,('c3-b2', 'untouched') 3223 ,('c4-b2', 'untouched') 3224 ; 3225 3226 # Cascade the delete in a to the SET DEFAULT in b to the CASCADE in c 3227 statement ok 3228 DELETE FROM a WHERE id = 'delete-me'; 3229 3230 query TT rowsort 3231 SELECT id, a_id FROM b 3232 UNION ALL 3233 SELECT id, b_a_id FROM c 3234 ; 3235 ---- 3236 b1 NULL 3237 b2 untouched 3238 c1-b1 NULL 3239 c2-b1 NULL 3240 c3-b2 untouched 3241 c4-b2 untouched 3242 3243 # Clean up after the test. 3244 statement ok 3245 DROP TABLE c, b, a; 3246 3247 subtest DeleteSetDefault_ToUpdateCascadeNotNull 3248 ### Cascade a delete in table a, to a SET DEFAULT in table b (of a NULL), to an 3249 # on update cascade of that null into table c, but table c's column is NOT NULL. 3250 # a 3251 # | 3252 # b 3253 # | 3254 # c 3255 3256 statement ok 3257 CREATE TABLE a ( 3258 id STRING PRIMARY KEY 3259 ); 3260 CREATE TABLE b ( 3261 id STRING PRIMARY KEY 3262 ,a_id STRING DEFAULT NULL UNIQUE REFERENCES a ON DELETE SET DEFAULT 3263 ); 3264 CREATE TABLE c ( 3265 id STRING PRIMARY KEY 3266 ,b_a_id STRING NOT NULL REFERENCES b(a_id) ON UPDATE CASCADE 3267 ); 3268 3269 statement oK 3270 INSERT INTO a VALUES ('delete-me'), ('untouched'); 3271 INSERT INTO b VALUES ('b1', 'delete-me'), ('b2', 'untouched'); 3272 INSERT INTO c VALUES 3273 ('c1-b1', 'delete-me') 3274 ,('c2-b1', 'delete-me') 3275 ,('c3-b2', 'untouched') 3276 ,('c4-b2', 'untouched') 3277 ; 3278 3279 # Cascade the delete in a to the SET DEFAULT in b to the CASCADE in c which 3280 # should violate the NOT NULL in c.b_a_id. 3281 statement error null value in column "b_a_id" violates not-null constraint 3282 DELETE FROM a WHERE id = 'delete-me'; 3283 3284 # Clean up after the test. 3285 statement ok 3286 DROP TABLE c, b, a; 3287 3288 subtest DefaultSetDefault_Unique 3289 ### Have a SET DEFAULT break a uniqueness constraint. 3290 # a 3291 # | 3292 # b 3293 3294 statement ok 3295 CREATE TABLE a ( 3296 id STRING PRIMARY KEY 3297 ); 3298 CREATE TABLE b ( 3299 id STRING PRIMARY KEY 3300 ,a_id STRING DEFAULT 'default' UNIQUE REFERENCES a ON DELETE SET DEFAULT 3301 ); 3302 3303 statement oK 3304 INSERT INTO a VALUES ('original'), ('default'); 3305 INSERT INTO b VALUES ('b1', 'original'), ('b2', 'default'); 3306 3307 statement error pq: duplicate key value \(a_id\)=\('default'\) violates unique constraint "b_a_id_key" 3308 DELETE FROM a WHERE id = 'original'; 3309 3310 # Clean up after the test. 3311 statement ok 3312 DROP TABLE b, a; 3313 3314 subtest UpdateSetDefault_Basic1 3315 ### Basic Update Set Default 3316 # a 3317 # // \\ 3318 # / | | \ 3319 # b1 b2 b3 b4 3320 3321 statement ok 3322 CREATE TABLE a ( 3323 id STRING PRIMARY KEY 3324 ); 3325 CREATE TABLE b1 ( 3326 id STRING PRIMARY KEY 3327 ,update_set_null STRING DEFAULT 'b1-default' REFERENCES a ON UPDATE SET DEFAULT 3328 ); 3329 CREATE TABLE b2 ( 3330 id STRING PRIMARY KEY 3331 ,update_set_null STRING DEFAULT 'b2-default' REFERENCES a ON UPDATE SET DEFAULT 3332 ); 3333 CREATE TABLE b3 ( 3334 id STRING PRIMARY KEY 3335 ,update_set_null STRING DEFAULT 'b3-default' REFERENCES a ON UPDATE SET DEFAULT 3336 ); 3337 CREATE TABLE b4 ( 3338 id STRING PRIMARY KEY 3339 ,update_set_null STRING DEFAULT 'b4-default' REFERENCES a ON UPDATE SET DEFAULT 3340 ); 3341 3342 statement ok 3343 INSERT INTO a VALUES ('original'), ('untouched'), ('b1-default'), ('b2-default'), ('b3-default'), ('b4-default'); 3344 INSERT INTO b1 VALUES ('b1-pk1', 'untouched'), ('b1-pk2', 'untouched'); 3345 INSERT INTO b2 VALUES ('b2-pk1', 'untouched'), ('b2-pk2', 'original'); 3346 INSERT INTO b3 VALUES ('b3-pk1', 'original'), ('b3-pk2', 'untouched'); 3347 INSERT INTO b3 VALUES ('b4-pk1', 'original'), ('b4-pk2', 'original'); 3348 3349 # ON UPDATE CASCADE 3350 statement ok 3351 UPDATE a SET id = 'updated' WHERE id = 'original'; 3352 3353 query TT rowsort 3354 SELECT id, update_set_null FROM b1 3355 UNION ALL 3356 SELECT id, update_set_null FROM b2 3357 UNION ALL 3358 SELECT id, update_set_null FROM b3 3359 UNION ALL 3360 SELECT id, update_set_null FROM b4 3361 ; 3362 ---- 3363 b1-pk1 untouched 3364 b1-pk2 untouched 3365 b2-pk1 untouched 3366 b2-pk2 b2-default 3367 b3-pk1 b3-default 3368 b3-pk2 untouched 3369 b4-pk1 b3-default 3370 b4-pk2 b3-default 3371 3372 # Clean up after the test. 3373 statement ok 3374 DROP TABLE b4, b3, b2, b1, a; 3375 3376 subtest UpdateSetDefault_Basic1_WrongDefault 3377 ### Basic Update Set Default 3378 # a 3379 # // \\ 3380 # / | | \ 3381 # b1 b2 b3 b4 3382 3383 statement ok 3384 CREATE TABLE a ( 3385 id STRING PRIMARY KEY 3386 ); 3387 CREATE TABLE b1 ( 3388 id STRING PRIMARY KEY 3389 ,update_set_null STRING DEFAULT 'b1-default' REFERENCES a ON UPDATE SET DEFAULT 3390 ); 3391 CREATE TABLE b2 ( 3392 id STRING PRIMARY KEY 3393 ,update_set_null STRING DEFAULT 'b2-default' REFERENCES a ON UPDATE SET DEFAULT 3394 ); 3395 CREATE TABLE b3 ( 3396 id STRING PRIMARY KEY 3397 ,update_set_null STRING DEFAULT 'missing' REFERENCES a ON UPDATE SET DEFAULT 3398 ); 3399 CREATE TABLE b4 ( 3400 id STRING PRIMARY KEY 3401 ,update_set_null STRING DEFAULT 'b4-default' REFERENCES a ON UPDATE SET DEFAULT 3402 ); 3403 3404 statement ok 3405 INSERT INTO a VALUES ('original'), ('untouched'), ('b1-default'), ('b2-default'), ('b3-default'), ('b4-default'); 3406 INSERT INTO b1 VALUES ('b1-pk1', 'untouched'), ('b1-pk2', 'untouched'); 3407 INSERT INTO b2 VALUES ('b2-pk1', 'untouched'), ('b2-pk2', 'original'); 3408 INSERT INTO b3 VALUES ('b3-pk1', 'original'), ('b3-pk2', 'untouched'); 3409 INSERT INTO b3 VALUES ('b4-pk1', 'original'), ('b4-pk2', 'original'); 3410 3411 # ON UPDATE CASCADE, which should fail since the value 'missing' is not in a. 3412 statement error update on table "b3" violates foreign key constraint "fk_update_set_null_ref_a"\nDETAIL: Key \(update_set_null\)=\('missing'\) is not present in table "a"\. 3413 UPDATE a SET id = 'updated' WHERE id = 'original'; 3414 3415 # Clean up after the test. 3416 statement ok 3417 DROP TABLE b4, b3, b2, b1, a; 3418 3419 subtest UpdateSetDefault_Basic2 3420 ### Basic UPDATE SET DEFAULT via an UPDATE CASCADE 3421 # a 3422 # / \ 3423 # b1 b2 3424 # / \ \ 3425 # c1 c2 c3 3426 3427 statement ok 3428 CREATE TABLE a ( 3429 id STRING PRIMARY KEY 3430 ); 3431 CREATE TABLE b1 ( 3432 id STRING PRIMARY KEY 3433 ,update_cascade STRING UNIQUE NOT NULL REFERENCES a ON UPDATE CASCADE 3434 ); 3435 CREATE TABLE b2 ( 3436 id STRING PRIMARY KEY 3437 ,update_cascade STRING UNIQUE NOT NULL REFERENCES a ON UPDATE CASCADE 3438 ); 3439 CREATE TABLE c1 ( 3440 id STRING PRIMARY KEY 3441 ,update_set_null STRING DEFAULT 'b1-default' REFERENCES b1(update_cascade) ON UPDATE SET DEFAULT 3442 ); 3443 CREATE TABLE c2 ( 3444 id STRING PRIMARY KEY 3445 ,update_set_null STRING DEFAULT 'b1-default' REFERENCES b1(update_cascade) ON UPDATE SET DEFAULT 3446 ); 3447 CREATE TABLE c3 ( 3448 id STRING PRIMARY KEY 3449 ,update_set_null STRING DEFAULT 'b2-default' REFERENCES b2(update_cascade) ON UPDATE SET DEFAULT 3450 ); 3451 3452 statement ok 3453 INSERT INTO a VALUES ('original'), ('untouched'), ('b1-default'), ('b2-default'); 3454 INSERT INTO b1 VALUES ('b1-pk1', 'original'), ('b1-pk2', 'untouched'), ('b1-default', 'b1-default'); 3455 INSERT INTO b2 VALUES ('b2-pk1', 'original'), ('b2-pk2', 'untouched'), ('b2-default', 'b2-default'); 3456 INSERT INTO c1 VALUES 3457 ('c1-pk1-b1-pk1', 'original') 3458 ,('c1-pk2-b1-pk1', 'original') 3459 ,('c1-pk3-b1-pk2', 'untouched') 3460 ,('c1-pk4-b1-pk2', 'untouched') 3461 ; 3462 INSERT INTO c2 VALUES 3463 ('c2-pk1-b1-pk1', 'original') 3464 ,('c2-pk2-b1-pk1', 'original') 3465 ,('c2-pk3-b1-pk2', 'untouched') 3466 ,('c2-pk4-b1-pk2', 'untouched') 3467 ; 3468 INSERT INTO c3 VALUES 3469 ('c3-pk1-b2-pk1', 'original') 3470 ,('c3-pk2-b2-pk1', 'original') 3471 ,('c3-pk3-b2-pk2', 'untouched') 3472 ,('c3-pk4-b2-pk2', 'untouched') 3473 ; 3474 3475 # ON UPDATE CASCADE all b1 originals should now be updated, and all c1 3476 # originals should now be set to defaults. 3477 statement ok 3478 UPDATE a SET id = 'updated' WHERE id = 'original'; 3479 3480 query TT rowsort 3481 SELECT id, update_cascade FROM b1 3482 UNION ALL 3483 SELECT id, update_cascade FROM b2 3484 UNION ALL 3485 SELECT id, update_set_null FROM c1 3486 UNION ALL 3487 SELECT id, update_set_null FROM c2 3488 UNION ALL 3489 SELECT id, update_set_null FROM c3 3490 ; 3491 ---- 3492 b1-default b1-default 3493 b1-pk1 updated 3494 b1-pk2 untouched 3495 b2-default b2-default 3496 b2-pk1 updated 3497 b2-pk2 untouched 3498 c1-pk1-b1-pk1 b1-default 3499 c1-pk2-b1-pk1 b1-default 3500 c1-pk3-b1-pk2 untouched 3501 c1-pk4-b1-pk2 untouched 3502 c2-pk1-b1-pk1 b1-default 3503 c2-pk2-b1-pk1 b1-default 3504 c2-pk3-b1-pk2 untouched 3505 c2-pk4-b1-pk2 untouched 3506 c3-pk1-b2-pk1 b2-default 3507 c3-pk2-b2-pk1 b2-default 3508 c3-pk3-b2-pk2 untouched 3509 c3-pk4-b2-pk2 untouched 3510 3511 # Clean up after the test. 3512 statement ok 3513 DROP TABLE c3, c2, c1, b2, b1, a; 3514 3515 subtest UpdateSetDefault_Basic2_WrongDefault 3516 ### Basic UPDATE SET DEFAULT via an UPDATE CASCADE 3517 # a 3518 # / \ 3519 # b1 b2 3520 # / \ \ 3521 # c1 c2 c3 3522 3523 statement ok 3524 CREATE TABLE a ( 3525 id STRING PRIMARY KEY 3526 ); 3527 CREATE TABLE b1 ( 3528 id STRING PRIMARY KEY 3529 ,update_cascade STRING UNIQUE NOT NULL REFERENCES a ON UPDATE CASCADE 3530 ); 3531 CREATE TABLE b2 ( 3532 id STRING PRIMARY KEY 3533 ,update_cascade STRING UNIQUE NOT NULL REFERENCES a ON UPDATE CASCADE 3534 ); 3535 CREATE TABLE c1 ( 3536 id STRING PRIMARY KEY 3537 ,update_set_null STRING DEFAULT 'b1-default' REFERENCES b1(update_cascade) ON UPDATE SET DEFAULT 3538 ); 3539 CREATE TABLE c2 ( 3540 id STRING PRIMARY KEY 3541 ,update_set_null STRING DEFAULT 'missing' REFERENCES b1(update_cascade) ON UPDATE SET DEFAULT 3542 ); 3543 CREATE TABLE c3 ( 3544 id STRING PRIMARY KEY 3545 ,update_set_null STRING DEFAULT 'b2-default' REFERENCES b2(update_cascade) ON UPDATE SET DEFAULT 3546 ); 3547 3548 statement ok 3549 INSERT INTO a VALUES ('original'), ('untouched'), ('b1-default'), ('b2-default'); 3550 INSERT INTO b1 VALUES ('b1-pk1', 'original'), ('b1-pk2', 'untouched'), ('b1-default', 'b1-default'); 3551 INSERT INTO b2 VALUES ('b2-pk1', 'original'), ('b2-pk2', 'untouched'), ('b2-default', 'b2-default'); 3552 INSERT INTO c1 VALUES 3553 ('c1-pk1-b1-pk1', 'original') 3554 ,('c1-pk2-b1-pk1', 'original') 3555 ,('c1-pk3-b1-pk2', 'untouched') 3556 ,('c1-pk4-b1-pk2', 'untouched') 3557 ; 3558 INSERT INTO c2 VALUES 3559 ('c2-pk1-b1-pk1', 'original') 3560 ,('c2-pk2-b1-pk1', 'original') 3561 ,('c2-pk3-b1-pk2', 'untouched') 3562 ,('c2-pk4-b1-pk2', 'untouched') 3563 ; 3564 INSERT INTO c3 VALUES 3565 ('c3-pk1-b2-pk1', 'original') 3566 ,('c3-pk2-b2-pk1', 'original') 3567 ,('c3-pk3-b2-pk2', 'untouched') 3568 ,('c3-pk4-b2-pk2', 'untouched') 3569 ; 3570 3571 # ON UPDATE CASCADE all b tables into the c tables, but fail due to a default 3572 # value that does not exist. 3573 statement error update on table "c2" violates foreign key constraint "fk_update_set_null_ref_b1"\nDETAIL: Key \(update_set_null\)=\('missing'\) is not present in table "b1"\. 3574 UPDATE a SET id = 'updated' WHERE id = 'original'; 3575 3576 # Clean up after the test. 3577 statement ok 3578 DROP TABLE c3, c2, c1, b2, b1, a; 3579 3580 subtest UpdateSetDefault_ToUpdateCascade 3581 ### Cascade an update in table a, to SET DEFAULT in table b, to an UPDATE 3582 # CASCADE of that default into table c. 3583 # a 3584 # | 3585 # b 3586 # | 3587 # c 3588 3589 statement ok 3590 CREATE TABLE a ( 3591 id STRING PRIMARY KEY 3592 ); 3593 CREATE TABLE b ( 3594 id STRING PRIMARY KEY 3595 ,a_id STRING UNIQUE DEFAULT 'default' REFERENCES a ON UPDATE SET DEFAULT 3596 ); 3597 CREATE TABLE c ( 3598 id STRING PRIMARY KEY 3599 ,b_a_id STRING REFERENCES b(a_id) ON UPDATE CASCADE 3600 ); 3601 3602 statement oK 3603 INSERT INTO a VALUES ('original'), ('untouched'), ('default'); 3604 INSERT INTO b VALUES ('b1', 'original'), ('b2', 'untouched'); 3605 INSERT INTO c VALUES 3606 ('c1-b1', 'original') 3607 ,('c2-b1', 'original') 3608 ,('c3-b2', 'untouched') 3609 ,('c4-b2', 'untouched') 3610 ; 3611 3612 statement ok 3613 UPDATE a SET id = 'updated' WHERE id = 'original'; 3614 3615 query TT rowsort 3616 SELECT id, a_id FROM b 3617 UNION ALL 3618 SELECT id, b_a_id FROM c 3619 ---- 3620 b1 default 3621 b2 untouched 3622 c1-b1 default 3623 c2-b1 default 3624 c3-b2 untouched 3625 c4-b2 untouched 3626 3627 # Clean up after the test. 3628 statement ok 3629 DROP TABLE c, b, a; 3630 3631 subtest UpdateSetDefault_ToUpdateCascadeNotNull 3632 ### Cascade a update in table a, to SET DEFAULT in table b, but that default is 3633 # a null. Then to an ON UPDATE CASCADE of that null into table c, but table c's 3634 # column is NOT NULL. 3635 # a 3636 # | 3637 # b 3638 # | 3639 # c 3640 3641 statement ok 3642 CREATE TABLE a ( 3643 id STRING PRIMARY KEY 3644 ); 3645 CREATE TABLE b ( 3646 id STRING PRIMARY KEY 3647 ,a_id STRING DEFAULT NULL UNIQUE REFERENCES a ON UPDATE SET DEFAULT 3648 ); 3649 CREATE TABLE c ( 3650 id STRING PRIMARY KEY 3651 ,b_a_id STRING NOT NULL REFERENCES b(a_id) ON UPDATE CASCADE 3652 ); 3653 3654 statement oK 3655 INSERT INTO a VALUES ('original'), ('untouched'), ('default'); 3656 INSERT INTO b VALUES ('b1', 'original'), ('b2', 'untouched'); 3657 INSERT INTO c VALUES 3658 ('c1-b1', 'original') 3659 ,('c2-b1', 'original') 3660 ,('c3-b2', 'untouched') 3661 ,('c4-b2', 'untouched') 3662 ; 3663 3664 statement error null value in column "b_a_id" violates not-null constraint 3665 UPDATE a SET id = 'updated' WHERE id = 'original'; 3666 3667 # Clean up after the test. 3668 statement ok 3669 DROP TABLE c, b, a; 3670 3671 subtest UpdateSetDefault_Unique 3672 ### Have a SET DEFAULT break a uniqueness constraint. 3673 # a 3674 # | 3675 # b 3676 3677 statement ok 3678 CREATE TABLE a ( 3679 id STRING PRIMARY KEY 3680 ); 3681 CREATE TABLE b ( 3682 id STRING PRIMARY KEY 3683 ,a_id STRING DEFAULT 'default' UNIQUE REFERENCES a ON UPDATE SET DEFAULT 3684 ); 3685 3686 statement oK 3687 INSERT INTO a VALUES ('original'), ('default'); 3688 INSERT INTO b VALUES ('b1', 'original'), ('b2', 'default'); 3689 3690 statement error pq: duplicate key value \(a_id\)=\('default'\) violates unique constraint "b_a_id_key" 3691 UPDATE a SET id = 'updated' WHERE id = 'original'; 3692 3693 # Clean up after the test. 3694 statement ok 3695 DROP TABLE b, a; 3696 3697 subtest NoNullCascades_NonComposite 3698 3699 # First with a non-composite index 3700 statement ok 3701 CREATE TABLE IF NOT EXISTS example ( 3702 a INT UNIQUE, 3703 b INT REFERENCES example (a) ON DELETE CASCADE ON UPDATE CASCADE 3704 ); 3705 3706 statement ok 3707 INSERT INTO example VALUES (20, NULL); 3708 INSERT INTO example VALUES (30, 20); 3709 INSERT INTO example VALUES (NULL, 30); 3710 3711 statement ok 3712 DELETE FROM example where a = 30; 3713 3714 query II colnames 3715 SELECT * FROM example; 3716 ---- 3717 a b 3718 20 NULL 3719 3720 # Clean up after the test. 3721 statement ok 3722 DROP TABLE example; 3723 3724 subtest NoNullCascades_MatchSimple 3725 # Note that these matches use a composite index with MATCH SIMPLE. 3726 3727 statement ok 3728 CREATE TABLE a ( 3729 x INT 3730 ,y INT 3731 ,UNIQUE (x, y) 3732 ); 3733 3734 statement ok 3735 CREATE TABLE b ( 3736 x INT 3737 ,y INT 3738 ,INDEX (x, y) 3739 ,FOREIGN KEY (x, y) REFERENCES a (x, y) ON DELETE CASCADE ON UPDATE CASCADE 3740 ); 3741 3742 statement ok 3743 INSERT INTO a VALUES (NULL, NULL), (NULL, 1), (2, NULL), (3, 3); 3744 INSERT INTO b VALUES (NULL, NULL), (NULL, 1), (2, NULL), (3, 3); 3745 3746 # What we start with. 3747 query II colnames 3748 SELECT * FROM b ORDER BY x, y; 3749 ---- 3750 x y 3751 NULL NULL 3752 NULL 1 3753 2 NULL 3754 3 3 3755 3756 # Remove everything from a. x=3,y=3 should be the only cascading values. 3757 statement ok 3758 DELETE FROM a; 3759 3760 # A match consisting of only NULLs is not cascaded. 3761 query II colnames 3762 SELECT * FROM b ORDER BY x, y; 3763 ---- 3764 x y 3765 NULL NULL 3766 NULL 1 3767 2 NULL 3768 3769 # Make sure that a is now empty. 3770 query II colnames 3771 SELECT * FROM a ORDER BY x; 3772 ---- 3773 x y 3774 3775 # Now try the same with inserts 3776 statement ok 3777 TRUNCATE b, a; 3778 INSERT INTO a VALUES (NULL, NULL), (NULL, 4), (5, NULL), (6, 6); 3779 INSERT INTO b VALUES (NULL, NULL), (NULL, 4), (5, NULL), (6, 6); 3780 3781 # For this, only x=6,y=6 should cascade. 3782 statement ok 3783 UPDATE a SET y = y*10 WHERE y > 0; 3784 UPDATE a SET x = x*10 WHERE x > 0; 3785 3786 query II colnames 3787 SELECT * FROM b ORDER BY x, y; 3788 ---- 3789 x y 3790 NULL NULL 3791 NULL 4 3792 5 NULL 3793 60 60 3794 3795 # Clean up after the test. 3796 statement ok 3797 DROP TABLE b, a; 3798 3799 subtest NoNullCascades_MatchFull 3800 # Note that these matches use a composite index with MATCH FULL. 3801 3802 statement ok 3803 CREATE TABLE a ( 3804 x INT 3805 ,y INT 3806 ,UNIQUE (x, y) 3807 ); 3808 3809 statement ok 3810 CREATE TABLE b ( 3811 x INT 3812 ,y INT 3813 ,INDEX (x, y) 3814 ,FOREIGN KEY (x, y) REFERENCES a (x, y) MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE 3815 ); 3816 3817 statement ok 3818 INSERT INTO a VALUES (NULL, NULL), (NULL, 1), (2, NULL), (3, 3); 3819 INSERT INTO b VALUES (NULL, NULL), (3, 3); 3820 3821 # What we start with 3822 query II colnames 3823 SELECT * FROM b ORDER BY x, y; 3824 ---- 3825 x y 3826 NULL NULL 3827 3 3 3828 3829 # Remove everything from a. x=3,y=3 should be the only cascading value. 3830 statement ok 3831 DELETE FROM a; 3832 3833 # A match consisting of only NULLs is not cascaded. 3834 query II colnames 3835 SELECT * FROM b ORDER BY x, y; 3836 ---- 3837 x y 3838 NULL NULL 3839 3840 # Just make sure that a is empty. 3841 query II colnames 3842 SELECT * FROM a ORDER BY x; 3843 ---- 3844 x y 3845 3846 # Now try the same with updates. 3847 statement ok 3848 TRUNCATE b, a; 3849 INSERT INTO a VALUES (NULL, NULL), (NULL, 4), (5, NULL), (6, 6); 3850 INSERT INTO b VALUES (NULL, NULL), (6, 6); 3851 3852 # For this test, only x=6,y=6 should cascade. 3853 statement ok 3854 UPDATE a SET y = y*10 WHERE y > 0; 3855 UPDATE a SET x = x*10 WHERE x > 0; 3856 3857 query II colnames 3858 SELECT * FROM b ORDER BY x, y; 3859 ---- 3860 x y 3861 NULL NULL 3862 60 60 3863 3864 # Clean up after the test. 3865 statement ok 3866 DROP TABLE b, a; 3867 3868 subtest MatchFullViaCascade 3869 # Test to ensure that setting a null value cannot cascade into a MATCH FULL fk 3870 # reference. 3871 3872 statement ok 3873 CREATE TABLE a ( 3874 x INT 3875 ,y INT 3876 ,UNIQUE (x, y) 3877 ); 3878 3879 statement ok 3880 CREATE TABLE b ( 3881 x INT DEFAULT 1 3882 ,y INT DEFAULT NULL 3883 ,UNIQUE (x, y) 3884 ,FOREIGN KEY (x, y) REFERENCES a (x, y) MATCH SIMPLE ON DELETE SET DEFAULT ON UPDATE SET DEFAULT 3885 ); 3886 3887 statement ok 3888 CREATE TABLE c ( 3889 x INT 3890 ,y INT 3891 ,UNIQUE (x, y) 3892 ,FOREIGN KEY (x, y) REFERENCES b (x, y) MATCH FULL ON UPDATE CASCADE 3893 ); 3894 3895 statement ok 3896 INSERT INTO a VALUES (2,2); 3897 INSERT INTO b VALUES (2,2); 3898 INSERT INTO c VALUES (2,2); 3899 3900 # C's MATCH FULL should reject this. 3901 statement error pq: update on table "c" violates foreign key constraint "fk_x_ref_b"\nDETAIL: MATCH FULL does not allow mixing of null and nonnull key values 3902 DELETE FROM a; 3903 3904 # Again, for the same reason. 3905 statement error update on table "c" violates foreign key constraint "fk_x_ref_b"\nDETAIL: MATCH FULL does not allow mixing of null and nonnull key values 3906 UPDATE a SET x = 3 WHERE x = 2; 3907 3908 # Ensure nothing has changed. 3909 query II colnames 3910 SELECT * from a; 3911 ---- 3912 x y 3913 2 2 3914 3915 statement ok 3916 DROP TABLE c, b, a; 3917 3918 # Now repeat the same test but make it ON DELETE SET NULL and ON UPDATE CASCADE. 3919 3920 statement ok 3921 CREATE TABLE a ( 3922 x INT 3923 ,y INT 3924 ,UNIQUE (x, y) 3925 ); 3926 3927 statement ok 3928 CREATE TABLE b ( 3929 x INT 3930 ,y INT 3931 ,UNIQUE (x, y) 3932 ,FOREIGN KEY (x, y) REFERENCES a (x, y) MATCH SIMPLE ON DELETE SET NULL ON UPDATE CASCADE 3933 ); 3934 3935 statement ok 3936 CREATE TABLE c ( 3937 x INT 3938 ,y INT 3939 ,UNIQUE (x, y) 3940 ,FOREIGN KEY (x, y) REFERENCES b (x, y) MATCH FULL ON UPDATE CASCADE 3941 ); 3942 3943 statement ok 3944 INSERT INTO a VALUES (2,2), (3,3); 3945 INSERT INTO b VALUES (2,2), (3,3); 3946 INSERT INTO c VALUES (2,2), (3,3); 3947 3948 # This will populate b and c with (null, null) where (2,2) used to be. 3949 statement ok 3950 DELETE FROM a WHERE x = 2; 3951 3952 # We can't cascade the null value though to c, since it would break MATCH FULL. 3953 statement error update on table "c" violates foreign key constraint "fk_x_ref_b"\nDETAIL: MATCH FULL does not allow mixing of null and nonnull key values\. 3954 UPDATE a SET x = NULL WHERE x = 3; 3955 3956 statement error update on table "c" violates foreign key constraint "fk_x_ref_b"\nDETAIL: MATCH FULL does not allow mixing of null and nonnull key values\. 3957 UPDATE a SET y = NULL WHERE y = 3; 3958 3959 # Now update (3,3) to (null, null) which should cascade. 3960 statement ok 3961 UPDATE a SET x = NULL, y = NULL WHERE x = 3; 3962 3963 query II colnames 3964 SELECT * from c; 3965 ---- 3966 x y 3967 NULL NULL 3968 NULL NULL 3969 3970 statement ok 3971 DROP TABLE c, b, a; 3972 3973 # Make sure the CHECK constraint is checked when a self-referencing cascade 3974 # modifies the original table (#42117). 3975 subtest SelfReferencingCheckFail 3976 3977 # TODO(radu): remove the FAMILY when #42120 is fixed. 3978 statement ok 3979 CREATE TABLE self_ab ( 3980 a INT UNIQUE, 3981 b INT DEFAULT 1 CHECK (b != 1), 3982 INDEX (b), 3983 FAMILY (a, b) 3984 ) 3985 3986 statement ok 3987 INSERT INTO self_ab VALUES (1, 2), (2, 2) 3988 3989 statement ok 3990 ALTER TABLE self_ab ADD CONSTRAINT fk FOREIGN KEY (b) REFERENCES self_ab (a) ON UPDATE SET DEFAULT 3991 3992 # This update would cause the references to 2 to get reset to the default, 3993 # which violates the check. 3994 statement error failed to satisfy CHECK constraint \(b != 1:::INT8\) 3995 UPDATE self_ab SET a = 3 WHERE a = 2 3996 3997 # Make sure the check fails when we apply the same update through a cascade. 3998 statement ok 3999 CREATE TABLE self_ab_parent (p INT PRIMARY KEY) 4000 4001 statement ok 4002 INSERT INTO self_ab_parent VALUES (1), (2) 4003 4004 statement ok 4005 ALTER TABLE self_ab ADD CONSTRAINT fk2 FOREIGN KEY (a) REFERENCES self_ab_parent (p) ON UPDATE CASCADE 4006 4007 statement error failed to satisfy CHECK constraint \(b != 1:::INT8\) 4008 UPDATE self_ab_parent SET p = 3 WHERE p = 2