github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/fk_opt (about) 1 # LogicTest: local fakedist 2 3 # The tests in this file target the new optimizer-driven FK paths (with 4 # fall back on the legacy paths for unsupported cases). 5 statement ok 6 SET optimizer_foreign_keys = true 7 8 statement ok 9 SET experimental_optimizer_foreign_key_cascades = true 10 11 # Randomize the use of insert fast path. 12 # The let statement will also log the value. 13 let $enable_insert_fast_path 14 SELECT random() < 0.5 15 16 statement ok 17 SET enable_insert_fast_path = $enable_insert_fast_path 18 19 # Insert 20 # ------ 21 22 statement ok 23 CREATE TABLE parent (p INT PRIMARY KEY, other INT) 24 25 statement ok 26 CREATE TABLE child (c INT PRIMARY KEY, p INT NOT NULL REFERENCES parent(p)) 27 28 statement error insert on table "child" violates foreign key constraint "fk_p_ref_parent"\nDETAIL: Key \(p\)=\(1\) is not present in table "parent"\. 29 INSERT INTO child VALUES (1,1) 30 31 statement ok 32 INSERT INTO parent VALUES (1), (2) 33 34 statement error insert on table "child" violates foreign key constraint "fk_p_ref_parent"\nDETAIL: Key \(p\)=\(3\) is not present in table "parent"\. 35 INSERT INTO child VALUES (1,1), (2,2), (3,3) 36 37 statement ok 38 INSERT INTO child VALUES (1,1), (2,2) 39 40 # Use data from a different table as input. 41 statement ok 42 CREATE TABLE xy (x INT, y INT) 43 44 statement ok 45 INSERT INTO xy VALUES (4, 4), (5, 5), (6, 6) 46 47 statement error insert on table "child" violates foreign key constraint "fk_p_ref_parent"\nDETAIL: Key \(p\)=\(4\) is not present in table "parent"\. 48 INSERT INTO child SELECT x,y FROM xy 49 50 statement ok 51 INSERT INTO parent SELECT x FROM xy 52 53 statement ok 54 INSERT INTO child SELECT x,y FROM xy 55 56 statement ok 57 DROP TABLE xy 58 59 statement ok 60 DROP TABLE child 61 62 statement ok 63 DROP TABLE parent 64 65 # Delete 66 # ------ 67 68 statement ok 69 CREATE TABLE parent (x INT, p INT PRIMARY KEY, u INT UNIQUE) 70 71 statement ok 72 CREATE TABLE child (c INT PRIMARY KEY, p INT NOT NULL REFERENCES parent(p)) 73 74 statement ok 75 INSERT INTO parent (p, u) VALUES (1, 10), (2, 20) 76 77 statement ok 78 INSERT INTO child VALUES (1, 1) 79 80 statement ok 81 DELETE FROM parent WHERE p = 2 82 83 statement error delete on table "parent" violates foreign key constraint "fk_p_ref_parent" on table "child"\nDETAIL: Key \(p\)=\(1\) is still referenced from table "child"\. 84 DELETE FROM parent WHERE p = 1 85 86 statement ok 87 CREATE TABLE child_u (c INT PRIMARY KEY, u INT NOT NULL REFERENCES parent(u)) 88 89 statement ok 90 DROP TABLE child 91 92 statement ok 93 INSERT INTO child_u VALUES (1, 10) 94 95 statement error delete on table "parent" violates foreign key constraint "fk_u_ref_parent" on table "child_u"\nDETAIL: Key \(u\)=\(10\) is still referenced from table "child_u"\. 96 DELETE FROM parent WHERE p = 1 97 98 statement ok 99 DROP TABLE child_u 100 101 statement ok 102 DROP TABLE parent 103 104 statement ok 105 CREATE TABLE parent2 (p1 INT, p2 INT, other INT, PRIMARY KEY (p1, p2)) 106 107 statement ok 108 CREATE TABLE child2 (c INT PRIMARY KEY, p1 INT, p2 INT, FOREIGN KEY (p1, p2) REFERENCES parent2 (p1, p2)) 109 110 statement ok 111 INSERT INTO parent2 VALUES 112 (10, 100), 113 (10, 150), 114 (20, 200) 115 116 statement ok 117 INSERT INTO child2 VALUES 118 (1, 10, 100), 119 (2, 10, NULL), 120 (3, 10, 150), 121 (4, 20, 200), 122 (5, NULL, 100) 123 124 statement error delete on table "parent2" violates foreign key constraint "fk_p1_ref_parent2" on table "child2"\nDETAIL: Key \(p1, p2\)=\(10, 100\) is still referenced from table "child2"\. 125 DELETE FROM parent2 WHERE p1 = 10 AND p2 = 100 126 127 statement ok 128 DELETE FROM child2 WHERE p1 = 10 AND p2 = 100 129 130 statement ok 131 DELETE FROM parent2 WHERE p1 = 10 AND p2 = 100 132 133 statement ok 134 DROP TABLE child2 135 136 statement ok 137 DROP TABLE parent2 138 139 # Upsert 140 # ------ 141 142 statement ok 143 CREATE TABLE parent (p INT PRIMARY KEY, other INT) 144 145 statement ok 146 CREATE TABLE child (c INT PRIMARY KEY, p INT NOT NULL REFERENCES parent(p)) 147 148 statement ok 149 INSERT INTO parent VALUES (1), (2) 150 151 # Insert case. 152 statement ok 153 INSERT INTO child VALUES (1, 1) ON CONFLICT (c) DO UPDATE SET p = 2 154 155 statement error foreign key 156 INSERT INTO child VALUES (2, 10) ON CONFLICT (c) DO UPDATE SET p = 2 157 158 # Update case. 159 statement ok 160 INSERT INTO child VALUES (1, 1) ON CONFLICT (c) DO UPDATE SET p = 1 161 162 statement ok 163 INSERT INTO child VALUES (1, 10) ON CONFLICT (c) DO UPDATE SET p = 1 164 165 statement error foreign key 166 INSERT INTO child VALUES (1, 10) ON CONFLICT (c) DO UPDATE SET p = 10 167 168 statement ok 169 TRUNCATE child 170 171 statement ok 172 INSERT INTO child VALUES (1, 1) 173 174 # Both insert and update case. 175 176 # Both insert and update are invalid. 177 178 statement error foreign key 179 INSERT INTO child VALUES (1, 1), (2, 3) ON CONFLICT (c) DO UPDATE SET p = 3 180 181 # Insert is invalid, update is valid. 182 183 statement error foreign key 184 INSERT INTO child VALUES (1, 2), (2, 3) ON CONFLICT (c) DO UPDATE SET p = 1 185 186 # Insert is valid, update is invalid. 187 188 statement error foreign key 189 INSERT INTO child VALUES (1, 2), (2, 1) ON CONFLICT (c) DO UPDATE SET p = 3 190 191 # Both insert and update are valid. 192 193 statement ok 194 INSERT INTO child VALUES (1, 2), (2, 1) ON CONFLICT (c) DO UPDATE SET p = 2 195 196 statement ok 197 DROP TABLE child 198 199 statement ok 200 DROP TABLE parent 201 202 # Pseudo-deletions 203 204 statement ok 205 CREATE TABLE parent (a INT PRIMARY KEY, b INT, UNIQUE (b)) 206 207 statement ok 208 CREATE TABLE child (a INT PRIMARY KEY, b INT REFERENCES parent (b)) 209 210 statement ok 211 INSERT INTO parent VALUES (1, 2) 212 213 statement ok 214 INSERT INTO child VALUES (10, 2) 215 216 statement error pq: upsert on table "parent" violates foreign key constraint "fk_b_ref_parent" on table "child"\nDETAIL: Key \(b\)=\(2\) is still referenced from table "child"\. 217 UPSERT INTO parent VALUES (1, 3) 218 219 statement ok 220 INSERT INTO parent VALUES (1, 3), (2, 2) ON CONFLICT (a) DO UPDATE SET b = 3 221 222 query II 223 SELECT * FROM child 224 ---- 225 10 2 226 227 query II rowsort 228 SELECT * FROM parent 229 ---- 230 1 3 231 2 2 232 233 # child references the second '2' column in parent. This mutation removes that 234 # row via an update, and is disallowed. 235 statement error pq: insert on table "parent" violates foreign key constraint "fk_b_ref_parent" on table "child"\nDETAIL: Key \(b\)=\(2\) is still referenced from table "child"\. 236 INSERT INTO parent VALUES (2, 2) ON CONFLICT (a) DO UPDATE SET b = parent.b - 1 237 238 statement ok 239 DROP TABLE child 240 241 statement ok 242 DROP TABLE parent 243 244 # Self-reference. 245 246 statement ok 247 CREATE TABLE self (k int primary key, a int unique, b int references self(a)) 248 249 statement error pq: upsert on table "self" violates foreign key constraint "fk_b_ref_self"\nDETAIL: Key \(b\)=\(2\) is not present in table "self" 250 UPSERT INTO self VALUES (1, 1, 2) 251 252 statement ok 253 UPSERT INTO self VALUES (1, 1, 1) 254 255 statement ok 256 UPSERT INTO self VALUES (1, 1, 1) 257 258 statement error pq: upsert on table "self" violates foreign key constraint "fk_b_ref_self"\nDETAIL: Key \(b\)=\(2\) is not present in table "self" 259 UPSERT INTO self VALUES (1, 1, 2) 260 261 statement ok 262 UPSERT INTO self VALUES (1, 2, 2) 263 264 statement error pq: upsert on table "self" violates foreign key constraint "fk_b_ref_self"\nDETAIL: Key \(b\)=\(2\) is not present in table "self" 265 UPSERT INTO self(k,a) VALUES (1, 1) 266 267 statement ok 268 UPSERT INTO self VALUES (1, 1, 2), (2, 2, 1) 269 270 statement ok 271 INSERT INTO self VALUES (2, 2, 2) ON CONFLICT (k) DO UPDATE SET b = self.b + 1 272 273 statement error pq: insert on table "self" violates foreign key constraint "fk_b_ref_self"\nDETAIL: Key \(b\)=\(3\) is not present in table "self" 274 INSERT INTO self VALUES (2, 2, 2) ON CONFLICT (k) DO UPDATE SET b = self.b + 1 275 276 statement ok 277 DROP TABLE self 278 279 # --- Tests that follow are copied from the fk tests and adjusted as needed. 280 281 statement ok 282 CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE) 283 284 statement ok 285 INSERT INTO customers VALUES (1, 'a@co.tld'), (2, 'b@co.tld') 286 287 statement ok 288 CREATE TABLE products (sku STRING PRIMARY KEY, upc STRING UNIQUE, vendor STRING) 289 290 statement ok 291 INSERT INTO products VALUES ('VP-W9QH-W44L', '867072000006', 'Dave'), ('780', '885155001450', 'iRobot') 292 293 statement error pgcode 42P01 relation "productz" does not exist 294 CREATE TABLE missing (product STRING REFERENCES productz) 295 296 statement error pgcode 42P01 relation "customerz" does not exist 297 CREATE TABLE missing_with_col (customer INT REFERENCES customerz (id)) 298 299 statement error pgcode 42703 column "idz" does not exist 300 CREATE TABLE missing_col (customer INT REFERENCES customers (idz)) 301 302 statement ok 303 CREATE TABLE unindexed (customer INT REFERENCES customers) 304 305 query TTBITTBB colnames 306 SHOW INDEXES FROM unindexed 307 ---- 308 table_name index_name non_unique seq_in_index column_name direction storing implicit 309 unindexed primary false 1 rowid ASC false false 310 unindexed unindexed_auto_index_fk_customer_ref_customers true 1 customer ASC false false 311 unindexed unindexed_auto_index_fk_customer_ref_customers true 2 rowid ASC false true 312 313 statement error there is no unique constraint matching given keys for referenced table products 314 CREATE TABLE non_unique (product STRING REFERENCES products (vendor)) 315 316 statement error type of "customer" \(int\) does not match foreign key "customers"."email" \(string\) 317 CREATE TABLE mismatch (customer INT REFERENCES customers (email)) 318 319 statement ok 320 CREATE TABLE orders ( 321 id INT, 322 shipment INT, 323 product STRING DEFAULT 'sprockets' REFERENCES products, 324 customer INT CONSTRAINT valid_customer REFERENCES customers (id), 325 PRIMARY KEY (id, shipment), 326 INDEX (product), 327 INDEX (customer) 328 ) 329 330 statement ok 331 ALTER TABLE orders DROP CONSTRAINT fk_product_ref_products 332 333 statement ok 334 ALTER TABLE orders ADD FOREIGN KEY (product) REFERENCES products ON DELETE NO ACTION 335 336 statement ok 337 ALTER TABLE orders DROP CONSTRAINT fk_product_ref_products 338 339 statement ok 340 ALTER TABLE orders ADD FOREIGN KEY (product) REFERENCES products ON UPDATE NO ACTION 341 342 statement ok 343 ALTER TABLE orders DROP CONSTRAINT fk_product_ref_products 344 345 statement ok 346 ALTER TABLE orders ADD FOREIGN KEY (product) REFERENCES products ON DELETE CASCADE 347 348 statement ok 349 ALTER TABLE orders DROP CONSTRAINT fk_product_ref_products 350 351 statement ok 352 ALTER TABLE orders ADD FOREIGN KEY (product) REFERENCES products ON UPDATE CASCADE 353 354 statement ok 355 ALTER TABLE orders DROP CONSTRAINT fk_product_ref_products 356 357 statement ok 358 ALTER TABLE orders ADD FOREIGN KEY (product) REFERENCES products ON DELETE SET NULL 359 360 statement ok 361 ALTER TABLE orders DROP CONSTRAINT fk_product_ref_products 362 363 statement ok 364 ALTER TABLE orders ADD FOREIGN KEY (product) REFERENCES products ON UPDATE SET NULL 365 366 statement ok 367 ALTER TABLE orders DROP CONSTRAINT fk_product_ref_products 368 369 statement ok 370 ALTER TABLE orders ADD FOREIGN KEY (product) REFERENCES products ON DELETE SET DEFAULT 371 372 statement ok 373 ALTER TABLE orders DROP CONSTRAINT fk_product_ref_products 374 375 statement ok 376 ALTER TABLE orders ADD FOREIGN KEY (product) REFERENCES products ON UPDATE SET DEFAULT 377 378 statement ok 379 ALTER TABLE orders DROP CONSTRAINT fk_product_ref_products 380 381 statement ok 382 ALTER TABLE orders ADD FOREIGN KEY (product) REFERENCES products ON DELETE RESTRICT ON UPDATE NO ACTION 383 384 statement ok 385 ALTER TABLE orders DROP CONSTRAINT fk_product_ref_products 386 387 statement ok 388 ALTER TABLE orders ADD FOREIGN KEY (product) REFERENCES products ON DELETE RESTRICT ON UPDATE RESTRICT 389 390 statement ok 391 ALTER TABLE orders VALIDATE CONSTRAINT fk_product_ref_products 392 393 statement ok 394 CREATE DATABASE "user content" 395 396 # "reviews" makes "products" have multiple inbound references, as well as making 397 # "orders" have both directions, and makes sure that we're handling escaping and 398 # cross-database references. 399 statement ok 400 CREATE TABLE "user content"."customer reviews" ( 401 id INT PRIMARY KEY, 402 product STRING NOT NULL REFERENCES products, 403 customer INT, 404 "order" INT, 405 shipment int, 406 body STRING, 407 CONSTRAINT customerfk FOREIGN KEY (customer) REFERENCES customers, 408 CONSTRAINT orderfk FOREIGN KEY ("order", shipment) REFERENCES orders (id, shipment), 409 INDEX (product), 410 INDEX (customer), 411 INDEX ("order") 412 ) 413 414 statement ok 415 INSERT INTO orders VALUES (1, 1, '780', 2) 416 417 statement error insert on table "orders" violates foreign key constraint "fk_product_ref_products"\nDETAIL: Key \(product\)=\('fake'\) is not present in table "products" 418 INSERT INTO orders VALUES (2, 2, 'fake', 2) 419 420 statement error pgcode 23503 delete on table "products" violates foreign key constraint "fk_product_ref_products" on table "orders"\nDETAIL: Key \(sku\)=\('780'\) is still referenced from table "orders"\. 421 DELETE FROM products 422 423 statement ok 424 INSERT INTO "user content"."customer reviews" VALUES (1, '780', 2, 1, 1, NULL) 425 426 statement error pgcode 23503 insert on table "customer reviews" violates foreign key constraint "fk_product_ref_products"\nDETAIL: Key \(product\)=\('790'\) is not present in table "products". 427 INSERT INTO "user content"."customer reviews" (id, product, body) VALUES (2, '790', 'would not buy again') 428 429 statement ok 430 INSERT INTO "user content"."customer reviews" (id, product, body) VALUES (2, '780', 'would not buy again') 431 432 statement ok 433 CREATE TABLE "user content".review_stats ( 434 id INT PRIMARY KEY, 435 upvotes INT, 436 CONSTRAINT reviewfk FOREIGN KEY (id) REFERENCES "user content"."customer reviews" 437 ) 438 439 query TTTTB 440 SHOW CONSTRAINTS FROM "user content".review_stats 441 ---- 442 review_stats primary PRIMARY KEY PRIMARY KEY (id ASC) true 443 review_stats reviewfk FOREIGN KEY FOREIGN KEY (id) REFERENCES "customer reviews"(id) true 444 445 statement error pgcode 23503 insert on table "review_stats" violates foreign key constraint "reviewfk"\nDETAIL: Key \(id\)=\(5\) is not present in table "customer reviews" 446 INSERT INTO "user content".review_stats (id, upvotes) VALUES (5, 1) 447 448 statement ok 449 INSERT INTO "user content".review_stats (id, upvotes) VALUES (2, 1) 450 451 statement error pgcode 23503 delete on table "customer reviews" violates foreign key constraint "reviewfk" on table "review_stats"\nDETAIL: Key \(id\)=\(2\) is still referenced from table "review_stats"\. 452 DELETE FROM "user content"."customer reviews" WHERE id = 2 453 454 statement ok 455 ALTER TABLE "user content".review_stats DROP CONSTRAINT reviewfk 456 457 query TTTTB 458 SHOW CONSTRAINTS FROM "user content".review_stats 459 ---- 460 review_stats primary PRIMARY KEY PRIMARY KEY (id ASC) true 461 462 statement ok 463 DELETE FROM "user content"."customer reviews" 464 465 statement error pgcode 23503 insert on table "orders" violates foreign key constraint "fk_product_ref_products"\nDETAIL: Key \(product\)=\('790'\) is not present in table "products" 466 INSERT INTO orders VALUES (2, 1, '790', 2) 467 468 statement error pgcode 23503 insert on table "orders" violates foreign key constraint "valid_customer"\nDETAIL: Key \(customer\)=\(43\) is not present in table "customers" 469 INSERT INTO orders VALUES (2, 1, '780', 43) 470 471 statement ok 472 INSERT INTO orders VALUES (2, 1, '780', 1) 473 474 # Try to point to missing FK. 475 statement error pgcode 23503 update on table "orders" violates foreign key constraint "fk_product_ref_products" 476 UPDATE orders SET product = '790' WHERE id = 2 477 478 # Try to point to missing fk *while changing PK*. 479 statement error pgcode 23503 update on table "orders" violates foreign key constraint "fk_product_ref_products" 480 UPDATE orders SET id = 3, product = '790' WHERE id = 2 481 482 # Change PK while leaving everything else is fine though. 483 statement ok 484 UPDATE orders SET id = 3 WHERE id = 2 485 486 # Change PK and point to different product. 487 statement ok 488 UPDATE orders SET id = 2, product = 'VP-W9QH-W44L' WHERE id = 3 489 490 statement ok 491 UPDATE orders SET product = '780' WHERE id = 2 492 493 # "delivery" is interesting since it references a secondary index with different col names. 494 statement ok 495 CREATE TABLE delivery ( 496 ts TIMESTAMP DEFAULT now(), 497 "order" int, 498 shipment int, 499 item STRING REFERENCES products (upc), 500 FOREIGN KEY ("order", shipment) REFERENCES orders (id, shipment), 501 INDEX (item), 502 FAMILY "primary" (ts, "order", shipment, item, rowid) 503 ) 504 505 query TT 506 SHOW CREATE TABLE delivery 507 ---- 508 delivery CREATE TABLE delivery ( 509 ts TIMESTAMP NULL DEFAULT now():::TIMESTAMP, 510 "order" INT8 NULL, 511 shipment INT8 NULL, 512 item STRING NULL, 513 CONSTRAINT fk_order_ref_orders FOREIGN KEY ("order", shipment) REFERENCES orders(id, shipment), 514 CONSTRAINT fk_item_ref_products FOREIGN KEY (item) REFERENCES products(upc), 515 INDEX delivery_item_idx (item ASC), 516 INDEX delivery_auto_index_fk_order_ref_orders ("order" ASC, shipment ASC), 517 FAMILY "primary" (ts, "order", shipment, item, rowid) 518 ) 519 520 statement ok 521 INSERT INTO delivery ("order", shipment, item) VALUES 522 (1, 1, '867072000006'), (1, 1, '867072000006'), (1, 1, '885155001450'), (1, 1, '867072000006') 523 524 statement error pgcode 23503 insert on table "delivery" violates foreign key constraint "fk_item_ref_products"\nDETAIL: Key \(item\)=\('missing'\) is not present in table "products" 525 INSERT INTO delivery ("order", shipment, item) VALUES 526 (1, 1, '867072000006'), (1, 1, 'missing'), (1, 1, '885155001450'), (1, 1, '867072000006') 527 528 statement error pgcode 23503 insert on table "delivery" violates foreign key constraint "fk_order_ref_orders"\nDETAIL: Key \(order, shipment\)=\(1, 99\) is not present in table "orders" 529 INSERT INTO delivery ("order", shipment, item) VALUES 530 (1, 1, '867072000006'), (1, 99, '867072000006') 531 532 statement error pgcode 23503 delete on table "products" violates foreign key constraint "fk_item_ref_products" on table "delivery"\nDETAIL: Key \(upc\)=\('867072000006'\) is still referenced from table "delivery"\. 533 DELETE FROM products WHERE sku = 'VP-W9QH-W44L' 534 535 # Blanking a field nobody cares about is fine. 536 statement ok 537 UPDATE products SET vendor = '' WHERE sku = '780' 538 539 # No-op update should be fine. 540 statement ok 541 UPDATE products SET sku = '770' WHERE sku = '750' 542 543 # Changing referenced PK fails. 544 statement error pgcode 23503 update on table "products" violates foreign key constraint "fk_product_ref_products" on table "orders"\nDETAIL: Key \(sku\)=\('780'\) is still referenced from table "orders"\. 545 UPDATE products SET sku = '770' WHERE sku = '780' 546 547 # No-op change to existing data is fine. 548 statement ok 549 UPDATE products SET upc = '885155001450' WHERE sku = '780' 550 551 # Changing referenced non-pk index fails. 552 statement error pgcode 23503 update on table "products" violates foreign key constraint "fk_item_ref_products" on table "delivery"\nDETAIL: Key \(upc\)=\('885155001450'\) is still referenced from table "delivery"\. 553 UPDATE products SET upc = 'blah' WHERE sku = '780' 554 555 statement ok 556 ALTER TABLE delivery DROP CONSTRAINT fk_item_ref_products 557 558 statement ok 559 UPDATE products SET upc = 'blah' WHERE sku = '780' 560 561 statement error pgcode 23503 foreign key violation: "delivery" row item='885155001450', rowid=[0-9]* has no match in "products" 562 ALTER TABLE delivery ADD FOREIGN KEY (item) REFERENCES products (upc) 563 564 query TTTTB 565 SHOW CONSTRAINTS FROM delivery 566 ---- 567 delivery fk_order_ref_orders FOREIGN KEY FOREIGN KEY ("order", shipment) REFERENCES orders(id, shipment) true 568 569 statement ok 570 UPDATE products SET upc = '885155001450' WHERE sku = '780' 571 572 statement ok 573 ALTER TABLE delivery ADD FOREIGN KEY (item) REFERENCES products (upc) 574 575 query TTTTB 576 SHOW CONSTRAINTS FROM delivery 577 ---- 578 delivery fk_item_ref_products FOREIGN KEY FOREIGN KEY (item) REFERENCES products(upc) true 579 delivery fk_order_ref_orders FOREIGN KEY FOREIGN KEY ("order", shipment) REFERENCES orders(id, shipment) true 580 581 statement ok 582 ALTER TABLE "user content"."customer reviews" 583 DROP CONSTRAINT orderfk 584 585 statement ok 586 INSERT INTO "user content"."customer reviews" (id, product, body, "order") VALUES (3, '780', 'i ordered 100 of them', 9) 587 588 statement ok 589 ALTER TABLE "user content"."customer reviews" 590 ADD CONSTRAINT orderfk2 FOREIGN KEY ("order", shipment) REFERENCES orders (id, shipment) 591 592 # This is allowed because we match using MATCH SIMPLE. 593 statement ok 594 ALTER TABLE "user content"."customer reviews" 595 VALIDATE CONSTRAINT orderfk2 596 597 # This is allowed because we match using MATCH SIMPLE. 598 statement ok 599 INSERT INTO "user content"."customer reviews" (id, product, body, "order") VALUES (4, '780', 'i ordered 101 of them', 9) 600 601 statement error pgcode 23503 insert on table "customer reviews" violates foreign key constraint "orderfk2"\nDETAIL: Key \(order, shipment\)=\(9, 1\) is not present in table "orders" 602 INSERT INTO "user content"."customer reviews" (id, product, body, "order", shipment) VALUES (5, '780', 'i ordered 101 of them', 9, 1) 603 604 statement error pgcode 23503 insert on table "customer reviews" violates foreign key constraint "orderfk2"\nDETAIL: Key \(order, shipment\)=\(1, 9\) is not present in table "orders" 605 INSERT INTO "user content"."customer reviews" (id, product, body, shipment, "order") VALUES (5, '780', 'i ordered 101 of them', 9, 1) 606 607 statement ok 608 ALTER TABLE delivery DROP CONSTRAINT fk_order_ref_orders 609 610 statement ok 611 TRUNCATE orders, "user content"."customer reviews" 612 613 # Changing now non-referenced and secondary field is fine. 614 statement ok 615 UPDATE products SET sku = '750', vendor = 'roomba' WHERE sku = '780' 616 617 # Changing PK and referenced secondary index is not ok. 618 statement error pgcode 23503 update on table "products" violates foreign key constraint "fk_item_ref_products" on table "delivery"\nDETAIL: Key \(upc\)=\('885155001450'\) is still referenced from table "delivery"\. 619 UPDATE products SET sku = '780', upc = 'blah' WHERE sku = '750' 620 621 statement error pgcode 23503 delete on table "products" violates foreign key constraint "fk_item_ref_products" on table "delivery"\nDETAIL: Key \(upc\)=\('885155001450'\) is still referenced from table "delivery"\. 622 DELETE FROM products WHERE sku = '750' 623 624 statement error "products" is referenced by foreign key from table "orders" 625 TRUNCATE products 626 627 query I 628 SELECT count(*) FROM delivery 629 ---- 630 4 631 632 statement ok 633 TRUNCATE products CASCADE 634 635 query I 636 SELECT count(*) FROM delivery 637 ---- 638 0 639 640 statement ok 641 TRUNCATE delivery, products, orders, "user content"."customer reviews" 642 643 query TTTTB colnames 644 SHOW CONSTRAINTS FROM orders 645 ---- 646 table_name constraint_name constraint_type details validated 647 orders fk_product_ref_products FOREIGN KEY FOREIGN KEY (product) REFERENCES products(sku) ON DELETE RESTRICT ON UPDATE RESTRICT true 648 orders primary PRIMARY KEY PRIMARY KEY (id ASC, shipment ASC) true 649 orders valid_customer FOREIGN KEY FOREIGN KEY (customer) REFERENCES customers(id) true 650 651 statement error pq: index "products_upc_key" is in use as unique constraint 652 DROP INDEX products@products_upc_key 653 654 statement error pq: index "products_upc_key" is in use as unique constraint 655 DROP INDEX products@products_upc_key RESTRICT 656 657 statement error "products_upc_key" is referenced by foreign key from table "delivery" 658 ALTER TABLE products DROP COLUMN upc 659 660 statement ok 661 ALTER TABLE delivery DROP COLUMN "item" 662 663 statement ok 664 DROP INDEX products@products_upc_key CASCADE 665 666 statement error index "orders_product_idx" is in use as a foreign key constraint 667 DROP INDEX orders@orders_product_idx 668 669 statement error index "orders_product_idx" is in use as a foreign key constraint 670 DROP INDEX orders@orders_product_idx RESTRICT 671 672 statement error "products" is referenced by foreign key from table "orders" 673 DROP TABLE products 674 675 statement error referenced by foreign key from table "orders" 676 DROP TABLE products RESTRICT 677 678 statement error referenced by foreign key from table "customer reviews" 679 DROP TABLE orders 680 681 # reviews has a multi-col FK in which dropping one col is not allowed. 682 statement error column "order" is referenced by existing index "customer reviews_auto_index_orderfk" 683 ALTER TABLE "user content"."customer reviews" DROP COLUMN "order" 684 685 statement ok 686 ALTER TABLE "user content"."customer reviews" DROP COLUMN "order" CASCADE 687 688 statement ok 689 DROP TABLE "user content"."customer reviews" 690 691 statement ok 692 DROP TABLE orders 693 694 statement ok 695 DROP TABLE products 696 697 statement ok 698 CREATE TABLE parent (id int primary key) 699 700 statement ok 701 CREATE TABLE child (id INT PRIMARY KEY, parent_id INT UNIQUE REFERENCES parent) 702 703 statement ok 704 CREATE TABLE grandchild (id INT PRIMARY KEY, parent_id INT REFERENCES child (parent_id), INDEX (parent_id)) 705 706 statement error "parent" is referenced by foreign key from table "child" 707 DROP TABLE parent 708 709 statement error "child" is referenced by foreign key from table "grandchild" 710 DROP TABLE child 711 712 statement error pgcode 23503 foreign key 713 INSERT INTO child VALUES (2, 2) 714 715 statement ok 716 DROP TABLE parent CASCADE 717 718 statement ok 719 INSERT INTO child VALUES (2, 2) 720 721 statement error pgcode 23503 foreign key 722 INSERT INTO grandchild VALUES (1, 1) 723 724 statement error in use as a foreign key constraint 725 DROP INDEX grandchild@grandchild_parent_id_idx 726 727 statement ok 728 DROP INDEX grandchild@grandchild_parent_id_idx CASCADE 729 730 statement ok 731 INSERT INTO grandchild VALUES (1, 1) 732 733 statement ok 734 DROP TABLE grandchild 735 736 statement ok 737 CREATE TABLE grandchild (id INT PRIMARY KEY, parent_id INT REFERENCES child (parent_id), INDEX (parent_id)) 738 739 statement error pgcode 23503 foreign key 740 INSERT INTO grandchild VALUES (1, 1) 741 742 statement error pq: index "child_parent_id_key" is in use as unique constraint 743 DROP INDEX child@child_parent_id_key 744 745 statement ok 746 DROP INDEX child@child_parent_id_key CASCADE 747 748 statement ok 749 INSERT INTO grandchild VALUES (1, 1) 750 751 statement ok 752 CREATE TABLE employees (id INT PRIMARY KEY, manager INT REFERENCES employees, INDEX (manager)) 753 754 statement ok 755 INSERT INTO employees VALUES (1, NULL) 756 757 statement ok 758 INSERT INTO employees VALUES (2, 1), (3, 1) 759 760 statement ok 761 INSERT INTO employees VALUES (4, 2), (5, 3) 762 763 statement error pgcode 23503 foreign key 764 DELETE FROM employees WHERE id = 2 765 766 # Deleting all the rows works - we are checking the FKs after the mutation. 767 statement ok 768 DELETE FROM employees WHERE id > 1 769 770 statement ok 771 DROP TABLE employees 772 773 statement ok 774 CREATE TABLE pairs (id INT PRIMARY KEY, src INT, dest STRING, UNIQUE (src, dest)) 775 776 statement ok 777 INSERT INTO pairs VALUES (1, 100, 'one'), (2, 200, 'two') 778 779 statement error type of "b" \(string\) does not match foreign key "pairs"."id" \(int\) 780 CREATE TABLE refpairs (a INT, b STRING, CONSTRAINT fk FOREIGN KEY (b) REFERENCES pairs) 781 782 statement error 2 columns must reference exactly 2 columns in referenced table \(found 1\) 783 CREATE TABLE refpairs (a INT, b STRING, CONSTRAINT fk FOREIGN KEY (a, b) REFERENCES pairs) 784 785 # TODO(dt): remove ordering constraint on matching index 786 statement ok 787 CREATE TABLE refpairs_wrong_order ( 788 a INT, 789 b STRING, 790 FOREIGN KEY (a, b) REFERENCES pairs (src, dest), 791 INDEX (b, a) 792 ) 793 794 query TTBITTBB colnames 795 SHOW INDEXES FROM refpairs_wrong_order 796 ---- 797 table_name index_name non_unique seq_in_index column_name direction storing implicit 798 refpairs_wrong_order primary false 1 rowid ASC false false 799 refpairs_wrong_order refpairs_wrong_order_b_a_idx true 1 b ASC false false 800 refpairs_wrong_order refpairs_wrong_order_b_a_idx true 2 a ASC false false 801 refpairs_wrong_order refpairs_wrong_order_b_a_idx true 3 rowid ASC false true 802 refpairs_wrong_order refpairs_wrong_order_auto_index_fk_a_ref_pairs true 1 a ASC false false 803 refpairs_wrong_order refpairs_wrong_order_auto_index_fk_a_ref_pairs true 2 b ASC false false 804 refpairs_wrong_order refpairs_wrong_order_auto_index_fk_a_ref_pairs true 3 rowid ASC false true 805 806 statement ok 807 CREATE TABLE refpairs_c_between (a INT, b STRING, c INT, FOREIGN KEY (a, b) REFERENCES pairs (src, dest), INDEX (a, c, b)) 808 809 query TTBITTBB colnames 810 SHOW INDEXES FROM refpairs_c_between 811 ---- 812 table_name index_name non_unique seq_in_index column_name direction storing implicit 813 refpairs_c_between primary false 1 rowid ASC false false 814 refpairs_c_between refpairs_c_between_a_c_b_idx true 1 a ASC false false 815 refpairs_c_between refpairs_c_between_a_c_b_idx true 2 c ASC false false 816 refpairs_c_between refpairs_c_between_a_c_b_idx true 3 b ASC false false 817 refpairs_c_between refpairs_c_between_a_c_b_idx true 4 rowid ASC false true 818 refpairs_c_between refpairs_c_between_auto_index_fk_a_ref_pairs true 1 a ASC false false 819 refpairs_c_between refpairs_c_between_auto_index_fk_a_ref_pairs true 2 b ASC false false 820 refpairs_c_between refpairs_c_between_auto_index_fk_a_ref_pairs true 3 rowid ASC false true 821 822 statement ok 823 CREATE TABLE refpairs ( 824 a INT, 825 b STRING, 826 c INT, 827 FOREIGN KEY (a, b) REFERENCES pairs (src, dest) ON UPDATE RESTRICT, 828 INDEX (a, b, c), 829 FAMILY "primary" (a, b, c, rowid) 830 ) 831 832 query TTBITTBB colnames 833 SHOW INDEXES FROM refpairs 834 ---- 835 table_name index_name non_unique seq_in_index column_name direction storing implicit 836 refpairs primary false 1 rowid ASC false false 837 refpairs refpairs_a_b_c_idx true 1 a ASC false false 838 refpairs refpairs_a_b_c_idx true 2 b ASC false false 839 refpairs refpairs_a_b_c_idx true 3 c ASC false false 840 refpairs refpairs_a_b_c_idx true 4 rowid ASC false true 841 842 query TT 843 SHOW CREATE TABLE refpairs 844 ---- 845 refpairs CREATE TABLE refpairs ( 846 a INT8 NULL, 847 b STRING NULL, 848 c INT8 NULL, 849 CONSTRAINT fk_a_ref_pairs FOREIGN KEY (a, b) REFERENCES pairs(src, dest) ON UPDATE RESTRICT, 850 INDEX refpairs_a_b_c_idx (a ASC, b ASC, c ASC), 851 FAMILY "primary" (a, b, c, rowid) 852 ) 853 854 statement error pgcode 23503 insert on table "refpairs" violates foreign key constraint "fk_a_ref_pairs"\nDETAIL: Key \(a, b\)=\(100, 'two'\) is not present in table "pairs". 855 INSERT INTO refpairs VALUES (100, 'two'), (200, 'two') 856 857 statement ok 858 INSERT INTO refpairs VALUES (100, 'one', 3), (200, 'two', null) 859 860 statement error pgcode 23503 update on table "pairs" violates foreign key constraint "fk_a_ref_pairs" on table "refpairs"\nDETAIL: Key \(src, dest\)=\(200, 'two'\) is still referenced from table "refpairs"\. 861 UPDATE pairs SET dest = 'too' WHERE id = 2 862 863 statement error pgcode 23503 delete on table "pairs" violates foreign key constraint "fk_a_ref_pairs" on table "refpairs"\nDETAIL: Key \(src, dest\)=\(200, 'two'\) is still referenced from table "refpairs"\. 864 DELETE FROM pairs WHERE id = 2 865 866 statement error pgcode 23503 delete on table "pairs" violates foreign key constraint "fk_a_ref_pairs" on table "refpairs"\nDETAIL: Key \(src, dest\)=\(100, 'one'\) is still referenced from table "refpairs"\. 867 DELETE FROM pairs WHERE id = 1 868 869 # since PKs are handled differently than other indexes, check pk<->pk ref with no other indexes in play. 870 statement ok 871 CREATE TABLE foo (id INT PRIMARY KEY) 872 873 statement ok 874 CREATE TABLE bar (id INT PRIMARY KEY REFERENCES foo) 875 876 statement ok 877 INSERT INTO foo VALUES (2) 878 879 statement ok 880 INSERT INTO bar VALUES (2) 881 882 statement error pgcode 23503 delete on table "foo" violates foreign key constraint "fk_id_ref_foo" on table "bar"\nDETAIL: Key \(id\)=\(2\) is still referenced from table "bar"\. 883 DELETE FROM foo 884 885 statement ok 886 CREATE DATABASE otherdb 887 888 statement ok 889 CREATE TABLE otherdb.othertable (id INT PRIMARY KEY) 890 891 statement ok 892 CREATE TABLE crossdb (id INT PRIMARY KEY, FOREIGN KEY (id) REFERENCES otherdb.othertable) 893 894 statement error pgcode 23503 insert on table "crossdb" violates foreign key constraint "fk_id_ref_othertable"\nDETAIL: Key \(id\)=\(2\) is not present in table "othertable". 895 INSERT INTO crossdb VALUES (2) 896 897 statement ok 898 INSERT INTO otherdb.othertable VALUES (1), (2) 899 900 statement ok 901 INSERT INTO crossdb VALUES (2) 902 903 statement error pgcode 23503 delete on table "othertable" violates foreign key constraint "fk_id_ref_othertable" on table "crossdb"\nDETAIL: Key \(id\)=\(2\) is still referenced from table "crossdb"\. 904 DELETE FROM otherdb.othertable WHERE id = 2 905 906 statement error "othertable" is referenced by foreign key from table "crossdb" 907 DROP TABLE otherdb.othertable 908 909 statement ok 910 DROP TABLE otherdb.othertable, crossdb 911 912 statement ok 913 CREATE TABLE modules (id BIGSERIAL NOT NULL PRIMARY KEY) 914 915 statement ok 916 CREATE TABLE domains (id BIGSERIAL NOT NULL PRIMARY KEY) 917 918 # We'll use the unique index for the domain fk (since it is a prefix), but we 919 # we correctly only mark the prefix as used and thus still allow module_id to be 920 # used in another FK. 921 statement ok 922 CREATE TABLE domain_modules ( 923 id BIGSERIAL NOT NULL PRIMARY KEY, 924 domain_id BIGINT NOT NULL, 925 module_id BIGINT NOT NULL, 926 CONSTRAINT domain_modules_domain_id_fk FOREIGN KEY (domain_id) REFERENCES domains (id), 927 CONSTRAINT domain_modules_module_id_fk FOREIGN KEY (module_id) REFERENCES modules (id), 928 CONSTRAINT domain_modules_uq UNIQUE (domain_id, module_id) 929 ) 930 931 query TTTTB 932 SHOW CONSTRAINTS FROM domain_modules 933 ---- 934 domain_modules domain_modules_domain_id_fk FOREIGN KEY FOREIGN KEY (domain_id) REFERENCES domains(id) true 935 domain_modules domain_modules_module_id_fk FOREIGN KEY FOREIGN KEY (module_id) REFERENCES modules(id) true 936 domain_modules domain_modules_uq UNIQUE UNIQUE (domain_id ASC, module_id ASC) true 937 domain_modules primary PRIMARY KEY PRIMARY KEY (id ASC) true 938 939 statement ok 940 INSERT INTO modules VALUES(3) 941 942 statement error insert on table "domain_modules" violates foreign key constraint "domain_modules_domain_id_fk"\nDETAIL: Key \(domain_id\)=\(2\) is not present in table "domains" 943 INSERT INTO domain_modules VALUES (1, 2, 3) 944 945 statement ok 946 CREATE TABLE tx ( 947 id INT NOT NULL PRIMARY KEY 948 ) 949 950 statement ok 951 CREATE TABLE tx_leg ( 952 leg_id SERIAL NOT NULL PRIMARY KEY, 953 tx_id INT NOT NULL REFERENCES tx 954 ) 955 956 statement ok 957 BEGIN TRANSACTION 958 959 statement ok 960 INSERT INTO tx VALUES (2) 961 962 statement ok 963 INSERT INTO tx_leg VALUES (201, 2); 964 965 statement ok 966 INSERT INTO tx_leg VALUES (202, 2); 967 968 statement ok 969 COMMIT 970 971 statement ok 972 BEGIN TRANSACTION 973 974 statement error pgcode 23503 insert on table "tx_leg" violates foreign key constraint "fk_tx_id_ref_tx"\nDETAIL: Key \(tx_id\)=\(3\) is not present in table "tx" 975 INSERT INTO tx_leg VALUES (302, 3); 976 977 statement ok 978 COMMIT 979 980 statement ok 981 CREATE TABLE a (id SERIAL NOT NULL, self_id INT, b_id INT NOT NULL, PRIMARY KEY (id)) 982 983 statement ok 984 CREATE TABLE b (id SERIAL NOT NULL, PRIMARY KEY (id)) 985 986 # The index needed for the fk constraint is automatically added because the table is empty 987 statement ok 988 ALTER TABLE a ADD CONSTRAINT fk_self_id FOREIGN KEY (self_id) REFERENCES a; 989 990 # The index needed for the fk constraint is automatically added because the table is empty 991 statement ok 992 ALTER TABLE a ADD CONSTRAINT fk_b FOREIGN KEY (b_id) REFERENCES b; 993 994 statement ok 995 INSERT INTO b VALUES (1), (2), (3); 996 997 statement ok 998 INSERT INTO a VALUES (1, NULL, 1) 999 1000 statement ok 1001 INSERT INTO a VALUES (2, 1, 1), (3, 1, 2) 1002 1003 statement ok 1004 INSERT INTO a VALUES (4, 2, 2) 1005 1006 statement ok 1007 DELETE FROM b WHERE id = 3 1008 1009 statement error pgcode 23503 violates foreign key 1010 DELETE FROM b WHERE id = 2 1011 1012 statement error pgcode 23503 violates foreign key 1013 DELETE FROM a WHERE id = 1 1014 1015 statement ok 1016 DELETE FROM a WHERE id > 2 1017 1018 statement ok 1019 DELETE FROM b WHERE id = 2 1020 1021 statement ok 1022 DROP TABLE a 1023 1024 statement ok 1025 DROP TABLE b 1026 1027 # A CREATE TABLE with a FK reference within a transaction. 1028 statement ok 1029 CREATE TABLE referee (id INT PRIMARY KEY); 1030 1031 statement ok 1032 BEGIN TRANSACTION 1033 1034 statement ok 1035 CREATE TABLE refers ( 1036 a INT REFERENCES referee, 1037 b INT, 1038 INDEX b_idx (b), 1039 FAMILY "primary" (a, b, rowid) 1040 ) 1041 1042 # Add some schema changes within the same transaction to verify that a 1043 # table that isn't yet public can be modified. 1044 statement ok 1045 CREATE INDEX foo ON refers (a) 1046 1047 statement ok 1048 ALTER INDEX refers@b_idx RENAME TO another_idx 1049 1050 query TT 1051 SHOW CREATE TABLE refers 1052 ---- 1053 refers CREATE TABLE refers ( 1054 a INT8 NULL, 1055 b INT8 NULL, 1056 CONSTRAINT fk_a_ref_referee FOREIGN KEY (a) REFERENCES referee(id), 1057 INDEX another_idx (b ASC), 1058 INDEX refers_auto_index_fk_a_ref_referee (a ASC), 1059 INDEX foo (a ASC), 1060 FAMILY "primary" (a, b, rowid) 1061 ) 1062 1063 statement ok 1064 DROP INDEX refers@another_idx 1065 1066 # refers is not visible because it is in the ADD state. 1067 query TTT 1068 SHOW TABLES FROM test 1069 ---- 1070 public bar table 1071 public child table 1072 public customers table 1073 public delivery table 1074 public domain_modules table 1075 public domains table 1076 public foo table 1077 public grandchild table 1078 public modules table 1079 public pairs table 1080 public referee table 1081 public refpairs table 1082 public refpairs_c_between table 1083 public refpairs_wrong_order table 1084 public tx table 1085 public tx_leg table 1086 public unindexed table 1087 1088 statement ok 1089 COMMIT 1090 1091 # CREATE AND DROP a table with a fk in the same transaction. 1092 statement ok 1093 BEGIN TRANSACTION 1094 1095 statement ok 1096 CREATE TABLE refers1 (a INT REFERENCES referee); 1097 1098 statement ok 1099 DROP TABLE refers1 1100 1101 statement ok 1102 COMMIT 1103 1104 # Check that removing self-ref FK correctly removed backref too, #16070. 1105 statement ok 1106 CREATE TABLE employee ( 1107 id INT PRIMARY KEY, 1108 manager INT, 1109 UNIQUE (manager) 1110 ); 1111 1112 statement ok 1113 ALTER TABLE employee 1114 ADD CONSTRAINT emp_emp 1115 FOREIGN KEY (manager) 1116 REFERENCES employee; 1117 1118 statement ok 1119 ALTER TABLE employee 1120 DROP CONSTRAINT emp_emp; 1121 1122 statement ok 1123 SHOW CREATE TABLE employee; 1124 1125 # Ensure that tables with an fk reference from their pk appear correctly in 1126 # SHOW CREATE TABLE (#17596). 1127 statement ok 1128 CREATE TABLE pkref_a (a INT PRIMARY KEY) 1129 1130 statement ok 1131 CREATE TABLE pkref_b (b INT PRIMARY KEY REFERENCES pkref_a ON UPDATE NO ACTION ON DELETE RESTRICT) 1132 1133 query TT 1134 SHOW CREATE TABLE pkref_b 1135 ---- 1136 pkref_b CREATE TABLE pkref_b ( 1137 b INT8 NOT NULL, 1138 CONSTRAINT "primary" PRIMARY KEY (b ASC), 1139 CONSTRAINT fk_b_ref_pkref_a FOREIGN KEY (b) REFERENCES pkref_a(a) ON DELETE RESTRICT, 1140 FAMILY "primary" (b) 1141 ) 1142 1143 subtest 20042 1144 1145 statement ok 1146 CREATE TABLE test20042 ( 1147 x STRING PRIMARY KEY 1148 ,y STRING UNIQUE 1149 ,z STRING REFERENCES test20042(y) 1150 ); 1151 1152 statement ok 1153 INSERT INTO test20042 (x, y, z) VALUES ('pk1', 'k1', null); 1154 1155 statement ok 1156 INSERT INTO test20042 (x, y, z) VALUES ('pk2', 'k2 ', 'k1'); 1157 1158 statement ok 1159 DELETE FROM test20042 WHERE x = 'pk2'; 1160 1161 statement ok 1162 DELETE FROM test20042 WHERE x = 'pk1'; 1163 1164 subtest 20045 1165 1166 statement ok 1167 CREATE TABLE test20045 ( 1168 x STRING PRIMARY KEY 1169 ,y STRING UNIQUE REFERENCES test20045(x) 1170 ,z STRING REFERENCES test20045(y) 1171 ); 1172 1173 statement ok 1174 INSERT INTO test20045 (x, y, z) VALUES ('pk1', NULL, NULL); 1175 1176 statement ok 1177 INSERT INTO test20045 (x, y, z) VALUES ('pk2', 'pk1', NULL); 1178 1179 statement ok 1180 INSERT INTO test20045 (x, y, z) VALUES ('pk3', 'pk2', 'pk1'); 1181 1182 statement ok 1183 DELETE FROM test20045 WHERE x = 'pk3'; 1184 1185 statement ok 1186 DELETE FROM test20045 WHERE x = 'pk2'; 1187 1188 statement ok 1189 DELETE FROM test20045 WHERE x = 'pk1'; 1190 1191 ## Delete cascade without privileges 1192 1193 statement ok 1194 CREATE DATABASE d; 1195 1196 statement ok 1197 CREATE TABLE d.a ( 1198 id STRING PRIMARY KEY 1199 ); 1200 1201 statement ok 1202 CREATE TABLE d.b ( 1203 id STRING PRIMARY KEY 1204 ,a_id STRING REFERENCES d.a ON DELETE CASCADE 1205 ); 1206 1207 statement ok 1208 INSERT INTO d.a VALUES ('a1'); 1209 1210 statement ok 1211 INSERT INTO d.b VALUES ('b1', 'a1'); 1212 1213 statement ok 1214 GRANT ALL ON DATABASE d TO testuser; 1215 1216 statement ok 1217 GRANT ALL ON d.a TO testuser; 1218 1219 user testuser 1220 1221 statement error user testuser does not have SELECT privilege on relation b 1222 DELETE FROM d.a WHERE id = 'a1'; 1223 1224 user root 1225 1226 statement ok 1227 GRANT SELECT ON d.b TO testuser; 1228 1229 user testuser 1230 1231 statement error user testuser does not have DELETE privilege on relation b 1232 DELETE FROM d.a WHERE id = 'a1'; 1233 1234 user root 1235 1236 statement ok 1237 GRANT DELETE ON d.b TO testuser; 1238 1239 user testuser 1240 1241 statement ok 1242 DELETE FROM d.a WHERE id = 'a1'; 1243 1244 user root 1245 1246 # Clean up after the test. 1247 statement ok 1248 DROP DATABASE d CASCADE; 1249 1250 subtest setNullWithNotNullConstraint 1251 ### Make sure that one cannot add a set null action on a NOT NULL column. 1252 1253 statement ok 1254 CREATE TABLE a ( 1255 id INT PRIMARY KEY 1256 ); 1257 1258 # Create a table with a NOT NULL column and a SET NULL action. 1259 statement error pq: cannot add a SET NULL cascading action on column "test.public.not_null_table.delete_not_nullable" which has a NOT NULL constraint 1260 CREATE TABLE not_null_table ( 1261 id INT PRIMARY KEY 1262 ,delete_not_nullable INT NOT NULL REFERENCES a ON DELETE SET NULL 1263 ); 1264 1265 statement error pq: cannot add a SET NULL cascading action on column "test.public.not_null_table.update_not_nullable" which has a NOT NULL constraint 1266 CREATE TABLE not_null_table ( 1267 id INT PRIMARY KEY 1268 ,update_not_nullable INT NOT NULL REFERENCES a ON UPDATE SET NULL 1269 ); 1270 1271 # Create a table where the primary key has a SET NULL action. 1272 statement error pq: cannot add a SET NULL cascading action on column "test.public.primary_key_table.id" which has a NOT NULL constraint 1273 CREATE TABLE primary_key_table ( 1274 id INT PRIMARY KEY REFERENCES a ON DELETE SET NULL 1275 ); 1276 1277 statement error pq: cannot add a SET NULL cascading action on column "test.public.primary_key_table.id" which has a NOT NULL constraint 1278 CREATE TABLE primary_key_table ( 1279 id INT PRIMARY KEY REFERENCES a ON UPDATE SET NULL 1280 ); 1281 1282 # Add a SET NULL action after the fact with a NOT NULL column. 1283 statement ok 1284 CREATE TABLE not_null_table ( 1285 id INT PRIMARY KEY 1286 ,delete_not_nullable INT NOT NULL 1287 ,update_not_nullable INT NOT NULL 1288 ); 1289 1290 statement error pq: cannot add a SET NULL cascading action on column "test.public.not_null_table.delete_not_nullable" which has a NOT NULL constraint 1291 ALTER TABLE not_null_table ADD CONSTRAINT not_null_delete_set_null 1292 FOREIGN KEY (delete_not_nullable) REFERENCES a (id) 1293 ON DELETE SET NULL; 1294 1295 statement error pq: cannot add a SET NULL cascading action on column "test.public.not_null_table.update_not_nullable" which has a NOT NULL constraint 1296 ALTER TABLE not_null_table ADD CONSTRAINT not_null_update_set_null 1297 FOREIGN KEY (update_not_nullable) REFERENCES a (id) 1298 ON UPDATE SET NULL; 1299 1300 # Clean up so far, 1301 statement ok 1302 DROP TABLE not_null_table; 1303 1304 # Add a SET NULL action after the fact with a primary key column. 1305 statement ok 1306 CREATE TABLE primary_key_table ( 1307 id INT PRIMARY KEY 1308 ); 1309 1310 statement error pq: cannot add a SET NULL cascading action on column "test.public.primary_key_table.id" which has a NOT NULL constraint 1311 ALTER TABLE primary_key_table ADD CONSTRAINT not_null_set_null 1312 FOREIGN KEY (id) REFERENCES a (id) 1313 ON DELETE SET NULL; 1314 1315 statement error pq: cannot add a SET NULL cascading action on column "test.public.primary_key_table.id" which has a NOT NULL constraint 1316 ALTER TABLE primary_key_table ADD CONSTRAINT not_null_set_null 1317 FOREIGN KEY (id) REFERENCES a (id) 1318 ON UPDATE SET NULL; 1319 1320 # Clean up the tables used so far. 1321 statement ok 1322 DROP TABLE primary_key_table, a; 1323 1324 # Now test composite foreign keys 1325 statement ok 1326 CREATE TABLE a ( 1327 id1 INT 1328 ,id2 INT 1329 ,PRIMARY KEY (id2, id1) 1330 ); 1331 1332 # Create a table with a NOT NULL column and a SET NULL action. 1333 statement error pq: cannot add a SET NULL cascading action on column "test.public.not_null_table.ref1" which has a NOT NULL constraint 1334 CREATE TABLE not_null_table ( 1335 id INT PRIMARY KEY 1336 ,ref1 INT NOT NULL 1337 ,ref2 INT NOT NULL 1338 ,INDEX (ref1, ref2) 1339 ,FOREIGN KEY (ref1, ref2) REFERENCES a (id2, id1) ON DELETE SET NULL 1340 ); 1341 1342 statement error pq: cannot add a SET NULL cascading action on column "test.public.not_null_table.ref1" which has a NOT NULL constraint 1343 CREATE TABLE not_null_table ( 1344 id INT PRIMARY KEY 1345 ,ref1 INT NOT NULL 1346 ,ref2 INT NOT NULL 1347 ,INDEX (ref1, ref2) 1348 ,FOREIGN KEY (ref1, ref2) REFERENCES a (id2, id1) ON UPDATE SET NULL 1349 ); 1350 1351 statement error pq: cannot add a SET NULL cascading action on column "test.public.not_null_table.ref1" which has a NOT NULL constraint 1352 CREATE TABLE not_null_table ( 1353 id INT PRIMARY KEY 1354 ,ref1 INT NOT NULL 1355 ,ref2 INT 1356 ,INDEX (ref1, ref2) 1357 ,FOREIGN KEY (ref1, ref2) REFERENCES a (id2, id1) ON DELETE SET NULL 1358 ); 1359 1360 statement error pq: cannot add a SET NULL cascading action on column "test.public.not_null_table.ref1" which has a NOT NULL constraint 1361 CREATE TABLE not_null_table ( 1362 id INT PRIMARY KEY 1363 ,ref1 INT NOT NULL 1364 ,ref2 INT 1365 ,INDEX (ref1, ref2) 1366 ,FOREIGN KEY (ref1, ref2) REFERENCES a (id2, id1) ON UPDATE SET NULL 1367 ); 1368 1369 statement error pq: cannot add a SET NULL cascading action on column "test.public.not_null_table.ref2" which has a NOT NULL constraint 1370 CREATE TABLE not_null_table ( 1371 id INT PRIMARY KEY 1372 ,ref1 INT 1373 ,ref2 INT NOT NULL 1374 ,INDEX (ref1, ref2) 1375 ,FOREIGN KEY (ref1, ref2) REFERENCES a (id2, id1) ON DELETE SET NULL 1376 ); 1377 1378 statement error pq: cannot add a SET NULL cascading action on column "test.public.not_null_table.ref2" which has a NOT NULL constraint 1379 CREATE TABLE not_null_table ( 1380 id INT PRIMARY KEY 1381 ,ref1 INT 1382 ,ref2 INT NOT NULL 1383 ,INDEX (ref1, ref2) 1384 ,FOREIGN KEY (ref1, ref2) REFERENCES a (id2, id1) ON UPDATE SET NULL 1385 ); 1386 1387 # Create a table where the primary key has a SET NULL action. 1388 statement error pq: cannot add a SET NULL cascading action on column "test.public.primary_key_table.ref1" which has a NOT NULL constraint 1389 CREATE TABLE primary_key_table ( 1390 ref1 INT 1391 ,ref2 INT 1392 ,PRIMARY KEY (ref2, ref1) 1393 ,FOREIGN KEY (ref1, ref2) REFERENCES a (id2, id1) ON DELETE SET NULL 1394 ); 1395 1396 # Create a table where the primary key has a SET NULL action. 1397 statement error pq: cannot add a SET NULL cascading action on column "test.public.primary_key_table.ref1" which has a NOT NULL constraint 1398 CREATE TABLE primary_key_table ( 1399 ref1 INT 1400 ,ref2 INT 1401 ,PRIMARY KEY (ref2, ref1) 1402 ,FOREIGN KEY (ref1, ref2) REFERENCES a (id2, id1) ON UPDATE SET NULL 1403 ); 1404 1405 statement error pq: cannot add a SET NULL cascading action on column "test.public.primary_key_table.ref2" which has a NOT NULL constraint 1406 CREATE TABLE primary_key_table ( 1407 ref1 INT 1408 ,ref2 INT 1409 ,PRIMARY KEY (ref2, ref1) 1410 ,FOREIGN KEY (ref2, ref1) REFERENCES a (id2, id1) ON DELETE SET NULL 1411 ); 1412 1413 statement error pq: cannot add a SET NULL cascading action on column "test.public.primary_key_table.ref2" which has a NOT NULL constraint 1414 CREATE TABLE primary_key_table ( 1415 ref1 INT 1416 ,ref2 INT 1417 ,PRIMARY KEY (ref2, ref1) 1418 ,FOREIGN KEY (ref2, ref1) REFERENCES a (id2, id1) ON UPDATE SET NULL 1419 ); 1420 1421 # Clean up after the test. 1422 statement ok 1423 DROP TABLE a; 1424 1425 subtest setDefaultWithoutDefault 1426 ### Make sure that one cannot add a SET DEFAULT action with no default values 1427 ### on a column. 1428 1429 statement ok 1430 CREATE TABLE a ( 1431 id INT PRIMARY KEY 1432 ); 1433 1434 # Create a table with no DEFAULT expressions column and a SET DEFAULT action. 1435 statement ok 1436 CREATE TABLE delete_no_default_table ( 1437 id INT PRIMARY KEY 1438 ,delete_no_default INT REFERENCES a ON DELETE SET DEFAULT 1439 ); 1440 1441 statement error pq: cannot add a SET DEFAULT cascading action on column "test.public.update_no_default_table.update_no_default" which has a NOT NULL constraint and a NULL default expression 1442 CREATE TABLE update_no_default_table ( 1443 id INT PRIMARY KEY 1444 ,update_no_default INT NOT NULL REFERENCES a ON UPDATE SET DEFAULT 1445 ); 1446 1447 # Create a table where the primary key has a SET DEFAULT action. 1448 # Primary keys are not allowed to be NULL 1449 statement error pq: cannot add a SET DEFAULT cascading action on column "test.public.primary_key_table_set_default.id" which has a NOT NULL constraint and a NULL default expression 1450 CREATE TABLE primary_key_table_set_default ( 1451 id INT PRIMARY KEY REFERENCES a ON DELETE SET DEFAULT 1452 ); 1453 1454 statement error pq: cannot add a SET DEFAULT cascading action on column "test.public.primary_key_table.id" which has a NOT NULL constraint and a NULL default expression 1455 CREATE TABLE primary_key_table ( 1456 id INT PRIMARY KEY REFERENCES a ON UPDATE SET DEFAULT 1457 ); 1458 1459 # Add a SET DEFAULT action after the to a column with no DEFAULT expression. 1460 statement ok 1461 CREATE TABLE no_default_table ( 1462 id INT PRIMARY KEY 1463 ,delete_no_default INT 1464 ,update_no_default INT 1465 ); 1466 1467 statement ok 1468 ALTER TABLE no_default_table ADD CONSTRAINT no_default_delete_set_default 1469 FOREIGN KEY (delete_no_default) REFERENCES a (id) 1470 ON DELETE SET DEFAULT; 1471 1472 statement ok 1473 ALTER TABLE no_default_table ADD CONSTRAINT no_default_update_set_default 1474 FOREIGN KEY (update_no_default) REFERENCES a (id) 1475 ON UPDATE SET DEFAULT; 1476 1477 # Clean up so far, 1478 statement ok 1479 DROP TABLE no_default_table; 1480 1481 # Add a SET DEFAULT action after the fact with a primary key column that has no 1482 # DEFAULT expression. 1483 statement ok 1484 CREATE TABLE primary_key_table ( 1485 id INT PRIMARY KEY 1486 ); 1487 1488 # id is a primary key and thus cannot be NULL 1489 statement error pq: cannot add a SET DEFAULT cascading action on column "test.public.primary_key_table.id" which has a NOT NULL constraint and a NULL default expression 1490 ALTER TABLE primary_key_table ADD CONSTRAINT no_default_delete_set_default 1491 FOREIGN KEY (id) REFERENCES a (id) 1492 ON DELETE SET DEFAULT; 1493 1494 statement error pq: cannot add a SET DEFAULT cascading action on column "test.public.primary_key_table.id" which has a NOT NULL constraint and a NULL default expression 1495 ALTER TABLE primary_key_table ADD CONSTRAINT no_default_update_set_default 1496 FOREIGN KEY (id) REFERENCES a (id) 1497 ON UPDATE SET DEFAULT; 1498 1499 # Clean up the tables used so far. 1500 statement ok 1501 DROP TABLE primary_key_table, delete_no_default_table, a; 1502 1503 # Now test composite foreign keys 1504 statement ok 1505 CREATE TABLE a ( 1506 id1 INT 1507 ,id2 INT 1508 ,PRIMARY KEY (id2, id1) 1509 ); 1510 1511 # Create a table with a column without a DEFAULT expression and a SET DEFAULT action. 1512 statement ok 1513 CREATE TABLE no_default_table ( 1514 id INT PRIMARY KEY 1515 ,ref1 INT 1516 ,ref2 INT 1517 ,INDEX (ref1, ref2) 1518 ,FOREIGN KEY (ref1, ref2) REFERENCES a (id2, id1) ON DELETE SET DEFAULT 1519 ); 1520 1521 statement ok 1522 INSERT INTO a VALUES (1, 2) 1523 1524 statement ok 1525 INSERT INTO a VALUES (3, 4) 1526 1527 statement ok 1528 INSERT INTO no_default_table VALUES (6, 2, 1) 1529 1530 query III colnames 1531 SELECT * FROM no_default_table 1532 ---- 1533 id ref1 ref2 1534 6 2 1 1535 1536 statement ok 1537 DELETE FROM a WHERE id1=1 1538 1539 query III colnames 1540 SELECT * FROM no_default_table 1541 ---- 1542 id ref1 ref2 1543 6 NULL NULL 1544 1545 statement ok 1546 CREATE TABLE no_default_table_on_update ( 1547 id INT PRIMARY KEY 1548 ,ref1 INT 1549 ,ref2 INT 1550 ,INDEX (ref1, ref2) 1551 ,FOREIGN KEY (ref1, ref2) REFERENCES a (id2, id1) ON UPDATE SET DEFAULT 1552 ); 1553 1554 statement ok 1555 INSERT INTO no_default_table_on_update VALUES (0, 4, 3) 1556 1557 query III colnames 1558 SELECT * FROM no_default_table_on_update 1559 ---- 1560 id ref1 ref2 1561 0 4 3 1562 1563 statement ok 1564 UPDATE a SET id1=33, id2=44 WHERE id1=3; 1565 1566 query III colnames 1567 SELECT * FROM no_default_table_on_update 1568 ---- 1569 id ref1 ref2 1570 0 NULL NULL 1571 1572 statement ok 1573 CREATE TABLE no_default_table_ref2_default_on_delete ( 1574 id INT PRIMARY KEY 1575 ,ref1 INT 1576 ,ref2 INT DEFAULT 1 1577 ,INDEX (ref1, ref2) 1578 ,FOREIGN KEY (ref1, ref2) REFERENCES a (id2, id1) ON DELETE SET DEFAULT 1579 ); 1580 1581 statement ok 1582 CREATE TABLE no_default_table_ref2_default_on_update ( 1583 id INT PRIMARY KEY 1584 ,ref1 INT 1585 ,ref2 INT DEFAULT 1 1586 ,INDEX (ref1, ref2) 1587 ,FOREIGN KEY (ref1, ref2) REFERENCES a (id2, id1) ON UPDATE SET DEFAULT 1588 ); 1589 1590 statement ok 1591 CREATE TABLE no_default_table_ref1_default_on_delete ( 1592 id INT PRIMARY KEY 1593 ,ref1 INT DEFAULT 1 1594 ,ref2 INT 1595 ,INDEX (ref1, ref2) 1596 ,FOREIGN KEY (ref1, ref2) REFERENCES a (id2, id1) ON DELETE SET DEFAULT 1597 ); 1598 1599 statement ok 1600 CREATE TABLE no_default_table_ref1_default_on_update ( 1601 id INT PRIMARY KEY 1602 ,ref1 INT DEFAULT 1 1603 ,ref2 INT 1604 ,INDEX (ref1, ref2) 1605 ,FOREIGN KEY (ref1, ref2) REFERENCES a (id2, id1) ON UPDATE SET DEFAULT 1606 ); 1607 1608 # Create a table with a NOT NULL column and a SET NULL action. 1609 statement error pq: cannot add a SET DEFAULT cascading action on column "test.public.not_null_table.ref1" which has a NOT NULL constraint and a NULL default expression 1610 CREATE TABLE not_null_table ( 1611 id INT PRIMARY KEY 1612 ,ref1 INT NOT NULL 1613 ,ref2 INT NOT NULL 1614 ,INDEX (ref1, ref2) 1615 ,FOREIGN KEY (ref1, ref2) REFERENCES a (id2, id1) ON DELETE SET DEFAULT 1616 ); 1617 1618 # Clean up after the test. 1619 statement ok 1620 DROP TABLE a, no_default_table, no_default_table_on_update, no_default_table_ref2_default_on_delete, 1621 no_default_table_ref2_default_on_update, no_default_table_ref1_default_on_delete, 1622 no_default_table_ref1_default_on_update 1623 1624 subtest unvalidated_fk_plan 1625 1626 # To get an unvalidated foreign key for testing, use the loophole that we 1627 # currently don't support adding a validated FK in the same transaction as 1628 # CREATE TABLE 1629 1630 statement ok 1631 CREATE TABLE a ( 1632 x STRING NULL, 1633 y STRING NULL, 1634 z STRING NULL, 1635 CONSTRAINT "primary" PRIMARY KEY (z, y, x) 1636 ) 1637 1638 statement ok 1639 CREATE TABLE b ( 1640 a_y STRING NULL, 1641 a_x STRING NULL, 1642 a_z STRING NULL, 1643 INDEX idx (a_z, a_y, a_x) 1644 ) 1645 1646 statement ok 1647 INSERT INTO b (a_x, a_y, a_z) VALUES ('x2', 'y1', 'z1') 1648 1649 statement ok 1650 ALTER TABLE b ADD CONSTRAINT fk_ref FOREIGN KEY (a_z, a_y, a_x) REFERENCES a (z, y, x) NOT VALID 1651 1652 statement error pq: foreign key violation: "b" row a_z='z1', a_y='y1', a_x='x2', rowid=[0-9]* has no match in "a" 1653 ALTER TABLE b VALIDATE CONSTRAINT fk_ref 1654 1655 # Verify that the optimizer doesn't use an unvalidated constraint to simplify plans. 1656 query TTT 1657 SELECT 1658 s.a_z, s.a_y, s.a_x 1659 FROM 1660 (SELECT * FROM b WHERE a_z IS NOT NULL AND a_y IS NOT NULL AND a_x IS NOT NULL) AS s 1661 LEFT JOIN a AS t ON s.a_z = t.z AND s.a_y = t.y AND s.a_x = t.x 1662 WHERE 1663 t.z IS NULL 1664 ---- 1665 z1 y1 x2 1666 1667 statement ok 1668 DROP TABLE a, b 1669 1670 subtest Composite_Simple 1671 # Originally from 26748. 1672 1673 # Test composite key with two columns. 1674 statement ok 1675 CREATE TABLE a ( 1676 x STRING NULL 1677 ,y STRING NULL 1678 ,CONSTRAINT "primary" PRIMARY KEY (y, x) 1679 ); 1680 1681 statement ok 1682 CREATE TABLE b ( 1683 a_y STRING NULL 1684 ,a_x STRING NULL 1685 ,CONSTRAINT fk_ref FOREIGN KEY (a_y, a_x) REFERENCES a (y, x) 1686 ); 1687 1688 statement ok 1689 INSERT INTO a (x, y) VALUES ('x1', 'y1') 1690 1691 # All of these are allowed because we do composite matching using MATCH SIMPLE. 1692 statement ok 1693 INSERT INTO b (a_x) VALUES ('x1') 1694 1695 statement ok 1696 INSERT INTO b (a_y) VALUES ('y1') 1697 1698 statement ok 1699 INSERT INTO b (a_y, a_x) VALUES ('y1', NULL) 1700 1701 statement ok 1702 INSERT INTO b (a_y, a_x) VALUES (NULL, 'x1') 1703 1704 statement ok 1705 INSERT INTO b (a_x, a_y) VALUES ('x1', 'y1') 1706 1707 statement ok 1708 INSERT INTO b (a_x, a_y) VALUES (NULL, NULL) 1709 1710 statement ok 1711 DROP TABLE b, a 1712 1713 # Test composite key with three columns. 1714 statement ok 1715 CREATE TABLE a ( 1716 x STRING NULL 1717 ,y STRING NULL 1718 ,z STRING NULL 1719 ,CONSTRAINT "primary" PRIMARY KEY (z, y, x) 1720 ); 1721 1722 statement ok 1723 CREATE TABLE b ( 1724 a_y STRING NULL 1725 ,a_x STRING NULL 1726 ,a_z STRING NULL 1727 ,CONSTRAINT fk_ref FOREIGN KEY (a_z, a_y, a_x) REFERENCES a (z, y, x) 1728 ); 1729 1730 statement ok 1731 INSERT INTO a (x, y, z) VALUES ('x1', 'y1', 'z1') 1732 1733 # All of these are allowed because we do composite matching using MATCH SIMPLE. 1734 statement ok 1735 INSERT INTO b (a_x) VALUES ('x1') 1736 1737 statement ok 1738 INSERT INTO b (a_y) VALUES ('y1') 1739 1740 statement ok 1741 INSERT INTO b (a_z) VALUES ('z1') 1742 1743 statement ok 1744 INSERT INTO b (a_x, a_y) VALUES ('x1', 'y1') 1745 1746 statement ok 1747 INSERT INTO b (a_x, a_y) VALUES (NULL, 'y1') 1748 1749 statement ok 1750 INSERT INTO b (a_x, a_y) VALUES ('x1', NULL) 1751 1752 statement ok 1753 INSERT INTO b (a_x, a_z) VALUES ('x1', 'z1') 1754 1755 statement ok 1756 INSERT INTO b (a_x, a_z) VALUES (NULL, 'z1') 1757 1758 statement ok 1759 INSERT INTO b (a_x, a_z) VALUES ('x1', NULL) 1760 1761 statement ok 1762 INSERT INTO b (a_y, a_z) VALUES ('y1', 'z1') 1763 1764 statement ok 1765 INSERT INTO b (a_y, a_z) VALUES (NULL, 'z1') 1766 1767 statement ok 1768 INSERT INTO b (a_y, a_z) VALUES ('y1', NULL) 1769 1770 statement ok 1771 INSERT INTO b (a_x, a_y, a_z) VALUES ('x1', NULL, NULL) 1772 1773 statement ok 1774 INSERT INTO b (a_x, a_y, a_z) VALUES (NULL, 'y1', NULL) 1775 1776 statement ok 1777 INSERT INTO b (a_x, a_y, a_z) VALUES (NULL, NULL, 'z1') 1778 1779 statement ok 1780 INSERT INTO b (a_x, a_y, a_z) VALUES ('x1', 'y1', NULL) 1781 1782 statement ok 1783 INSERT INTO b (a_x, a_y, a_z) VALUES ('x1', NULL, 'z1') 1784 1785 statement ok 1786 INSERT INTO b (a_x, a_y, a_z) VALUES (NULL, 'y1', 'z1') 1787 1788 statement ok 1789 INSERT INTO b (a_x, a_y, a_z) VALUES (NULL, NULL, NULL) 1790 1791 statement ok 1792 INSERT INTO b (a_x, a_y, a_z) VALUES ('x2', NULL, NULL) 1793 1794 statement ok 1795 INSERT INTO b (a_x, a_y, a_z) VALUES (NULL, 'y2', NULL) 1796 1797 statement ok 1798 INSERT INTO b (a_x, a_y, a_z) VALUES (NULL, NULL, 'z2') 1799 1800 statement ok 1801 INSERT INTO b (a_x, a_y, a_z) VALUES ('x2', 'y2', NULL) 1802 1803 statement ok 1804 INSERT INTO b (a_x, a_y, a_z) VALUES ('x2', NULL, 'z2') 1805 1806 statement ok 1807 INSERT INTO b (a_x, a_y, a_z) VALUES (NULL, 'y2', 'z2') 1808 1809 statement ok 1810 DROP TABLE b, a 1811 1812 subtest Composite_Simple_Add_Constraint_Valid 1813 # Test ADD CONSTRAINT validation by inserting valid rows before the constraint is added. 1814 1815 statement ok 1816 CREATE TABLE a ( 1817 x STRING NULL 1818 ,y STRING NULL 1819 ,z STRING NULL 1820 ,CONSTRAINT "primary" PRIMARY KEY (z, y, x) 1821 ); 1822 1823 statement ok 1824 CREATE TABLE b ( 1825 a_y STRING NULL 1826 ,a_x STRING NULL 1827 ,a_z STRING NULL 1828 ,INDEX idx (a_z, a_y, a_x) 1829 ); 1830 1831 statement ok 1832 INSERT INTO a (x, y, z) VALUES ('x1', 'y1', 'z1') 1833 1834 # All of these are allowed because we do composite matching using MATCH SIMPLE. 1835 statement ok 1836 INSERT INTO b (a_x, a_y, a_z) VALUES ('x1', NULL, NULL) 1837 1838 statement ok 1839 INSERT INTO b (a_x, a_y, a_z) VALUES (NULL, 'y1', NULL) 1840 1841 statement ok 1842 INSERT INTO b (a_x, a_y, a_z) VALUES (NULL, NULL, 'z1') 1843 1844 statement ok 1845 INSERT INTO b (a_x, a_y, a_z) VALUES ('x1', 'y1', NULL) 1846 1847 statement ok 1848 INSERT INTO b (a_x, a_y, a_z) VALUES ('x1', NULL, 'z1') 1849 1850 statement ok 1851 INSERT INTO b (a_x, a_y, a_z) VALUES (NULL, 'y1', 'z1') 1852 1853 statement ok 1854 INSERT INTO b (a_x, a_y, a_z) VALUES (NULL, NULL, NULL) 1855 1856 statement ok 1857 INSERT INTO b (a_x, a_y, a_z) VALUES ('x2', NULL, NULL) 1858 1859 statement ok 1860 INSERT INTO b (a_x, a_y, a_z) VALUES (NULL, 'y2', NULL) 1861 1862 statement ok 1863 INSERT INTO b (a_x, a_y, a_z) VALUES (NULL, NULL, 'z2') 1864 1865 statement ok 1866 INSERT INTO b (a_x, a_y, a_z) VALUES ('x2', 'y2', NULL) 1867 1868 statement ok 1869 INSERT INTO b (a_x, a_y, a_z) VALUES ('x2', NULL, 'z2') 1870 1871 statement ok 1872 INSERT INTO b (a_x, a_y, a_z) VALUES (NULL, 'y2', 'z2') 1873 1874 statement ok 1875 ALTER TABLE b ADD CONSTRAINT fk_ref FOREIGN KEY (a_z, a_y, a_x) REFERENCES a (z, y, x) 1876 1877 statement ok 1878 DROP TABLE b, a 1879 1880 subtest Composite_Simple_Add_Constraint_Invalid 1881 # Test ADD CONSTRAINT validation by inserting invalid rows before the constraint is added, one at a time. 1882 1883 statement ok 1884 CREATE TABLE a ( 1885 x STRING NULL 1886 ,y STRING NULL 1887 ,z STRING NULL 1888 ,CONSTRAINT "primary" PRIMARY KEY (z, y, x) 1889 ); 1890 1891 statement ok 1892 CREATE TABLE b ( 1893 a_y STRING NULL 1894 ,a_x STRING NULL 1895 ,a_z STRING NULL 1896 ,INDEX idx (a_z, a_y, a_x) 1897 ); 1898 1899 statement ok 1900 INSERT INTO b (a_x, a_y, a_z) VALUES ('x2', 'y1', 'z1') 1901 1902 statement error foreign key violation: "b" row a_z='z1', a_y='y1', a_x='x2', rowid=[0-9]* has no match in "a" 1903 ALTER TABLE b ADD CONSTRAINT fk_ref FOREIGN KEY (a_z, a_y, a_x) REFERENCES a (z, y, x) 1904 1905 statement ok 1906 TRUNCATE b 1907 1908 statement ok 1909 INSERT INTO b (a_x, a_y, a_z) VALUES ('x2', 'y2', 'z1') 1910 1911 statement error foreign key violation: "b" row a_z='z1', a_y='y2', a_x='x2', rowid=[0-9]* has no match in "a" 1912 ALTER TABLE b ADD CONSTRAINT fk_ref FOREIGN KEY (a_z, a_y, a_x) REFERENCES a (z, y, x) 1913 1914 statement ok 1915 TRUNCATE b 1916 1917 statement ok 1918 INSERT INTO b (a_x, a_y, a_z) VALUES ('x2', 'y2', 'z2') 1919 1920 statement error foreign key violation: "b" row a_z='z2', a_y='y2', a_x='x2', rowid=[0-9]* has no match in "a" 1921 ALTER TABLE b ADD CONSTRAINT fk_ref FOREIGN KEY (a_z, a_y, a_x) REFERENCES a (z, y, x) 1922 1923 statement ok 1924 DROP TABLE b, a 1925 1926 subtest Composite_Simple_Unvalidated 1927 # Test inserting into table with an unvalidated constraint, and running VALIDATE CONSTRAINT later 1928 1929 # Test composite key with two columns. 1930 statement ok 1931 CREATE TABLE a ( 1932 x STRING NULL 1933 ,y STRING NULL 1934 ,CONSTRAINT "primary" PRIMARY KEY (y, x) 1935 ); 1936 1937 statement ok 1938 CREATE TABLE b ( 1939 a_y STRING NULL 1940 ,a_x STRING NULL 1941 ); 1942 1943 # Add the constraint separately so that it's unvalidated, so we can test VALIDATE CONSTRAINT. 1944 statement ok 1945 ALTER TABLE b ADD CONSTRAINT fk_ref FOREIGN KEY (a_y, a_x) REFERENCES a (y, x) NOT VALID 1946 1947 statement ok 1948 INSERT INTO a (x, y) VALUES ('x1', 'y1') 1949 1950 # All of these are allowed because we do composite matching using MATCH SIMPLE. 1951 statement ok 1952 INSERT INTO b (a_x) VALUES ('x1') 1953 1954 statement ok 1955 INSERT INTO b (a_y) VALUES ('y1') 1956 1957 statement ok 1958 INSERT INTO b (a_y, a_x) VALUES ('y1', NULL) 1959 1960 statement ok 1961 INSERT INTO b (a_y, a_x) VALUES (NULL, 'x1') 1962 1963 statement ok 1964 INSERT INTO b (a_y, a_x) VALUES ('y2', NULL) 1965 1966 statement ok 1967 INSERT INTO b (a_y, a_x) VALUES (NULL, 'x2') 1968 1969 statement ok 1970 INSERT INTO b (a_x, a_y) VALUES ('x1', 'y1') 1971 1972 statement ok 1973 INSERT INTO b (a_x, a_y) VALUES (NULL, NULL) 1974 1975 statement ok 1976 ALTER TABLE b VALIDATE CONSTRAINT fk_ref 1977 1978 statement ok 1979 DROP TABLE b, a 1980 1981 # Test composite key with three columns. 1982 statement ok 1983 CREATE TABLE a ( 1984 x STRING NULL 1985 ,y STRING NULL 1986 ,z STRING NULL 1987 ,CONSTRAINT "primary" PRIMARY KEY (z, y, x) 1988 ); 1989 1990 statement ok 1991 CREATE TABLE b ( 1992 a_y STRING NULL 1993 ,a_x STRING NULL 1994 ,a_z STRING NULL 1995 ); 1996 1997 # Add the constraint separately so that it's unvalidated, so we can test VALIDATE CONSTRAINT. 1998 statement ok 1999 ALTER TABLE b ADD CONSTRAINT fk_ref FOREIGN KEY (a_z, a_y, a_x) REFERENCES a (z, y, x) NOT VALID 2000 2001 statement ok 2002 INSERT INTO a (x, y, z) VALUES ('x1', 'y1', 'z1') 2003 2004 # All of these are allowed because we do composite matching using MATCH SIMPLE. 2005 statement ok 2006 INSERT INTO b (a_x) VALUES ('x1') 2007 2008 statement ok 2009 INSERT INTO b (a_y) VALUES ('y1') 2010 2011 statement ok 2012 INSERT INTO b (a_z) VALUES ('z1') 2013 2014 statement ok 2015 INSERT INTO b (a_x, a_y) VALUES ('x1', 'y1') 2016 2017 statement ok 2018 INSERT INTO b (a_x, a_y) VALUES (NULL, 'y1') 2019 2020 statement ok 2021 INSERT INTO b (a_x, a_y) VALUES ('x1', NULL) 2022 2023 statement ok 2024 INSERT INTO b (a_x, a_z) VALUES ('x1', 'z1') 2025 2026 statement ok 2027 INSERT INTO b (a_x, a_z) VALUES (NULL, 'z1') 2028 2029 statement ok 2030 INSERT INTO b (a_x, a_z) VALUES ('x1', NULL) 2031 2032 statement ok 2033 INSERT INTO b (a_y, a_z) VALUES ('y1', 'z1') 2034 2035 statement ok 2036 INSERT INTO b (a_y, a_z) VALUES (NULL, 'z1') 2037 2038 statement ok 2039 INSERT INTO b (a_y, a_z) VALUES ('y1', NULL) 2040 2041 statement ok 2042 INSERT INTO b (a_x, a_y, a_z) VALUES ('x1', NULL, NULL) 2043 2044 statement ok 2045 INSERT INTO b (a_x, a_y, a_z) VALUES (NULL, 'y1', NULL) 2046 2047 statement ok 2048 INSERT INTO b (a_x, a_y, a_z) VALUES (NULL, NULL, 'z1') 2049 2050 statement ok 2051 INSERT INTO b (a_x, a_y, a_z) VALUES ('x1', 'y1', NULL) 2052 2053 statement ok 2054 INSERT INTO b (a_x, a_y, a_z) VALUES ('x1', NULL, 'z1') 2055 2056 statement ok 2057 INSERT INTO b (a_x, a_y, a_z) VALUES (NULL, 'y1', 'z1') 2058 2059 statement ok 2060 INSERT INTO b (a_x, a_y, a_z) VALUES ('x2', NULL, NULL) 2061 2062 statement ok 2063 INSERT INTO b (a_x, a_y, a_z) VALUES (NULL, 'y2', NULL) 2064 2065 statement ok 2066 INSERT INTO b (a_x, a_y, a_z) VALUES (NULL, NULL, 'z2') 2067 2068 statement ok 2069 INSERT INTO b (a_x, a_y, a_z) VALUES ('x2', 'y2', NULL) 2070 2071 statement ok 2072 INSERT INTO b (a_x, a_y, a_z) VALUES ('x2', NULL, 'z2') 2073 2074 statement ok 2075 INSERT INTO b (a_x, a_y, a_z) VALUES (NULL, 'y2', 'z2') 2076 2077 statement ok 2078 INSERT INTO b (a_x, a_y, a_z) VALUES (NULL, NULL, NULL) 2079 2080 statement ok 2081 ALTER TABLE b VALIDATE CONSTRAINT fk_ref 2082 2083 statement ok 2084 DROP TABLE b, a 2085 2086 #subtest Composite_Simple_Validate_Constraint_Invalid 2087 2088 subtest Composite_Full 2089 # Originally from 26748. 2090 2091 # Test composite key with two columns. 2092 statement ok 2093 CREATE TABLE a ( 2094 x STRING NULL, 2095 y STRING NULL, 2096 CONSTRAINT "primary" PRIMARY KEY (y, x) 2097 ); 2098 2099 statement ok 2100 CREATE TABLE b ( 2101 a_y STRING NULL, 2102 a_x STRING NULL 2103 ); 2104 2105 # Add the constraint separately so that it's unvalidated, so we can test VALIDATE CONSTRAINT. 2106 statement ok 2107 ALTER TABLE b ADD CONSTRAINT fk_ref FOREIGN KEY (a_y, a_x) REFERENCES a (y, x) MATCH FULL NOT VALID 2108 2109 statement ok 2110 INSERT INTO a (x, y) VALUES ('x1', 'y1') 2111 2112 # These statements should all fail because this uses MATCH FULL. 2113 statement error missing value for column "a_y" in multi-part foreign key 2114 INSERT INTO b (a_x) VALUES ('x1') 2115 2116 statement error missing value for column "a_x" in multi-part foreign key 2117 INSERT INTO b (a_y) VALUES ('y1') 2118 2119 statement error insert on table "b" violates foreign key constraint "fk_ref"\nDETAIL: MATCH FULL does not allow mixing of null and nonnull key values 2120 INSERT INTO b (a_y, a_x) VALUES ('y1', NULL) 2121 2122 statement error insert on table "b" violates foreign key constraint "fk_ref"\nDETAIL: MATCH FULL does not allow mixing of null and nonnull key values 2123 INSERT INTO b (a_y, a_x) VALUES (NULL, 'x1') 2124 2125 # These next two statements should still be allowed. 2126 statement ok 2127 INSERT INTO b (a_x, a_y) VALUES ('x1', 'y1') 2128 2129 statement ok 2130 INSERT INTO b (a_x, a_y) VALUES (NULL, NULL) 2131 2132 statement ok 2133 DROP TABLE b, a 2134 2135 # Test composite key with three columns. 2136 statement ok 2137 CREATE TABLE a ( 2138 x STRING NULL, 2139 y STRING NULL, 2140 z STRING NULL, 2141 CONSTRAINT "primary" PRIMARY KEY (z, y, x) 2142 ); 2143 2144 statement ok 2145 CREATE TABLE b ( 2146 a_y STRING NULL, 2147 a_x STRING NULL, 2148 a_z STRING NULL 2149 ); 2150 2151 statement ok 2152 ALTER TABLE b ADD CONSTRAINT fk_ref FOREIGN KEY (a_z, a_y, a_x) REFERENCES a (z, y, x) MATCH FULL NOT VALID 2153 2154 statement ok 2155 INSERT INTO a (x, y, z) VALUES ('x1', 'y1', 'z1') 2156 2157 # These statements should all fail because this uses MATCH FULL. 2158 statement error missing values for columns \["a_y" "a_z"\] in multi-part foreign key 2159 INSERT INTO b (a_x) VALUES ('x1') 2160 2161 statement error missing values for columns \["a_x" "a_z"\] in multi-part foreign key 2162 INSERT INTO b (a_y) VALUES ('y1') 2163 2164 statement error missing values for columns \["a_x" "a_y"\] in multi-part foreign key 2165 INSERT INTO b (a_z) VALUES ('z1') 2166 2167 statement error missing value for column "a_z" in multi-part foreign key 2168 INSERT INTO b (a_x, a_y) VALUES ('x1', 'y1') 2169 2170 statement error missing value for column "a_z" in multi-part foreign key 2171 INSERT INTO b (a_x, a_y) VALUES (NULL, 'y1') 2172 2173 statement error missing value for column "a_z" in multi-part foreign key 2174 INSERT INTO b (a_x, a_y) VALUES ('x1', NULL) 2175 2176 statement error missing value for column "a_y" in multi-part foreign key 2177 INSERT INTO b (a_x, a_z) VALUES ('x1', 'z1') 2178 2179 statement error missing value for column "a_y" in multi-part foreign key 2180 INSERT INTO b (a_x, a_z) VALUES (NULL, 'z1') 2181 2182 statement error missing value for column "a_y" in multi-part foreign key 2183 INSERT INTO b (a_x, a_z) VALUES ('x1', NULL) 2184 2185 statement error missing value for column "a_x" in multi-part foreign key 2186 INSERT INTO b (a_y, a_z) VALUES ('y1', 'z1') 2187 2188 statement error missing value for column "a_x" in multi-part foreign key 2189 INSERT INTO b (a_y, a_z) VALUES (NULL, 'z1') 2190 2191 statement error missing value for column "a_x" in multi-part foreign key 2192 INSERT INTO b (a_y, a_z) VALUES ('y1', NULL) 2193 2194 statement error insert on table "b" violates foreign key constraint "fk_ref"\nDETAIL: MATCH FULL does not allow mixing of null and nonnull key values 2195 INSERT INTO b (a_x, a_y, a_z) VALUES ('x1', NULL, NULL) 2196 2197 statement error insert on table "b" violates foreign key constraint "fk_ref"\nDETAIL: MATCH FULL does not allow mixing of null and nonnull key values 2198 INSERT INTO b (a_x, a_y, a_z) VALUES (NULL, 'y1', NULL) 2199 2200 statement error insert on table "b" violates foreign key constraint "fk_ref"\nDETAIL: MATCH FULL does not allow mixing of null and nonnull key values 2201 INSERT INTO b (a_x, a_y, a_z) VALUES (NULL, NULL, 'z1') 2202 2203 statement error insert on table "b" violates foreign key constraint "fk_ref"\nDETAIL: MATCH FULL does not allow mixing of null and nonnull key values 2204 INSERT INTO b (a_x, a_y, a_z) VALUES ('x1', 'y1', NULL) 2205 2206 statement error insert on table "b" violates foreign key constraint "fk_ref"\nDETAIL: MATCH FULL does not allow mixing of null and nonnull key values 2207 INSERT INTO b (a_x, a_y, a_z) VALUES ('x1', NULL, 'z1') 2208 2209 statement error insert on table "b" violates foreign key constraint "fk_ref"\nDETAIL: MATCH FULL does not allow mixing of null and nonnull key values 2210 INSERT INTO b (a_x, a_y, a_z) VALUES (NULL, 'y1', 'z1') 2211 2212 # This statement should still be allowed. 2213 statement ok 2214 INSERT INTO b (a_x, a_y, a_z) VALUES (NULL, NULL, NULL) 2215 2216 statement ok 2217 DROP TABLE b, a 2218 2219 subtest Composite_Full_Add_Constraint_Valid 2220 # Test ADD CONSTRAINT validation by inserting valid rows before the constraint is added. 2221 2222 statement ok 2223 CREATE TABLE a ( 2224 x STRING NULL 2225 ,y STRING NULL 2226 ,z STRING NULL 2227 ,CONSTRAINT "primary" PRIMARY KEY (z, y, x) 2228 ); 2229 2230 statement ok 2231 CREATE TABLE b ( 2232 a_y STRING NULL 2233 ,a_x STRING NULL 2234 ,a_z STRING NULL 2235 ,INDEX idx (a_z, a_y, a_x) 2236 ); 2237 2238 statement ok 2239 INSERT INTO a (x, y, z) VALUES ('x1', 'y1', 'z1') 2240 2241 # This statement should still be allowed. 2242 statement ok 2243 INSERT INTO b (a_x, a_y, a_z) VALUES (NULL, NULL, NULL) 2244 2245 statement ok 2246 ALTER TABLE b ADD CONSTRAINT fk_ref FOREIGN KEY (a_z, a_y, a_x) REFERENCES a (z, y, x) 2247 2248 statement ok 2249 DROP TABLE b, a 2250 2251 subtest Composite_Full_Validate_Constraint_Invalid 2252 # Test VALIDATE CONSTRAINT by inserting invalid rows before the constraint is added, one at a time. 2253 2254 statement ok 2255 CREATE TABLE a ( 2256 x STRING NULL 2257 ,y STRING NULL 2258 ,z STRING NULL 2259 ,CONSTRAINT "primary" PRIMARY KEY (z, y, x) 2260 ); 2261 2262 statement ok 2263 CREATE TABLE b ( 2264 a_y STRING NULL 2265 ,a_x STRING NULL 2266 ,a_z STRING NULL 2267 ,INDEX idx (a_z, a_y, a_x) 2268 ); 2269 2270 statement ok 2271 INSERT INTO b (a_x, a_y, a_z) VALUES ('x1', NULL, NULL) 2272 2273 statement error foreign key violation: MATCH FULL does not allow mixing of null and nonnull values 2274 ALTER TABLE b ADD CONSTRAINT fk_ref FOREIGN KEY (a_z, a_y, a_x) REFERENCES a (z, y, x) MATCH FULL 2275 2276 statement ok 2277 TRUNCATE b 2278 2279 statement ok 2280 INSERT INTO b (a_x, a_y, a_z) VALUES (NULL, 'y1', NULL) 2281 2282 statement error foreign key violation: MATCH FULL does not allow mixing of null and nonnull values 2283 ALTER TABLE b ADD CONSTRAINT fk_ref FOREIGN KEY (a_z, a_y, a_x) REFERENCES a (z, y, x) MATCH FULL 2284 2285 statement ok 2286 TRUNCATE b 2287 2288 statement ok 2289 INSERT INTO b (a_x, a_y, a_z) VALUES (NULL, NULL, 'z1') 2290 2291 statement error foreign key violation: MATCH FULL does not allow mixing of null and nonnull values 2292 ALTER TABLE b ADD CONSTRAINT fk_ref FOREIGN KEY (a_z, a_y, a_x) REFERENCES a (z, y, x) MATCH FULL 2293 2294 statement ok 2295 TRUNCATE b 2296 2297 statement ok 2298 INSERT INTO b (a_x, a_y, a_z) VALUES ('x1', 'y1', NULL) 2299 2300 statement error foreign key violation: MATCH FULL does not allow mixing of null and nonnull values 2301 ALTER TABLE b ADD CONSTRAINT fk_ref FOREIGN KEY (a_z, a_y, a_x) REFERENCES a (z, y, x) MATCH FULL 2302 2303 statement ok 2304 TRUNCATE b 2305 2306 statement ok 2307 INSERT INTO b (a_x, a_y, a_z) VALUES ('x1', NULL, 'z1') 2308 2309 statement error foreign key violation: MATCH FULL does not allow mixing of null and nonnull values 2310 ALTER TABLE b ADD CONSTRAINT fk_ref FOREIGN KEY (a_z, a_y, a_x) REFERENCES a (z, y, x) MATCH FULL 2311 2312 statement ok 2313 TRUNCATE b 2314 2315 statement ok 2316 INSERT INTO b (a_x, a_y, a_z) VALUES (NULL, 'y1', 'z1') 2317 2318 statement error foreign key violation: MATCH FULL does not allow mixing of null and nonnull values 2319 ALTER TABLE b ADD CONSTRAINT fk_ref FOREIGN KEY (a_z, a_y, a_x) REFERENCES a (z, y, x) MATCH FULL 2320 2321 statement ok 2322 TRUNCATE b 2323 2324 statement ok 2325 INSERT INTO b (a_x, a_y, a_z) VALUES ('x2', 'y1', 'z1') 2326 2327 statement error foreign key violation: "b" row a_z='z1', a_y='y1', a_x='x2', rowid=[0-9]* has no match in "a" 2328 ALTER TABLE b ADD CONSTRAINT fk_ref FOREIGN KEY (a_z, a_y, a_x) REFERENCES a (z, y, x) MATCH FULL 2329 2330 statement ok 2331 TRUNCATE b 2332 2333 statement ok 2334 INSERT INTO b (a_x, a_y, a_z) VALUES ('x2', 'y2', 'z1') 2335 2336 statement error foreign key violation: "b" row a_z='z1', a_y='y2', a_x='x2', rowid=[0-9]* has no match in "a" 2337 ALTER TABLE b ADD CONSTRAINT fk_ref FOREIGN KEY (a_z, a_y, a_x) REFERENCES a (z, y, x) MATCH FULL 2338 2339 statement ok 2340 TRUNCATE b 2341 2342 statement ok 2343 INSERT INTO b (a_x, a_y, a_z) VALUES ('x2', 'y2', 'z2') 2344 2345 statement error foreign key violation: "b" row a_z='z2', a_y='y2', a_x='x2', rowid=[0-9]* has no match in "a" 2346 ALTER TABLE b ADD CONSTRAINT fk_ref FOREIGN KEY (a_z, a_y, a_x) REFERENCES a (z, y, x) MATCH FULL 2347 2348 statement ok 2349 DROP TABLE b, a 2350 2351 subtest Composite_Full_Validate_Later 2352 # Test inserting into table with an unvalidated constraint, and running VALIDATE CONSTRAINT later 2353 2354 # Test composite key with two columns. 2355 statement ok 2356 CREATE TABLE a ( 2357 x STRING NULL 2358 ,y STRING NULL 2359 ,CONSTRAINT "primary" PRIMARY KEY (y, x) 2360 ); 2361 2362 statement ok 2363 CREATE TABLE b ( 2364 a_y STRING NULL 2365 ,a_x STRING NULL 2366 ); 2367 2368 # Add the constraint separately so that it's unvalidated, so we can test VALIDATE CONSTRAINT. 2369 statement ok 2370 ALTER TABLE b ADD CONSTRAINT fk_ref FOREIGN KEY (a_y, a_x) REFERENCES a (y, x) MATCH FULL NOT VALID 2371 2372 statement ok 2373 INSERT INTO a (x, y) VALUES ('x1', 'y1') 2374 2375 # These statements should all fail because this uses MATCH FULL. 2376 statement error missing value for column "a_y" in multi-part foreign key 2377 INSERT INTO b (a_x) VALUES ('x1') 2378 2379 statement error missing value for column "a_x" in multi-part foreign key 2380 INSERT INTO b (a_y) VALUES ('y1') 2381 2382 statement error insert on table "b" violates foreign key constraint "fk_ref"\nDETAIL: MATCH FULL does not allow mixing of null and nonnull key values 2383 INSERT INTO b (a_y, a_x) VALUES ('y1', NULL) 2384 2385 statement error insert on table "b" violates foreign key constraint "fk_ref"\nDETAIL: MATCH FULL does not allow mixing of null and nonnull key values 2386 INSERT INTO b (a_y, a_x) VALUES (NULL, 'x1') 2387 2388 # These next two statements should still be allowed. 2389 statement ok 2390 INSERT INTO b (a_x, a_y) VALUES ('x1', 'y1') 2391 2392 statement ok 2393 INSERT INTO b (a_x, a_y) VALUES (NULL, NULL) 2394 2395 statement ok 2396 ALTER TABLE b VALIDATE CONSTRAINT fk_ref 2397 2398 statement ok 2399 DROP TABLE b, a 2400 2401 # Test composite key with three columns. 2402 statement ok 2403 CREATE TABLE a ( 2404 x STRING NULL 2405 ,y STRING NULL 2406 ,z STRING NULL 2407 ,CONSTRAINT "primary" PRIMARY KEY (z, y, x) 2408 ); 2409 2410 statement ok 2411 CREATE TABLE b ( 2412 a_y STRING NULL 2413 ,a_x STRING NULL 2414 ,a_z STRING NULL 2415 ); 2416 2417 # Add the constraint separately so that it's unvalidated, so we can test VALIDATE CONSTRAINT. 2418 statement ok 2419 ALTER TABLE b ADD CONSTRAINT fk_ref FOREIGN KEY (a_z, a_y, a_x) REFERENCES a (z, y, x) MATCH FULL NOT VALID 2420 2421 statement ok 2422 INSERT INTO a (x, y, z) VALUES ('x1', 'y1', 'z1') 2423 2424 # These statements should all fail because this uses MATCH FULL. 2425 statement error missing values for columns \["a_y" "a_z"\] in multi-part foreign key 2426 INSERT INTO b (a_x) VALUES ('x1') 2427 2428 statement error missing values for columns \["a_x" "a_z"\] in multi-part foreign key 2429 INSERT INTO b (a_y) VALUES ('y1') 2430 2431 statement error missing values for columns \["a_x" "a_y"\] in multi-part foreign key 2432 INSERT INTO b (a_z) VALUES ('z1') 2433 2434 statement error missing value for column "a_z" in multi-part foreign key 2435 INSERT INTO b (a_x, a_y) VALUES ('x1', 'y1') 2436 2437 statement error missing value for column "a_z" in multi-part foreign key 2438 INSERT INTO b (a_x, a_y) VALUES (NULL, 'y1') 2439 2440 statement error missing value for column "a_z" in multi-part foreign key 2441 INSERT INTO b (a_x, a_y) VALUES ('x1', NULL) 2442 2443 statement error missing value for column "a_y" in multi-part foreign key 2444 INSERT INTO b (a_x, a_z) VALUES ('x1', 'z1') 2445 2446 statement error missing value for column "a_y" in multi-part foreign key 2447 INSERT INTO b (a_x, a_z) VALUES (NULL, 'z1') 2448 2449 statement error missing value for column "a_y" in multi-part foreign key 2450 INSERT INTO b (a_x, a_z) VALUES ('x1', NULL) 2451 2452 statement error missing value for column "a_x" in multi-part foreign key 2453 INSERT INTO b (a_y, a_z) VALUES ('y1', 'z1') 2454 2455 statement error missing value for column "a_x" in multi-part foreign key 2456 INSERT INTO b (a_y, a_z) VALUES (NULL, 'z1') 2457 2458 statement error missing value for column "a_x" in multi-part foreign key 2459 INSERT INTO b (a_y, a_z) VALUES ('y1', NULL) 2460 2461 statement error insert on table "b" violates foreign key constraint "fk_ref"\nDETAIL: MATCH FULL does not allow mixing of null and nonnull key values 2462 INSERT INTO b (a_x, a_y, a_z) VALUES ('x1', NULL, NULL) 2463 2464 statement error insert on table "b" violates foreign key constraint "fk_ref"\nDETAIL: MATCH FULL does not allow mixing of null and nonnull key values 2465 INSERT INTO b (a_x, a_y, a_z) VALUES (NULL, 'y1', NULL) 2466 2467 statement error insert on table "b" violates foreign key constraint "fk_ref"\nDETAIL: MATCH FULL does not allow mixing of null and nonnull key values 2468 INSERT INTO b (a_x, a_y, a_z) VALUES (NULL, NULL, 'z1') 2469 2470 statement error insert on table "b" violates foreign key constraint "fk_ref"\nDETAIL: MATCH FULL does not allow mixing of null and nonnull key values 2471 INSERT INTO b (a_x, a_y, a_z) VALUES ('x1', 'y1', NULL) 2472 2473 statement error insert on table "b" violates foreign key constraint "fk_ref"\nDETAIL: MATCH FULL does not allow mixing of null and nonnull key values 2474 INSERT INTO b (a_x, a_y, a_z) VALUES ('x1', NULL, 'z1') 2475 2476 statement error insert on table "b" violates foreign key constraint "fk_ref"\nDETAIL: MATCH FULL does not allow mixing of null and nonnull key values 2477 INSERT INTO b (a_x, a_y, a_z) VALUES (NULL, 'y1', 'z1') 2478 2479 # This statement should still be allowed. 2480 statement ok 2481 INSERT INTO b (a_x, a_y, a_z) VALUES (NULL, NULL, NULL) 2482 2483 statement ok 2484 ALTER TABLE b VALIDATE CONSTRAINT fk_ref 2485 2486 statement ok 2487 DROP TABLE b, a 2488 2489 subtest Composite_Full_Validate_Constraint_Invalid 2490 # Test VALIDATE CONSTRAINT by inserting invalid rows before the constraint is added, one at a time. 2491 2492 statement ok 2493 CREATE TABLE a ( 2494 x STRING NULL 2495 ,y STRING NULL 2496 ,z STRING NULL 2497 ,CONSTRAINT "primary" PRIMARY KEY (z, y, x) 2498 ); 2499 2500 statement ok 2501 CREATE TABLE b ( 2502 a_y STRING NULL 2503 ,a_x STRING NULL 2504 ,a_z STRING NULL 2505 ,INDEX idx (a_z, a_y, a_x) 2506 ); 2507 2508 statement ok 2509 INSERT INTO b (a_x, a_y, a_z) VALUES ('x1', NULL, NULL) 2510 2511 statement ok 2512 ALTER TABLE b ADD CONSTRAINT fk_ref FOREIGN KEY (a_z, a_y, a_x) REFERENCES a (z, y, x) MATCH FULL NOT VALID 2513 2514 statement error foreign key violation: MATCH FULL does not allow mixing of null and nonnull values 2515 ALTER TABLE b VALIDATE CONSTRAINT fk_ref 2516 2517 statement ok 2518 TRUNCATE b 2519 2520 statement ok 2521 ALTER TABLE b DROP CONSTRAINT fk_ref 2522 2523 statement ok 2524 INSERT INTO b (a_x, a_y, a_z) VALUES (NULL, 'y1', NULL) 2525 2526 statement ok 2527 ALTER TABLE b ADD CONSTRAINT fk_ref FOREIGN KEY (a_z, a_y, a_x) REFERENCES a (z, y, x) MATCH FULL NOT VALID 2528 2529 statement error foreign key violation: MATCH FULL does not allow mixing of null and nonnull values 2530 ALTER TABLE b VALIDATE CONSTRAINT fk_ref 2531 2532 statement ok 2533 TRUNCATE b 2534 2535 statement ok 2536 ALTER TABLE b DROP CONSTRAINT fk_ref 2537 2538 statement ok 2539 INSERT INTO b (a_x, a_y, a_z) VALUES (NULL, NULL, 'z1') 2540 2541 statement ok 2542 ALTER TABLE b ADD CONSTRAINT fk_ref FOREIGN KEY (a_z, a_y, a_x) REFERENCES a (z, y, x) MATCH FULL NOT VALID 2543 2544 statement error foreign key violation: MATCH FULL does not allow mixing of null and nonnull values 2545 ALTER TABLE b VALIDATE CONSTRAINT fk_ref 2546 2547 statement ok 2548 TRUNCATE b 2549 2550 statement ok 2551 ALTER TABLE b DROP CONSTRAINT fk_ref 2552 2553 statement ok 2554 INSERT INTO b (a_x, a_y, a_z) VALUES ('x1', 'y1', NULL) 2555 2556 statement ok 2557 ALTER TABLE b ADD CONSTRAINT fk_ref FOREIGN KEY (a_z, a_y, a_x) REFERENCES a (z, y, x) MATCH FULL NOT VALID 2558 2559 statement error foreign key violation: MATCH FULL does not allow mixing of null and nonnull values 2560 ALTER TABLE b VALIDATE CONSTRAINT fk_ref 2561 2562 statement ok 2563 TRUNCATE b 2564 2565 statement ok 2566 ALTER TABLE b DROP CONSTRAINT fk_ref 2567 2568 statement ok 2569 INSERT INTO b (a_x, a_y, a_z) VALUES ('x1', NULL, 'z1') 2570 2571 statement ok 2572 ALTER TABLE b ADD CONSTRAINT fk_ref FOREIGN KEY (a_z, a_y, a_x) REFERENCES a (z, y, x) MATCH FULL NOT VALID 2573 2574 statement error foreign key violation: MATCH FULL does not allow mixing of null and nonnull values 2575 ALTER TABLE b VALIDATE CONSTRAINT fk_ref 2576 2577 statement ok 2578 TRUNCATE b 2579 2580 statement ok 2581 ALTER TABLE b DROP CONSTRAINT fk_ref 2582 2583 statement ok 2584 INSERT INTO b (a_x, a_y, a_z) VALUES (NULL, 'y1', 'z1') 2585 2586 statement ok 2587 ALTER TABLE b ADD CONSTRAINT fk_ref FOREIGN KEY (a_z, a_y, a_x) REFERENCES a (z, y, x) MATCH FULL NOT VALID 2588 2589 statement error foreign key violation: MATCH FULL does not allow mixing of null and nonnull values 2590 ALTER TABLE b VALIDATE CONSTRAINT fk_ref 2591 2592 statement ok 2593 TRUNCATE b 2594 2595 statement ok 2596 ALTER TABLE b DROP CONSTRAINT fk_ref 2597 2598 statement ok 2599 INSERT INTO b (a_x, a_y, a_z) VALUES ('x2', 'y1', 'z1') 2600 2601 statement ok 2602 ALTER TABLE b ADD CONSTRAINT fk_ref FOREIGN KEY (a_z, a_y, a_x) REFERENCES a (z, y, x) MATCH FULL NOT VALID 2603 2604 statement error pq: foreign key violation: "b" row a_z='z1', a_y='y1', a_x='x2', rowid=[0-9]* has no match in "a" 2605 ALTER TABLE b VALIDATE CONSTRAINT fk_ref 2606 2607 statement ok 2608 TRUNCATE b 2609 2610 statement ok 2611 ALTER TABLE b DROP CONSTRAINT fk_ref 2612 2613 statement ok 2614 INSERT INTO b (a_x, a_y, a_z) VALUES ('x2', 'y2', 'z1') 2615 2616 statement ok 2617 ALTER TABLE b ADD CONSTRAINT fk_ref FOREIGN KEY (a_z, a_y, a_x) REFERENCES a (z, y, x) MATCH FULL NOT VALID 2618 2619 statement error pq: foreign key violation: "b" row a_z='z1', a_y='y2', a_x='x2', rowid=[0-9]* has no match in "a" 2620 ALTER TABLE b VALIDATE CONSTRAINT fk_ref 2621 2622 statement ok 2623 TRUNCATE b 2624 2625 statement ok 2626 ALTER TABLE b DROP CONSTRAINT fk_ref 2627 2628 statement ok 2629 INSERT INTO b (a_x, a_y, a_z) VALUES ('x2', 'y2', 'z2') 2630 2631 statement ok 2632 ALTER TABLE b ADD CONSTRAINT fk_ref FOREIGN KEY (a_z, a_y, a_x) REFERENCES a (z, y, x) MATCH FULL NOT VALID 2633 2634 statement error pq: foreign key violation: "b" row a_z='z2', a_y='y2', a_x='x2', rowid=[0-9]* has no match in "a" 2635 ALTER TABLE b VALIDATE CONSTRAINT fk_ref 2636 2637 statement ok 2638 DROP TABLE b, a 2639 2640 subtest auto_add_fk_with_composite_index_to_empty_table 2641 2642 statement ok 2643 CREATE TABLE parent_composite_index (a_id INT NOT NULL, b_id INT NOT NULL, PRIMARY KEY (a_id, b_id)) 2644 2645 statement ok 2646 CREATE TABLE child_composite_index (id SERIAL NOT NULL, parent_a_id INT, parent_b_id INT, PRIMARY KEY (id)) 2647 2648 # The (composite) index needed for the fk constraint is automatically added because the table is empty 2649 statement ok 2650 ALTER TABLE child_composite_index ADD CONSTRAINT fk_id FOREIGN KEY (parent_a_id, parent_b_id) REFERENCES parent_composite_index; 2651 2652 statement ok 2653 INSERT INTO parent_composite_index VALUES (100, 200) 2654 2655 statement ok 2656 INSERT INTO child_composite_index VALUES (1, 100, 200) 2657 2658 statement error insert on table "child_composite_index" violates foreign key constraint "fk_id"\nDETAIL: Key \(parent_a_id, parent_b_id\)=\(100, 300\) is not present in table "parent_composite_index" 2659 INSERT INTO child_composite_index VALUES (2, 100, 300) 2660 2661 statement ok 2662 DROP TABLE child_composite_index, parent_composite_index 2663 2664 subtest auto_add_fk_to_nonempty_table_error 2665 2666 statement ok 2667 CREATE TABLE nonempty_a (id SERIAL NOT NULL, self_id INT, b_id INT NOT NULL, PRIMARY KEY (id)) 2668 2669 statement ok 2670 CREATE TABLE nonempty_b (id SERIAL NOT NULL, PRIMARY KEY (id)) 2671 2672 statement ok 2673 INSERT INTO nonempty_b VALUES (1), (2), (3); 2674 2675 statement ok 2676 INSERT INTO nonempty_a VALUES (1, NULL, 1) 2677 2678 # Fails because self_id is not indexed, and an index will not be automatically created because the table is nonempty 2679 statement error foreign key requires an existing index on columns \("self_id"\) 2680 ALTER TABLE nonempty_a ADD CONSTRAINT fk_self_id FOREIGN KEY (self_id) REFERENCES nonempty_a; 2681 2682 statement ok 2683 CREATE INDEX ON nonempty_a (self_id) 2684 2685 # This now succeeds with the manually added index 2686 statement ok 2687 ALTER TABLE nonempty_a ADD CONSTRAINT fk_self_id FOREIGN KEY (self_id) REFERENCES nonempty_a; 2688 2689 # Fails because b_id is not indexed, and an index will not be automatically created because the table is nonempty 2690 statement error foreign key requires an existing index on columns \("b_id"\) 2691 ALTER TABLE nonempty_a ADD CONSTRAINT fk_b FOREIGN KEY (b_id) REFERENCES nonempty_b; 2692 2693 statement ok 2694 CREATE INDEX ON nonempty_a (b_id) 2695 2696 # This now succeeds with the manually added index 2697 statement ok 2698 ALTER TABLE nonempty_a ADD CONSTRAINT fk_b FOREIGN KEY (b_id) REFERENCES nonempty_b; 2699 2700 statement ok 2701 DROP TABLE nonempty_a, nonempty_b 2702 2703 subtest auto_add_fk_index_name_collision 2704 2705 statement ok 2706 CREATE TABLE parent_name_collision (id SERIAL NOT NULL, PRIMARY KEY (id)) 2707 2708 statement ok 2709 CREATE TABLE child_name_collision (id SERIAL NOT NULL, parent_id INT, other_col INT) 2710 2711 statement ok 2712 CREATE INDEX child_name_collision_auto_index_fk_id ON child_name_collision (other_col) 2713 2714 # Testing the unusual case where an index already exists that has the same name 2715 # as the index to be auto-generated when adding a fk constraint to an empty 2716 # table (but the existing index is not on the referencing column), in which 2717 # case the ALTER TABLE will create another name for the index. 2718 statement ok 2719 ALTER TABLE child_name_collision ADD CONSTRAINT fk_id FOREIGN KEY (parent_id) references parent_name_collision 2720 2721 subtest auto_add_fk_duplicate_cols_error 2722 2723 statement ok 2724 CREATE TABLE parent (a_id INT, b_id INT, PRIMARY KEY (a_id, b_id)) 2725 2726 statement ok 2727 CREATE TABLE child_duplicate_cols (id INT, parent_id INT, PRIMARY KEY (id)) 2728 2729 # The fk constraint is invalid because it has duplicate columns, so automatically adding the index fails 2730 statement error index \"child_duplicate_cols_auto_index_fk\" contains duplicate column \"parent_id\" 2731 ALTER TABLE child_duplicate_cols ADD CONSTRAINT fk FOREIGN KEY (parent_id, parent_id) references parent 2732 2733 statement ok 2734 DROP TABLE parent, child_duplicate_cols 2735 2736 # Check that a FK cannot be added to a column being backfilled. 2737 # If this behavior is changed you should create a test similar to 2738 # TestCRUDWhileColumnBackfill to test that CRUD operations operating 2739 # with FK relationships work correctly over NON NULL columns that 2740 # are still being backfilled. 2741 subtest cannot_add_fk_on_col_needing_backfill 2742 2743 statement ok 2744 CREATE TABLE parentid ( 2745 k INT NOT NULL PRIMARY KEY, 2746 v INT NOT NULL 2747 ); 2748 2749 statement ok 2750 CREATE TABLE childid ( 2751 id INT NOT NULL PRIMARY KEY 2752 ); 2753 2754 # Make tables non-empty. 2755 statement ok 2756 INSERT INTO parentid (k, v) VALUES (0, 1); INSERT INTO childid (id) VALUES (2); 2757 2758 statement error column \"id\" does not exist 2759 BEGIN; ALTER TABLE parentid ADD id INT NOT NULL AS (k + 2) STORED; ALTER TABLE childid ADD CONSTRAINT fk_id FOREIGN KEY (id) REFERENCES parentid (id); 2760 2761 statement ok 2762 ROLLBACK; 2763 2764 subtest dont_check_nulls 2765 # Make sure that nulls are never checked while executing FK constraints. 2766 2767 statement ok 2768 CREATE TABLE t1(x INT UNIQUE) 2769 2770 statement ok 2771 INSERT INTO t1(x) VALUES (1), (null) 2772 2773 statement ok 2774 CREATE TABLE t2( 2775 x INT REFERENCES t1(x) 2776 ) 2777 2778 statement ok 2779 INSERT INTO t2(x) VALUES (1), (null) 2780 2781 statement ok 2782 DELETE FROM t1 WHERE x IS NULL 2783 2784 statement ok 2785 DROP TABLE t1, t2 CASCADE 2786 2787 subtest test_not_valid_fk 2788 2789 statement ok 2790 CREATE TABLE person (id INT PRIMARY KEY, age INT, name STRING) 2791 2792 statement ok 2793 CREATE TABLE pet (id INT PRIMARY KEY, name STRING) 2794 2795 statement ok 2796 INSERT INTO pet VALUES (0, 'crookshanks') 2797 2798 statement error pq: foreign key violation: "pet" row id=0 has no match in "person" 2799 ALTER TABLE pet ADD CONSTRAINT fk_constraint FOREIGN KEY (id) REFERENCES person (id) 2800 2801 statement ok 2802 ALTER TABLE pet ADD CONSTRAINT fk_constraint FOREIGN KEY (id) REFERENCES person (id) NOT VALID 2803 2804 query TTTTB 2805 SHOW CONSTRAINTS FROM pet 2806 ---- 2807 pet fk_constraint FOREIGN KEY FOREIGN KEY (id) REFERENCES person(id) false 2808 pet primary PRIMARY KEY PRIMARY KEY (id ASC) true 2809 2810 statement error pq: foreign key violation: "pet" row id=0 has no match in "person" 2811 ALTER TABLE pet VALIDATE CONSTRAINT fk_constraint 2812 2813 statement ok 2814 INSERT INTO person VALUES (0, 18, 'Hermione Granger') 2815 2816 statement ok 2817 ALTER TABLE pet VALIDATE CONSTRAINT fk_constraint 2818 2819 query TTTTB 2820 SHOW CONSTRAINTS FROM pet 2821 ---- 2822 pet fk_constraint FOREIGN KEY FOREIGN KEY (id) REFERENCES person(id) true 2823 pet primary PRIMARY KEY PRIMARY KEY (id ASC) true 2824 2825 statement ok 2826 DROP TABLE person, pet 2827 2828 statement ok 2829 DROP TABLE child 2830 2831 # Update 2832 # ------ 2833 2834 statement ok 2835 CREATE TABLE parent (x INT, p INT PRIMARY KEY, u INT UNIQUE) 2836 2837 statement ok 2838 CREATE TABLE child (c INT PRIMARY KEY, p INT NOT NULL REFERENCES parent(p)) 2839 2840 statement ok 2841 INSERT INTO parent (p, u) VALUES (1, 10), (2, 20) 2842 2843 statement ok 2844 INSERT INTO child VALUES (1, 1) 2845 2846 # Updating the referenced table 2847 statement error update on table "parent" violates foreign key constraint "fk_p_ref_parent" on table "child"\nDETAIL: Key \(p\)=\(1\) is still referenced from table "child"\. 2848 UPDATE parent SET p = 3 WHERE p = 1 2849 2850 statement ok 2851 UPDATE parent SET p = 3 WHERE p = 2 2852 2853 statement ok 2854 UPDATE parent SET p = 3 WHERE p = 10 2855 2856 # Updating the referencing table 2857 statement ok 2858 UPDATE child SET p = 3 WHERE p = 1 2859 2860 statement ok 2861 DELETE FROM child 2862 2863 statement ok 2864 DELETE FROM parent 2865 2866 statement ok 2867 INSERT INTO parent (p, u) VALUES (2, 10), (3, 20) 2868 2869 statement ok 2870 INSERT INTO child (c, p) VALUES (200, 2) 2871 2872 # These two test cases are sort of undefined behavior, since their 2873 # success/failure depends on the order in which the updates are performed. 2874 statement error duplicate key value \(p\)=\(3\) violates unique constraint "primary" 2875 UPDATE parent SET p = p + 1 2876 2877 statement ok 2878 UPDATE parent SET p = p - 1 2879 2880 # This case illustrates why it's insufficient to just check the "old" values, 2881 # even in the absence of temporary unique constraint violations. In this case, 2882 # the "new" and "old" values are the same, so we have to ensure that we don't 2883 # run deletion FK checks on old values which were replaced with new ones. 2884 statement ok 2885 UPDATE parent SET p = p 2886 2887 statement ok 2888 CREATE TABLE self (x INT PRIMARY KEY, y INT NOT NULL REFERENCES self(x)) 2889 2890 statement ok 2891 INSERT INTO self VALUES (1, 2), (2, 3), (3, 4), (4, 1) 2892 2893 statement error update on table "self" violates foreign key constraint "fk_y_ref_self" 2894 UPDATE self SET y = 5 WHERE y = 1 2895 2896 statement error update on table "self" violates foreign key constraint "fk_y_ref_self" on table "self"\nDETAIL: Key \(x\)=\(4\) is still referenced from table "self"\. 2897 UPDATE self SET x = 5 WHERE y = 1 2898 2899 statement ok 2900 TRUNCATE self 2901 2902 statement ok 2903 INSERT INTO self VALUES (1, 1) 2904 2905 statement ok 2906 UPDATE self SET x = 5, y = 5 2907 2908 statement ok 2909 CREATE TABLE two (a int, b int, primary key (a, b)) 2910 2911 statement ok 2912 INSERT INTO two VALUES (1, 1), (1, 3), (3, 3) 2913 2914 statement ok 2915 CREATE TABLE fam ( 2916 a INT, 2917 b INT, 2918 c INT, 2919 d INT, 2920 e INT, 2921 FAMILY (a, b, c), 2922 FAMILY (d, e), 2923 FOREIGN KEY (c, d) REFERENCES two (a, b) 2924 ) 2925 2926 statement ok 2927 INSERT INTO fam VALUES (10, 10, 1, 1, 10) 2928 2929 # Ensure that we fetch all relevant columns for a foreign key. Here we have 2930 # FKs that span column families. 2931 2932 # NOTE: when we no longer require indexes to be created for FKs, ensure that 2933 # these still scan all the relevant FK columns. 2934 statement ok 2935 UPDATE fam SET d = 3 2936 2937 statement ok 2938 UPDATE fam SET c = 3 2939 2940 statement ok 2941 CREATE TABLE match_full ( 2942 a INT, 2943 b INT, 2944 FOREIGN KEY (a, b) REFERENCES two (a, b) MATCH FULL 2945 ) 2946 2947 statement ok 2948 INSERT INTO match_full VALUES (1, 1) 2949 2950 statement error update on table "match_full" violates foreign key constraint "fk_a_ref_two" 2951 UPDATE match_full SET a = NULL 2952 2953 statement ok 2954 UPDATE match_full SET a = NULL, b = NULL 2955 2956 statement ok 2957 CREATE TABLE match_simple ( 2958 a INT, 2959 b INT, 2960 FOREIGN KEY (a, b) REFERENCES two (a, b) MATCH SIMPLE 2961 ) 2962 2963 statement ok 2964 INSERT INTO match_simple VALUES (1, 1) 2965 2966 statement ok 2967 UPDATE match_simple SET a = NULL 2968 2969 # Test that when the foreign key is a primary index we only look up the primary 2970 # family. 2971 subtest families 2972 2973 statement ok 2974 CREATE TABLE fam_parent ( 2975 k INT PRIMARY KEY, 2976 a INT, 2977 b INT NOT NULL, 2978 FAMILY (k, a), 2979 FAMILY (b) 2980 ) 2981 2982 statement ok 2983 CREATE TABLE fam_child ( 2984 k INT PRIMARY KEY, 2985 fk INT REFERENCES fam_parent(k) 2986 ) 2987 2988 statement ok 2989 INSERT INTO fam_parent VALUES (1, 1, 1) 2990 2991 statement ok 2992 GRANT ALL ON fam_parent TO testuser; 2993 GRANT ALL ON fam_child TO testuser; 2994 2995 # Open a transaction that modifies b. 2996 statement ok 2997 BEGIN 2998 2999 statement count 1 3000 UPDATE fam_parent SET b = b+1 WHERE k = 1 3001 3002 user testuser 3003 3004 # Run an INSERT which needs to check existence of the row. If we try to scan 3005 # the entire row, this blocks on the other transaction. We should only be 3006 # scanning the primary column family. A critical reason why this works is 3007 # because column b is NOT NULL, so the UPDATE statement does not read or 3008 # acquire FOR UPDATE locks on the primary column family because a lookup 3009 # on b's column family is enough to determine whether the row exists or not. 3010 statement ok 3011 INSERT INTO fam_child VALUES (1, 1) 3012 3013 user root 3014 3015 statement ok 3016 COMMIT 3017 3018 subtest foreign_key_multiple_output_columns 3019 3020 # Create a recursive table: messages refs good_users refs users. 3021 # Sometimes, messages refs users directly. 3022 3023 statement ok 3024 CREATE TABLE users ( 3025 id INTEGER PRIMARY KEY 3026 ) 3027 3028 statement ok 3029 CREATE TABLE good_users ( 3030 id INTEGER PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE, 3031 id2 INTEGER UNIQUE 3032 ) 3033 3034 statement ok 3035 CREATE SEQUENCE message_seq START 1 INCREMENT 1 3036 3037 statement ok 3038 CREATE TABLE messages ( 3039 message_id INT PRIMARY KEY DEFAULT nextval('message_seq'), 3040 user_id_1 integer REFERENCES good_users(id) ON DELETE CASCADE ON UPDATE CASCADE, 3041 user_id_2 integer REFERENCES good_users(id) ON DELETE CASCADE ON UPDATE CASCADE, -- this is recursive through good_users 3042 text string 3043 ) 3044 3045 # Add the same foreign key twice onto user. 3046 statement ok 3047 ALTER TABLE messages ADD FOREIGN KEY (user_id_1) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE 3048 3049 statement ok 3050 ALTER TABLE messages ADD FOREIGN KEY (user_id_1) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE 3051 3052 # Insert some rows 3053 statement ok 3054 INSERT INTO users(id) VALUES (1), (2), (3) 3055 3056 statement ok 3057 INSERT INTO good_users(id, id2) VALUES (1, 10), (2, 20), (3, 30) 3058 3059 statement ok 3060 INSERT INTO messages (user_id_1, user_id_2, text) VALUES 3061 (1, 2, 'hi jordan'), 3062 (2, 1, 'hi oliver'), 3063 (1, 2, 'you are a good user jordan'), 3064 (1, 3, 'you are a good user too rohan'), 3065 (3, 1, 'lucy is a good user') 3066 3067 query error 999.*good_users 3068 INSERT INTO messages (user_id_1, user_id_2, text) VALUES 3069 (999, 1, 'you are a bad user') 3070 3071 # Now try and update the user_id. 3072 statement ok 3073 update users set id = id * 10 3074 3075 # See that it propagates. 3076 query I 3077 SELECT * FROM users ORDER BY id ASC 3078 ---- 3079 10 3080 20 3081 30 3082 3083 query II 3084 SELECT * FROM good_users ORDER BY id ASC 3085 ---- 3086 10 10 3087 20 20 3088 30 30 3089 3090 query IIIT 3091 SELECT * FROM messages ORDER BY message_id ASC 3092 ---- 3093 1 10 20 hi jordan 3094 2 20 10 hi oliver 3095 3 10 20 you are a good user jordan 3096 4 10 30 you are a good user too rohan 3097 5 30 10 lucy is a good user 3098 3099 # Delete from users should work as well 3100 statement ok 3101 DELETE FROM users WHERE id = 30 3102 3103 # See that it propagates. 3104 query I 3105 SELECT * FROM users ORDER BY id ASC 3106 ---- 3107 10 3108 20 3109 3110 query II 3111 SELECT * FROM good_users ORDER BY id ASC 3112 ---- 3113 10 10 3114 20 20 3115 3116 query IIIT 3117 SELECT * FROM messages ORDER BY message_id ASC 3118 ---- 3119 1 10 20 hi jordan 3120 2 20 10 hi oliver 3121 3 10 20 you are a good user jordan 3122 3123 # Add a foreign key on id2, which is a different column. 3124 # This one is restrictive on updates and deletes. 3125 statement ok 3126 ALTER TABLE messages ADD FOREIGN KEY (user_id_1) REFERENCES good_users(id2) 3127 3128 statement ok 3129 ALTER TABLE good_users ADD FOREIGN KEY (id2) REFERENCES users(id) 3130 3131 # Updating should no longer work, since we have a restrict. 3132 statement error update on table "users" violates foreign key constraint "fk_id2_ref_users" on table "good_users"\nDETAIL: Key \(id\)=\(20\) is still referenced from table "good_users"\. 3133 UPDATE users SET id = id * 100 WHERE id = 20 3134 3135 # Insert some more stuff -- make sure it still behaves as expected. 3136 statement ok 3137 INSERT INTO users VALUES (40) 3138 3139 statement ok 3140 INSERT INTO good_users VALUES (40, 40) 3141 3142 statement ok 3143 INSERT INTO messages (user_id_1, user_id_2, text) VALUES 3144 (10, 40, 'oh hi mark'), 3145 (40, 10, 'youre tearing me apart lisa!') 3146 3147 query error 999.*good_users 3148 INSERT INTO messages (user_id_1, user_id_2, text) VALUES 3149 (999, 40, 'johnny is my best friend') 3150 3151 # And sanity check everything. 3152 query IIIT 3153 SELECT * FROM messages ORDER BY message_id ASC 3154 ---- 3155 1 10 20 hi jordan 3156 2 20 10 hi oliver 3157 3 10 20 you are a good user jordan 3158 7 10 40 oh hi mark 3159 8 40 10 youre tearing me apart lisa! 3160 3161 # Delete should still be okay since the cascade from id1 should "win". 3162 statement ok 3163 DELETE FROM users WHERE id = 20 3164 3165 query IIIT 3166 SELECT * FROM messages ORDER BY message_id ASC 3167 ---- 3168 7 10 40 oh hi mark 3169 8 40 10 youre tearing me apart lisa! 3170 3171 # Drop everything. 3172 statement ok 3173 DROP TABLE users CASCADE 3174 3175 statement ok 3176 DROP TABLE good_users CASCADE 3177 3178 statement ok 3179 DROP TABLE messages 3180 3181 # Test conflicting foreign keys ON DELETE and ON UPDATE - some known corner cases. 3182 # SET NULL/SET DEFAULT/CASCADE have priority and are evaluated in order, followed 3183 # by RESTRICT/NO ACTION. 3184 3185 # 3186 # ON DELETE 3187 # 3188 3189 statement ok 3190 CREATE TABLE t1 (a INT PRIMARY KEY); CREATE TABLE t2 (a INT DEFAULT 1) 3191 3192 # 'ON DELETE NO ACTION', followed by 'ON DELETE SET NULL' 3193 statement ok 3194 ALTER TABLE t2 ADD CONSTRAINT fk1 FOREIGN KEY (a) REFERENCES t1 ON DELETE NO ACTION; ALTER TABLE t2 ADD CONSTRAINT fk2 FOREIGN KEY (a) REFERENCES t1 ON DELETE SET NULL 3195 3196 statement ok 3197 insert into t1 values (123); insert into t2 values (123) 3198 3199 statement ok 3200 DELETE FROM t1 WHERE a = 123 3201 3202 query I 3203 SELECT * FROM t2 3204 ---- 3205 NULL 3206 3207 statement ok 3208 ALTER TABLE t2 DROP CONSTRAINT fk1; ALTER TABLE t2 DROP CONSTRAINT fk2; TRUNCATE TABLE t2; TRUNCATE TABLE t1 3209 3210 # 'ON DELETE NO ACTION', followed by 'ON DELETE CASCADE' 3211 statement ok 3212 ALTER TABLE t2 ADD CONSTRAINT fk1 FOREIGN KEY (a) REFERENCES t1 ON DELETE NO ACTION; ALTER TABLE t2 ADD CONSTRAINT fk2 FOREIGN KEY (a) REFERENCES t1 ON DELETE CASCADE 3213 3214 statement ok 3215 insert into t1 values (123); insert into t2 values (123) 3216 3217 statement ok 3218 DELETE FROM t1 WHERE a = 123 3219 3220 query I 3221 SELECT * FROM t2 3222 ---- 3223 3224 statement ok 3225 ALTER TABLE t2 DROP CONSTRAINT fk1; ALTER TABLE t2 DROP CONSTRAINT fk2; TRUNCATE TABLE t2; TRUNCATE TABLE t1 3226 3227 # 'ON DELETE RESTRICT', followed by 'ON DELETE SET NULL' 3228 statement ok 3229 ALTER TABLE t2 ADD CONSTRAINT fk1 FOREIGN KEY (a) REFERENCES t1 ON DELETE RESTRICT; ALTER TABLE t2 ADD CONSTRAINT fk2 FOREIGN KEY (a) REFERENCES t1 ON DELETE SET NULL 3230 3231 statement ok 3232 insert into t1 values (123); insert into t2 values (123) 3233 3234 statement ok 3235 DELETE FROM t1 WHERE a = 123 3236 3237 query I 3238 SELECT * FROM t2 3239 ---- 3240 NULL 3241 3242 statement ok 3243 ALTER TABLE t2 DROP CONSTRAINT fk1; ALTER TABLE t2 DROP CONSTRAINT fk2; TRUNCATE TABLE t2; TRUNCATE TABLE t1 3244 3245 # 'ON DELETE RESTRICT', followed by 'ON DELETE CASCADE' 3246 statement ok 3247 ALTER TABLE t2 ADD CONSTRAINT fk1 FOREIGN KEY (a) REFERENCES t1 ON DELETE RESTRICT; ALTER TABLE t2 ADD CONSTRAINT fk2 FOREIGN KEY (a) REFERENCES t1 ON DELETE CASCADE 3248 3249 statement ok 3250 insert into t1 values (123); insert into t2 values (123) 3251 3252 statement ok 3253 DELETE FROM t1 WHERE a = 123 3254 3255 query I 3256 SELECT * FROM t2 3257 ---- 3258 3259 statement ok 3260 ALTER TABLE t2 DROP CONSTRAINT fk1; ALTER TABLE t2 DROP CONSTRAINT fk2; TRUNCATE TABLE t2; TRUNCATE TABLE t1 3261 3262 # 'ON DELETE CASCADE', followed by 'ON DELETE SET DEFAULT' 3263 statement ok 3264 ALTER TABLE t2 ADD CONSTRAINT fk1 FOREIGN KEY (a) REFERENCES t1 ON DELETE CASCADE; ALTER TABLE t2 ADD CONSTRAINT fk2 FOREIGN KEY (a) REFERENCES t1 ON DELETE SET DEFAULT 3265 3266 statement ok 3267 insert into t1 values (123); insert into t2 values (123) 3268 3269 statement ok 3270 DELETE FROM t1 WHERE a = 123 3271 3272 query I 3273 SELECT * FROM t2 3274 ---- 3275 3276 statement ok 3277 ALTER TABLE t2 DROP CONSTRAINT fk1; ALTER TABLE t2 DROP CONSTRAINT fk2; TRUNCATE TABLE t2; TRUNCATE TABLE t1 3278 3279 # 'ON DELETE CASCADE', followed by 'ON DELETE SET NULL' 3280 statement ok 3281 ALTER TABLE t2 ADD CONSTRAINT fk1 FOREIGN KEY (a) REFERENCES t1 ON DELETE CASCADE; ALTER TABLE t2 ADD CONSTRAINT fk2 FOREIGN KEY (a) REFERENCES t1 ON DELETE SET NULL 3282 3283 statement ok 3284 insert into t1 values (123); insert into t2 values (123) 3285 3286 statement ok 3287 DELETE FROM t1 WHERE a = 123 3288 3289 query I 3290 SELECT * FROM t2 3291 ---- 3292 3293 statement ok 3294 ALTER TABLE t2 DROP CONSTRAINT fk1; ALTER TABLE t2 DROP CONSTRAINT fk2; TRUNCATE TABLE t2; TRUNCATE TABLE t1 3295 3296 # 'ON DELETE SET DEFAULT', followed by 'ON DELETE CASCADE' 3297 statement ok 3298 ALTER TABLE t2 ADD CONSTRAINT fk1 FOREIGN KEY (a) REFERENCES t1 ON DELETE SET DEFAULT; 3299 ALTER TABLE t2 ADD CONSTRAINT fk2 FOREIGN KEY (a) REFERENCES t1 ON DELETE CASCADE 3300 3301 statement ok 3302 INSERT INTO t1 VALUES (123); 3303 INSERT INTO t2 VALUES (123) 3304 3305 statement error pq: update on table "t2" violates foreign key constraint "fk1"\nDETAIL: Key \(a\)=\(1\) is not present in table "t1"\. 3306 DELETE FROM t1 WHERE a = 123 3307 3308 query I 3309 SELECT * FROM t2 3310 ---- 3311 123 3312 3313 statement ok 3314 ALTER TABLE t2 DROP CONSTRAINT fk1; ALTER TABLE t2 DROP CONSTRAINT fk2; TRUNCATE TABLE t2; TRUNCATE TABLE t1 3315 3316 # 'ON DELETE SET DEFAULT', followed by 'ON DELETE SET NULL' 3317 statement ok 3318 ALTER TABLE t2 ADD CONSTRAINT fk1 FOREIGN KEY (a) REFERENCES t1 ON DELETE SET DEFAULT; 3319 ALTER TABLE t2 ADD CONSTRAINT fk2 FOREIGN KEY (a) REFERENCES t1 ON DELETE SET NULL 3320 3321 statement ok 3322 INSERT INTO t1 VALUES (123); 3323 INSERT INTO t2 VALUES (123) 3324 3325 statement error pq: update on table "t2" violates foreign key constraint "fk1"\nDETAIL: Key \(a\)=\(1\) is not present in table "t1"\. 3326 DELETE FROM t1 WHERE a = 123 3327 3328 query I 3329 SELECT * FROM t2 3330 ---- 3331 123 3332 3333 statement ok 3334 ALTER TABLE t2 DROP CONSTRAINT fk1; ALTER TABLE t2 DROP CONSTRAINT fk2; TRUNCATE TABLE t2; TRUNCATE TABLE t1 3335 3336 # 'ON DELETE SET NULL', followed by 'ON DELETE SET DEFAULT' 3337 statement ok 3338 ALTER TABLE t2 ADD CONSTRAINT fk1 FOREIGN KEY (a) REFERENCES t1 ON DELETE SET NULL; ALTER TABLE t2 ADD CONSTRAINT fk2 FOREIGN KEY (a) REFERENCES t1 ON DELETE SET DEFAULT 3339 3340 statement ok 3341 insert into t1 values (123); insert into t2 values (123) 3342 3343 statement ok 3344 DELETE FROM t1 WHERE a = 123 3345 3346 query I 3347 SELECT * FROM t2 3348 ---- 3349 NULL 3350 3351 statement ok 3352 ALTER TABLE t2 DROP CONSTRAINT fk1; ALTER TABLE t2 DROP CONSTRAINT fk2; TRUNCATE TABLE t2; TRUNCATE TABLE t1 3353 3354 statement ok 3355 DROP TABLE t2 CASCADE; DROP TABLE t1 CASCADE 3356 3357 # 3358 # ON UPDATE 3359 # 3360 3361 statement ok 3362 CREATE TABLE t1 (a INT PRIMARY KEY); CREATE TABLE t2 (a INT DEFAULT 1) 3363 3364 # 'ON UPDATE NO ACTION', followed by 'ON UPDATE SET NULL' 3365 statement ok 3366 ALTER TABLE t2 ADD CONSTRAINT fk1 FOREIGN KEY (a) REFERENCES t1 ON UPDATE NO ACTION; ALTER TABLE t2 ADD CONSTRAINT fk2 FOREIGN KEY (a) REFERENCES t1 ON UPDATE SET NULL 3367 3368 statement ok 3369 insert into t1 values (123); insert into t2 values (123) 3370 3371 statement ok 3372 UPDATE t1 SET a = 2 WHERE a = 123 3373 3374 query I 3375 SELECT * FROM t2 3376 ---- 3377 NULL 3378 3379 statement ok 3380 ALTER TABLE t2 DROP CONSTRAINT fk1; ALTER TABLE t2 DROP CONSTRAINT fk2; TRUNCATE TABLE t2; TRUNCATE TABLE t1 3381 3382 # 'ON UPDATE NO ACTION', followed by 'ON UPDATE CASCADE' 3383 statement ok 3384 ALTER TABLE t2 ADD CONSTRAINT fk1 FOREIGN KEY (a) REFERENCES t1 ON UPDATE NO ACTION; ALTER TABLE t2 ADD CONSTRAINT fk2 FOREIGN KEY (a) REFERENCES t1 ON UPDATE CASCADE 3385 3386 statement ok 3387 insert into t1 values (123); insert into t2 values (123) 3388 3389 statement ok 3390 UPDATE t1 SET a = 2 WHERE a = 123 3391 3392 query I 3393 SELECT * FROM t2 3394 ---- 3395 2 3396 3397 statement ok 3398 ALTER TABLE t2 DROP CONSTRAINT fk1; ALTER TABLE t2 DROP CONSTRAINT fk2; TRUNCATE TABLE t2; TRUNCATE TABLE t1 3399 3400 # 'ON UPDATE RESTRICT', followed by 'ON UPDATE SET NULL' 3401 statement ok 3402 ALTER TABLE t2 ADD CONSTRAINT fk1 FOREIGN KEY (a) REFERENCES t1 ON UPDATE RESTRICT; ALTER TABLE t2 ADD CONSTRAINT fk2 FOREIGN KEY (a) REFERENCES t1 ON UPDATE SET NULL 3403 3404 statement ok 3405 insert into t1 values (123); insert into t2 values (123) 3406 3407 statement ok 3408 UPDATE t1 SET a = 2 WHERE a = 123 3409 3410 query I 3411 SELECT * FROM t2 3412 ---- 3413 NULL 3414 3415 statement ok 3416 ALTER TABLE t2 DROP CONSTRAINT fk1; ALTER TABLE t2 DROP CONSTRAINT fk2; TRUNCATE TABLE t2; TRUNCATE TABLE t1 3417 3418 # 'ON UPDATE RESTRICT', followed by 'ON UPDATE CASCADE' 3419 statement ok 3420 ALTER TABLE t2 ADD CONSTRAINT fk1 FOREIGN KEY (a) REFERENCES t1 ON UPDATE RESTRICT; ALTER TABLE t2 ADD CONSTRAINT fk2 FOREIGN KEY (a) REFERENCES t1 ON UPDATE CASCADE 3421 3422 statement ok 3423 insert into t1 values (123); insert into t2 values (123) 3424 3425 statement ok 3426 UPDATE t1 SET a = 2 WHERE a = 123 3427 3428 query I 3429 SELECT * FROM t2 3430 ---- 3431 2 3432 3433 statement ok 3434 ALTER TABLE t2 DROP CONSTRAINT fk1; ALTER TABLE t2 DROP CONSTRAINT fk2; TRUNCATE TABLE t2; TRUNCATE TABLE t1 3435 3436 # 'ON UPDATE CASCADE', followed by 'ON UPDATE SET DEFAULT' 3437 statement ok 3438 ALTER TABLE t2 ADD CONSTRAINT fk1 FOREIGN KEY (a) REFERENCES t1 ON UPDATE CASCADE; ALTER TABLE t2 ADD CONSTRAINT fk2 FOREIGN KEY (a) REFERENCES t1 ON UPDATE SET DEFAULT 3439 3440 statement ok 3441 insert into t1 values (123); insert into t2 values (123) 3442 3443 statement ok 3444 UPDATE t1 SET a = 2 WHERE a = 123 3445 3446 query I 3447 SELECT * FROM t2 3448 ---- 3449 2 3450 3451 statement ok 3452 ALTER TABLE t2 DROP CONSTRAINT fk1; ALTER TABLE t2 DROP CONSTRAINT fk2; TRUNCATE TABLE t2; TRUNCATE TABLE t1 3453 3454 # 'ON UPDATE CASCADE', followed by 'ON UPDATE SET NULL' 3455 statement ok 3456 ALTER TABLE t2 ADD CONSTRAINT fk1 FOREIGN KEY (a) REFERENCES t1 ON UPDATE CASCADE; ALTER TABLE t2 ADD CONSTRAINT fk2 FOREIGN KEY (a) REFERENCES t1 ON UPDATE SET NULL 3457 3458 statement ok 3459 insert into t1 values (123); insert into t2 values (123) 3460 3461 statement ok 3462 UPDATE t1 SET a = 2 WHERE a = 123 3463 3464 query I 3465 SELECT * FROM t2 3466 ---- 3467 2 3468 3469 statement ok 3470 ALTER TABLE t2 DROP CONSTRAINT fk1; ALTER TABLE t2 DROP CONSTRAINT fk2; TRUNCATE TABLE t2; TRUNCATE TABLE t1 3471 3472 # 'ON UPDATE SET DEFAULT', followed by 'ON UPDATE CASCADE' 3473 statement ok 3474 ALTER TABLE t2 ADD CONSTRAINT fk1 FOREIGN KEY (a) REFERENCES t1 ON UPDATE SET DEFAULT; ALTER TABLE t2 ADD CONSTRAINT fk2 FOREIGN KEY (a) REFERENCES t1 ON UPDATE CASCADE 3475 3476 statement ok 3477 insert into t1 values (123); insert into t2 values (123) 3478 3479 statement error pq: update on table "t2" violates foreign key constraint "fk1"\nDETAIL: Key \(a\)=\(1\) is not present in table "t1"\. 3480 UPDATE t1 SET a = 2 WHERE a = 123 3481 3482 query I 3483 SELECT * FROM t2 3484 ---- 3485 123 3486 3487 statement ok 3488 ALTER TABLE t2 DROP CONSTRAINT fk1; ALTER TABLE t2 DROP CONSTRAINT fk2; TRUNCATE TABLE t2; TRUNCATE TABLE t1 3489 3490 # 'ON UPDATE SET DEFAULT', followed by 'ON UPDATE SET NULL' 3491 statement ok 3492 ALTER TABLE t2 ADD CONSTRAINT fk1 FOREIGN KEY (a) REFERENCES t1 ON UPDATE SET DEFAULT; ALTER TABLE t2 ADD CONSTRAINT fk2 FOREIGN KEY (a) REFERENCES t1 ON UPDATE SET NULL 3493 3494 statement ok 3495 insert into t1 values (123); insert into t2 values (123) 3496 3497 statement error update on table "t2" violates foreign key constraint "fk1"\nDETAIL: Key \(a\)=\(1\) is not present in table "t1"\. 3498 UPDATE t1 SET a = 2 WHERE a = 123 3499 3500 query I 3501 SELECT * FROM t2 3502 ---- 3503 123 3504 3505 statement ok 3506 ALTER TABLE t2 DROP CONSTRAINT fk1; ALTER TABLE t2 DROP CONSTRAINT fk2; TRUNCATE TABLE t2; TRUNCATE TABLE t1 3507 3508 # 'ON UPDATE SET NULL', followed by 'ON UPDATE SET DEFAULT' 3509 statement ok 3510 ALTER TABLE t2 ADD CONSTRAINT fk1 FOREIGN KEY (a) REFERENCES t1 ON UPDATE SET NULL; ALTER TABLE t2 ADD CONSTRAINT fk2 FOREIGN KEY (a) REFERENCES t1 ON UPDATE SET DEFAULT 3511 3512 statement ok 3513 insert into t1 values (123); insert into t2 values (123) 3514 3515 statement ok 3516 UPDATE t1 SET a = 2 WHERE a = 123 3517 3518 query I 3519 SELECT * FROM t2 3520 ---- 3521 NULL 3522 3523 statement ok 3524 ALTER TABLE t2 DROP CONSTRAINT fk1; ALTER TABLE t2 DROP CONSTRAINT fk2; TRUNCATE TABLE t2; TRUNCATE TABLE t1 3525 3526 statement ok 3527 DROP TABLE t2 CASCADE; DROP TABLE t1 CASCADE 3528 3529 # Test FK check that is using a non-unique index (#43969). In this case the 3530 # index on k2 is preferred because it doesn't contain unnecessary column v. 3531 statement ok 3532 CREATE TABLE nonunique_idx_parent ( 3533 k1 INT, 3534 k2 INT, 3535 v INT, 3536 CONSTRAINT "primary" PRIMARY KEY (k1, k2), 3537 INDEX (k2) 3538 ) 3539 3540 statement ok 3541 CREATE TABLE nonunique_idx_child ( 3542 k INT PRIMARY KEY, 3543 ref1 INT, 3544 ref2 INT, 3545 CONSTRAINT "fk" FOREIGN KEY (ref1, ref2) REFERENCES nonunique_idx_parent (k1, k2) 3546 ) 3547 3548 statement ok 3549 INSERT INTO nonunique_idx_parent VALUES (1, 10) 3550 3551 statement ok 3552 INSERT INTO nonunique_idx_child VALUES (0, 1, 10) 3553 3554 # Stepping regression test. There was a bug where having cascades disables txn 3555 # stepping which caused issues when executing postqueries, so a query which 3556 # involved a mixed situation would error out. 3557 3558 statement ok 3559 CREATE TABLE x ( 3560 k INT PRIMARY KEY 3561 ) 3562 3563 statement ok 3564 CREATE TABLE y ( 3565 y INT PRIMARY KEY, 3566 b INT NULL REFERENCES x(k), 3567 c INT NULL REFERENCES x(k) ON DELETE CASCADE 3568 ) 3569 3570 statement ok 3571 WITH 3572 a AS (INSERT INTO y VALUES (1) RETURNING 1), b AS (DELETE FROM x WHERE true RETURNING 1) 3573 SELECT 3574 * 3575 FROM 3576 a