1-- 2-- FOREIGN KEY 3-- 4-- MATCH FULL 5-- 6-- First test, check and cascade 7-- 8CREATE TABLE PKTABLE ( ptest1 int PRIMARY KEY, ptest2 text ); 9CREATE TABLE FKTABLE ( ftest1 int REFERENCES PKTABLE MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE, ftest2 int ); 10-- Insert test data into PKTABLE 11INSERT INTO PKTABLE VALUES (1, 'Test1'); 12INSERT INTO PKTABLE VALUES (2, 'Test2'); 13INSERT INTO PKTABLE VALUES (3, 'Test3'); 14INSERT INTO PKTABLE VALUES (4, 'Test4'); 15INSERT INTO PKTABLE VALUES (5, 'Test5'); 16-- Insert successful rows into FK TABLE 17INSERT INTO FKTABLE VALUES (1, 2); 18INSERT INTO FKTABLE VALUES (2, 3); 19INSERT INTO FKTABLE VALUES (3, 4); 20INSERT INTO FKTABLE VALUES (NULL, 1); 21-- Insert a failed row into FK TABLE 22INSERT INTO FKTABLE VALUES (100, 2); 23ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_fkey" 24DETAIL: Key (ftest1)=(100) is not present in table "pktable". 25-- Check FKTABLE 26SELECT * FROM FKTABLE; 27 ftest1 | ftest2 28--------+-------- 29 1 | 2 30 2 | 3 31 3 | 4 32 | 1 33(4 rows) 34 35-- Delete a row from PK TABLE 36DELETE FROM PKTABLE WHERE ptest1=1; 37-- Check FKTABLE for removal of matched row 38SELECT * FROM FKTABLE; 39 ftest1 | ftest2 40--------+-------- 41 2 | 3 42 3 | 4 43 | 1 44(3 rows) 45 46-- Update a row from PK TABLE 47UPDATE PKTABLE SET ptest1=1 WHERE ptest1=2; 48-- Check FKTABLE for update of matched row 49SELECT * FROM FKTABLE; 50 ftest1 | ftest2 51--------+-------- 52 3 | 4 53 | 1 54 1 | 3 55(3 rows) 56 57DROP TABLE FKTABLE; 58DROP TABLE PKTABLE; 59-- 60-- check set NULL and table constraint on multiple columns 61-- 62CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 text, PRIMARY KEY(ptest1, ptest2) ); 63CREATE TABLE FKTABLE ( ftest1 int, ftest2 int, ftest3 int, CONSTRAINT constrname FOREIGN KEY(ftest1, ftest2) 64 REFERENCES PKTABLE MATCH FULL ON DELETE SET NULL ON UPDATE SET NULL); 65-- Test comments 66COMMENT ON CONSTRAINT constrname_wrong ON FKTABLE IS 'fk constraint comment'; 67ERROR: constraint "constrname_wrong" for table "fktable" does not exist 68COMMENT ON CONSTRAINT constrname ON FKTABLE IS 'fk constraint comment'; 69COMMENT ON CONSTRAINT constrname ON FKTABLE IS NULL; 70-- Insert test data into PKTABLE 71INSERT INTO PKTABLE VALUES (1, 2, 'Test1'); 72INSERT INTO PKTABLE VALUES (1, 3, 'Test1-2'); 73INSERT INTO PKTABLE VALUES (2, 4, 'Test2'); 74INSERT INTO PKTABLE VALUES (3, 6, 'Test3'); 75INSERT INTO PKTABLE VALUES (4, 8, 'Test4'); 76INSERT INTO PKTABLE VALUES (5, 10, 'Test5'); 77-- Insert successful rows into FK TABLE 78INSERT INTO FKTABLE VALUES (1, 2, 4); 79INSERT INTO FKTABLE VALUES (1, 3, 5); 80INSERT INTO FKTABLE VALUES (2, 4, 8); 81INSERT INTO FKTABLE VALUES (3, 6, 12); 82INSERT INTO FKTABLE VALUES (NULL, NULL, 0); 83-- Insert failed rows into FK TABLE 84INSERT INTO FKTABLE VALUES (100, 2, 4); 85ERROR: insert or update on table "fktable" violates foreign key constraint "constrname" 86DETAIL: Key (ftest1, ftest2)=(100, 2) is not present in table "pktable". 87INSERT INTO FKTABLE VALUES (2, 2, 4); 88ERROR: insert or update on table "fktable" violates foreign key constraint "constrname" 89DETAIL: Key (ftest1, ftest2)=(2, 2) is not present in table "pktable". 90INSERT INTO FKTABLE VALUES (NULL, 2, 4); 91ERROR: insert or update on table "fktable" violates foreign key constraint "constrname" 92DETAIL: MATCH FULL does not allow mixing of null and nonnull key values. 93INSERT INTO FKTABLE VALUES (1, NULL, 4); 94ERROR: insert or update on table "fktable" violates foreign key constraint "constrname" 95DETAIL: MATCH FULL does not allow mixing of null and nonnull key values. 96-- Check FKTABLE 97SELECT * FROM FKTABLE; 98 ftest1 | ftest2 | ftest3 99--------+--------+-------- 100 1 | 2 | 4 101 1 | 3 | 5 102 2 | 4 | 8 103 3 | 6 | 12 104 | | 0 105(5 rows) 106 107-- Delete a row from PK TABLE 108DELETE FROM PKTABLE WHERE ptest1=1 and ptest2=2; 109-- Check FKTABLE for removal of matched row 110SELECT * FROM FKTABLE; 111 ftest1 | ftest2 | ftest3 112--------+--------+-------- 113 1 | 3 | 5 114 2 | 4 | 8 115 3 | 6 | 12 116 | | 0 117 | | 4 118(5 rows) 119 120-- Delete another row from PK TABLE 121DELETE FROM PKTABLE WHERE ptest1=5 and ptest2=10; 122-- Check FKTABLE (should be no change) 123SELECT * FROM FKTABLE; 124 ftest1 | ftest2 | ftest3 125--------+--------+-------- 126 1 | 3 | 5 127 2 | 4 | 8 128 3 | 6 | 12 129 | | 0 130 | | 4 131(5 rows) 132 133-- Update a row from PK TABLE 134UPDATE PKTABLE SET ptest1=1 WHERE ptest1=2; 135-- Check FKTABLE for update of matched row 136SELECT * FROM FKTABLE; 137 ftest1 | ftest2 | ftest3 138--------+--------+-------- 139 1 | 3 | 5 140 3 | 6 | 12 141 | | 0 142 | | 4 143 | | 8 144(5 rows) 145 146-- Try altering the column type where foreign keys are involved 147ALTER TABLE PKTABLE ALTER COLUMN ptest1 TYPE bigint; 148ALTER TABLE FKTABLE ALTER COLUMN ftest1 TYPE bigint; 149SELECT * FROM PKTABLE; 150 ptest1 | ptest2 | ptest3 151--------+--------+--------- 152 1 | 3 | Test1-2 153 3 | 6 | Test3 154 4 | 8 | Test4 155 1 | 4 | Test2 156(4 rows) 157 158SELECT * FROM FKTABLE; 159 ftest1 | ftest2 | ftest3 160--------+--------+-------- 161 1 | 3 | 5 162 3 | 6 | 12 163 | | 0 164 | | 4 165 | | 8 166(5 rows) 167 168DROP TABLE PKTABLE CASCADE; 169NOTICE: drop cascades to constraint constrname on table fktable 170DROP TABLE FKTABLE; 171-- 172-- check set default and table constraint on multiple columns 173-- 174CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 text, PRIMARY KEY(ptest1, ptest2) ); 175CREATE TABLE FKTABLE ( ftest1 int DEFAULT -1, ftest2 int DEFAULT -2, ftest3 int, CONSTRAINT constrname2 FOREIGN KEY(ftest1, ftest2) 176 REFERENCES PKTABLE MATCH FULL ON DELETE SET DEFAULT ON UPDATE SET DEFAULT); 177-- Insert a value in PKTABLE for default 178INSERT INTO PKTABLE VALUES (-1, -2, 'The Default!'); 179-- Insert test data into PKTABLE 180INSERT INTO PKTABLE VALUES (1, 2, 'Test1'); 181INSERT INTO PKTABLE VALUES (1, 3, 'Test1-2'); 182INSERT INTO PKTABLE VALUES (2, 4, 'Test2'); 183INSERT INTO PKTABLE VALUES (3, 6, 'Test3'); 184INSERT INTO PKTABLE VALUES (4, 8, 'Test4'); 185INSERT INTO PKTABLE VALUES (5, 10, 'Test5'); 186-- Insert successful rows into FK TABLE 187INSERT INTO FKTABLE VALUES (1, 2, 4); 188INSERT INTO FKTABLE VALUES (1, 3, 5); 189INSERT INTO FKTABLE VALUES (2, 4, 8); 190INSERT INTO FKTABLE VALUES (3, 6, 12); 191INSERT INTO FKTABLE VALUES (NULL, NULL, 0); 192-- Insert failed rows into FK TABLE 193INSERT INTO FKTABLE VALUES (100, 2, 4); 194ERROR: insert or update on table "fktable" violates foreign key constraint "constrname2" 195DETAIL: Key (ftest1, ftest2)=(100, 2) is not present in table "pktable". 196INSERT INTO FKTABLE VALUES (2, 2, 4); 197ERROR: insert or update on table "fktable" violates foreign key constraint "constrname2" 198DETAIL: Key (ftest1, ftest2)=(2, 2) is not present in table "pktable". 199INSERT INTO FKTABLE VALUES (NULL, 2, 4); 200ERROR: insert or update on table "fktable" violates foreign key constraint "constrname2" 201DETAIL: MATCH FULL does not allow mixing of null and nonnull key values. 202INSERT INTO FKTABLE VALUES (1, NULL, 4); 203ERROR: insert or update on table "fktable" violates foreign key constraint "constrname2" 204DETAIL: MATCH FULL does not allow mixing of null and nonnull key values. 205-- Check FKTABLE 206SELECT * FROM FKTABLE; 207 ftest1 | ftest2 | ftest3 208--------+--------+-------- 209 1 | 2 | 4 210 1 | 3 | 5 211 2 | 4 | 8 212 3 | 6 | 12 213 | | 0 214(5 rows) 215 216-- Delete a row from PK TABLE 217DELETE FROM PKTABLE WHERE ptest1=1 and ptest2=2; 218-- Check FKTABLE to check for removal 219SELECT * FROM FKTABLE; 220 ftest1 | ftest2 | ftest3 221--------+--------+-------- 222 1 | 3 | 5 223 2 | 4 | 8 224 3 | 6 | 12 225 | | 0 226 -1 | -2 | 4 227(5 rows) 228 229-- Delete another row from PK TABLE 230DELETE FROM PKTABLE WHERE ptest1=5 and ptest2=10; 231-- Check FKTABLE (should be no change) 232SELECT * FROM FKTABLE; 233 ftest1 | ftest2 | ftest3 234--------+--------+-------- 235 1 | 3 | 5 236 2 | 4 | 8 237 3 | 6 | 12 238 | | 0 239 -1 | -2 | 4 240(5 rows) 241 242-- Update a row from PK TABLE 243UPDATE PKTABLE SET ptest1=1 WHERE ptest1=2; 244-- Check FKTABLE for update of matched row 245SELECT * FROM FKTABLE; 246 ftest1 | ftest2 | ftest3 247--------+--------+-------- 248 1 | 3 | 5 249 3 | 6 | 12 250 | | 0 251 -1 | -2 | 4 252 -1 | -2 | 8 253(5 rows) 254 255-- this should fail for lack of CASCADE 256DROP TABLE PKTABLE; 257ERROR: cannot drop table pktable because other objects depend on it 258DETAIL: constraint constrname2 on table fktable depends on table pktable 259HINT: Use DROP ... CASCADE to drop the dependent objects too. 260DROP TABLE PKTABLE CASCADE; 261NOTICE: drop cascades to constraint constrname2 on table fktable 262DROP TABLE FKTABLE; 263-- 264-- First test, check with no on delete or on update 265-- 266CREATE TABLE PKTABLE ( ptest1 int PRIMARY KEY, ptest2 text ); 267CREATE TABLE FKTABLE ( ftest1 int REFERENCES PKTABLE MATCH FULL, ftest2 int ); 268-- Insert test data into PKTABLE 269INSERT INTO PKTABLE VALUES (1, 'Test1'); 270INSERT INTO PKTABLE VALUES (2, 'Test2'); 271INSERT INTO PKTABLE VALUES (3, 'Test3'); 272INSERT INTO PKTABLE VALUES (4, 'Test4'); 273INSERT INTO PKTABLE VALUES (5, 'Test5'); 274-- Insert successful rows into FK TABLE 275INSERT INTO FKTABLE VALUES (1, 2); 276INSERT INTO FKTABLE VALUES (2, 3); 277INSERT INTO FKTABLE VALUES (3, 4); 278INSERT INTO FKTABLE VALUES (NULL, 1); 279-- Insert a failed row into FK TABLE 280INSERT INTO FKTABLE VALUES (100, 2); 281ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_fkey" 282DETAIL: Key (ftest1)=(100) is not present in table "pktable". 283-- Check FKTABLE 284SELECT * FROM FKTABLE; 285 ftest1 | ftest2 286--------+-------- 287 1 | 2 288 2 | 3 289 3 | 4 290 | 1 291(4 rows) 292 293-- Check PKTABLE 294SELECT * FROM PKTABLE; 295 ptest1 | ptest2 296--------+-------- 297 1 | Test1 298 2 | Test2 299 3 | Test3 300 4 | Test4 301 5 | Test5 302(5 rows) 303 304-- Delete a row from PK TABLE (should fail) 305DELETE FROM PKTABLE WHERE ptest1=1; 306ERROR: update or delete on table "pktable" violates foreign key constraint "fktable_ftest1_fkey" on table "fktable" 307DETAIL: Key (ptest1)=(1) is still referenced from table "fktable". 308-- Delete a row from PK TABLE (should succeed) 309DELETE FROM PKTABLE WHERE ptest1=5; 310-- Check PKTABLE for deletes 311SELECT * FROM PKTABLE; 312 ptest1 | ptest2 313--------+-------- 314 1 | Test1 315 2 | Test2 316 3 | Test3 317 4 | Test4 318(4 rows) 319 320-- Update a row from PK TABLE (should fail) 321UPDATE PKTABLE SET ptest1=0 WHERE ptest1=2; 322ERROR: update or delete on table "pktable" violates foreign key constraint "fktable_ftest1_fkey" on table "fktable" 323DETAIL: Key (ptest1)=(2) is still referenced from table "fktable". 324-- Update a row from PK TABLE (should succeed) 325UPDATE PKTABLE SET ptest1=0 WHERE ptest1=4; 326-- Check PKTABLE for updates 327SELECT * FROM PKTABLE; 328 ptest1 | ptest2 329--------+-------- 330 1 | Test1 331 2 | Test2 332 3 | Test3 333 0 | Test4 334(4 rows) 335 336DROP TABLE FKTABLE; 337DROP TABLE PKTABLE; 338-- MATCH SIMPLE 339-- Base test restricting update/delete 340CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2, ptest3) ); 341CREATE TABLE FKTABLE ( ftest1 int, ftest2 int, ftest3 int, ftest4 int, CONSTRAINT constrname3 342 FOREIGN KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE); 343-- Insert Primary Key values 344INSERT INTO PKTABLE VALUES (1, 2, 3, 'test1'); 345INSERT INTO PKTABLE VALUES (1, 3, 3, 'test2'); 346INSERT INTO PKTABLE VALUES (2, 3, 4, 'test3'); 347INSERT INTO PKTABLE VALUES (2, 4, 5, 'test4'); 348-- Insert Foreign Key values 349INSERT INTO FKTABLE VALUES (1, 2, 3, 1); 350INSERT INTO FKTABLE VALUES (NULL, 2, 3, 2); 351INSERT INTO FKTABLE VALUES (2, NULL, 3, 3); 352INSERT INTO FKTABLE VALUES (NULL, 2, 7, 4); 353INSERT INTO FKTABLE VALUES (NULL, 3, 4, 5); 354-- Insert a failed values 355INSERT INTO FKTABLE VALUES (1, 2, 7, 6); 356ERROR: insert or update on table "fktable" violates foreign key constraint "constrname3" 357DETAIL: Key (ftest1, ftest2, ftest3)=(1, 2, 7) is not present in table "pktable". 358-- Show FKTABLE 359SELECT * from FKTABLE; 360 ftest1 | ftest2 | ftest3 | ftest4 361--------+--------+--------+-------- 362 1 | 2 | 3 | 1 363 | 2 | 3 | 2 364 2 | | 3 | 3 365 | 2 | 7 | 4 366 | 3 | 4 | 5 367(5 rows) 368 369-- Try to update something that should fail 370UPDATE PKTABLE set ptest2=5 where ptest2=2; 371ERROR: update or delete on table "pktable" violates foreign key constraint "constrname3" on table "fktable" 372DETAIL: Key (ptest1, ptest2, ptest3)=(1, 2, 3) is still referenced from table "fktable". 373-- Try to update something that should succeed 374UPDATE PKTABLE set ptest1=1 WHERE ptest2=3; 375-- Try to delete something that should fail 376DELETE FROM PKTABLE where ptest1=1 and ptest2=2 and ptest3=3; 377ERROR: update or delete on table "pktable" violates foreign key constraint "constrname3" on table "fktable" 378DETAIL: Key (ptest1, ptest2, ptest3)=(1, 2, 3) is still referenced from table "fktable". 379-- Try to delete something that should work 380DELETE FROM PKTABLE where ptest1=2; 381-- Show PKTABLE and FKTABLE 382SELECT * from PKTABLE; 383 ptest1 | ptest2 | ptest3 | ptest4 384--------+--------+--------+-------- 385 1 | 2 | 3 | test1 386 1 | 3 | 3 | test2 387 1 | 3 | 4 | test3 388(3 rows) 389 390SELECT * from FKTABLE; 391 ftest1 | ftest2 | ftest3 | ftest4 392--------+--------+--------+-------- 393 1 | 2 | 3 | 1 394 | 2 | 3 | 2 395 2 | | 3 | 3 396 | 2 | 7 | 4 397 | 3 | 4 | 5 398(5 rows) 399 400DROP TABLE FKTABLE; 401DROP TABLE PKTABLE; 402-- cascade update/delete 403CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2, ptest3) ); 404CREATE TABLE FKTABLE ( ftest1 int, ftest2 int, ftest3 int, ftest4 int, CONSTRAINT constrname3 405 FOREIGN KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE 406 ON DELETE CASCADE ON UPDATE CASCADE); 407-- Insert Primary Key values 408INSERT INTO PKTABLE VALUES (1, 2, 3, 'test1'); 409INSERT INTO PKTABLE VALUES (1, 3, 3, 'test2'); 410INSERT INTO PKTABLE VALUES (2, 3, 4, 'test3'); 411INSERT INTO PKTABLE VALUES (2, 4, 5, 'test4'); 412-- Insert Foreign Key values 413INSERT INTO FKTABLE VALUES (1, 2, 3, 1); 414INSERT INTO FKTABLE VALUES (NULL, 2, 3, 2); 415INSERT INTO FKTABLE VALUES (2, NULL, 3, 3); 416INSERT INTO FKTABLE VALUES (NULL, 2, 7, 4); 417INSERT INTO FKTABLE VALUES (NULL, 3, 4, 5); 418-- Insert a failed values 419INSERT INTO FKTABLE VALUES (1, 2, 7, 6); 420ERROR: insert or update on table "fktable" violates foreign key constraint "constrname3" 421DETAIL: Key (ftest1, ftest2, ftest3)=(1, 2, 7) is not present in table "pktable". 422-- Show FKTABLE 423SELECT * from FKTABLE; 424 ftest1 | ftest2 | ftest3 | ftest4 425--------+--------+--------+-------- 426 1 | 2 | 3 | 1 427 | 2 | 3 | 2 428 2 | | 3 | 3 429 | 2 | 7 | 4 430 | 3 | 4 | 5 431(5 rows) 432 433-- Try to update something that will cascade 434UPDATE PKTABLE set ptest2=5 where ptest2=2; 435-- Try to update something that should not cascade 436UPDATE PKTABLE set ptest1=1 WHERE ptest2=3; 437-- Show PKTABLE and FKTABLE 438SELECT * from PKTABLE; 439 ptest1 | ptest2 | ptest3 | ptest4 440--------+--------+--------+-------- 441 2 | 4 | 5 | test4 442 1 | 5 | 3 | test1 443 1 | 3 | 3 | test2 444 1 | 3 | 4 | test3 445(4 rows) 446 447SELECT * from FKTABLE; 448 ftest1 | ftest2 | ftest3 | ftest4 449--------+--------+--------+-------- 450 | 2 | 3 | 2 451 2 | | 3 | 3 452 | 2 | 7 | 4 453 | 3 | 4 | 5 454 1 | 5 | 3 | 1 455(5 rows) 456 457-- Try to delete something that should cascade 458DELETE FROM PKTABLE where ptest1=1 and ptest2=5 and ptest3=3; 459-- Show PKTABLE and FKTABLE 460SELECT * from PKTABLE; 461 ptest1 | ptest2 | ptest3 | ptest4 462--------+--------+--------+-------- 463 2 | 4 | 5 | test4 464 1 | 3 | 3 | test2 465 1 | 3 | 4 | test3 466(3 rows) 467 468SELECT * from FKTABLE; 469 ftest1 | ftest2 | ftest3 | ftest4 470--------+--------+--------+-------- 471 | 2 | 3 | 2 472 2 | | 3 | 3 473 | 2 | 7 | 4 474 | 3 | 4 | 5 475(4 rows) 476 477-- Try to delete something that should not have a cascade 478DELETE FROM PKTABLE where ptest1=2; 479-- Show PKTABLE and FKTABLE 480SELECT * from PKTABLE; 481 ptest1 | ptest2 | ptest3 | ptest4 482--------+--------+--------+-------- 483 1 | 3 | 3 | test2 484 1 | 3 | 4 | test3 485(2 rows) 486 487SELECT * from FKTABLE; 488 ftest1 | ftest2 | ftest3 | ftest4 489--------+--------+--------+-------- 490 | 2 | 3 | 2 491 2 | | 3 | 3 492 | 2 | 7 | 4 493 | 3 | 4 | 5 494(4 rows) 495 496DROP TABLE FKTABLE; 497DROP TABLE PKTABLE; 498-- set null update / set default delete 499CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2, ptest3) ); 500CREATE TABLE FKTABLE ( ftest1 int DEFAULT 0, ftest2 int, ftest3 int, ftest4 int, CONSTRAINT constrname3 501 FOREIGN KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE 502 ON DELETE SET DEFAULT ON UPDATE SET NULL); 503-- Insert Primary Key values 504INSERT INTO PKTABLE VALUES (1, 2, 3, 'test1'); 505INSERT INTO PKTABLE VALUES (1, 3, 3, 'test2'); 506INSERT INTO PKTABLE VALUES (2, 3, 4, 'test3'); 507INSERT INTO PKTABLE VALUES (2, 4, 5, 'test4'); 508-- Insert Foreign Key values 509INSERT INTO FKTABLE VALUES (1, 2, 3, 1); 510INSERT INTO FKTABLE VALUES (2, 3, 4, 1); 511INSERT INTO FKTABLE VALUES (NULL, 2, 3, 2); 512INSERT INTO FKTABLE VALUES (2, NULL, 3, 3); 513INSERT INTO FKTABLE VALUES (NULL, 2, 7, 4); 514INSERT INTO FKTABLE VALUES (NULL, 3, 4, 5); 515-- Insert a failed values 516INSERT INTO FKTABLE VALUES (1, 2, 7, 6); 517ERROR: insert or update on table "fktable" violates foreign key constraint "constrname3" 518DETAIL: Key (ftest1, ftest2, ftest3)=(1, 2, 7) is not present in table "pktable". 519-- Show FKTABLE 520SELECT * from FKTABLE; 521 ftest1 | ftest2 | ftest3 | ftest4 522--------+--------+--------+-------- 523 1 | 2 | 3 | 1 524 2 | 3 | 4 | 1 525 | 2 | 3 | 2 526 2 | | 3 | 3 527 | 2 | 7 | 4 528 | 3 | 4 | 5 529(6 rows) 530 531-- Try to update something that will set null 532UPDATE PKTABLE set ptest2=5 where ptest2=2; 533-- Try to update something that should not set null 534UPDATE PKTABLE set ptest2=2 WHERE ptest2=3 and ptest1=1; 535-- Show PKTABLE and FKTABLE 536SELECT * from PKTABLE; 537 ptest1 | ptest2 | ptest3 | ptest4 538--------+--------+--------+-------- 539 2 | 3 | 4 | test3 540 2 | 4 | 5 | test4 541 1 | 5 | 3 | test1 542 1 | 2 | 3 | test2 543(4 rows) 544 545SELECT * from FKTABLE; 546 ftest1 | ftest2 | ftest3 | ftest4 547--------+--------+--------+-------- 548 2 | 3 | 4 | 1 549 | 2 | 3 | 2 550 2 | | 3 | 3 551 | 2 | 7 | 4 552 | 3 | 4 | 5 553 | | | 1 554(6 rows) 555 556-- Try to delete something that should set default 557DELETE FROM PKTABLE where ptest1=2 and ptest2=3 and ptest3=4; 558-- Show PKTABLE and FKTABLE 559SELECT * from PKTABLE; 560 ptest1 | ptest2 | ptest3 | ptest4 561--------+--------+--------+-------- 562 2 | 4 | 5 | test4 563 1 | 5 | 3 | test1 564 1 | 2 | 3 | test2 565(3 rows) 566 567SELECT * from FKTABLE; 568 ftest1 | ftest2 | ftest3 | ftest4 569--------+--------+--------+-------- 570 | 2 | 3 | 2 571 2 | | 3 | 3 572 | 2 | 7 | 4 573 | 3 | 4 | 5 574 | | | 1 575 0 | | | 1 576(6 rows) 577 578-- Try to delete something that should not set default 579DELETE FROM PKTABLE where ptest2=5; 580-- Show PKTABLE and FKTABLE 581SELECT * from PKTABLE; 582 ptest1 | ptest2 | ptest3 | ptest4 583--------+--------+--------+-------- 584 2 | 4 | 5 | test4 585 1 | 2 | 3 | test2 586(2 rows) 587 588SELECT * from FKTABLE; 589 ftest1 | ftest2 | ftest3 | ftest4 590--------+--------+--------+-------- 591 | 2 | 3 | 2 592 2 | | 3 | 3 593 | 2 | 7 | 4 594 | 3 | 4 | 5 595 | | | 1 596 0 | | | 1 597(6 rows) 598 599DROP TABLE FKTABLE; 600DROP TABLE PKTABLE; 601-- set default update / set null delete 602CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2, ptest3) ); 603CREATE TABLE FKTABLE ( ftest1 int DEFAULT 0, ftest2 int DEFAULT -1, ftest3 int DEFAULT -2, ftest4 int, CONSTRAINT constrname3 604 FOREIGN KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE 605 ON DELETE SET NULL ON UPDATE SET DEFAULT); 606-- Insert Primary Key values 607INSERT INTO PKTABLE VALUES (1, 2, 3, 'test1'); 608INSERT INTO PKTABLE VALUES (1, 3, 3, 'test2'); 609INSERT INTO PKTABLE VALUES (2, 3, 4, 'test3'); 610INSERT INTO PKTABLE VALUES (2, 4, 5, 'test4'); 611INSERT INTO PKTABLE VALUES (2, -1, 5, 'test5'); 612-- Insert Foreign Key values 613INSERT INTO FKTABLE VALUES (1, 2, 3, 1); 614INSERT INTO FKTABLE VALUES (2, 3, 4, 1); 615INSERT INTO FKTABLE VALUES (2, 4, 5, 1); 616INSERT INTO FKTABLE VALUES (NULL, 2, 3, 2); 617INSERT INTO FKTABLE VALUES (2, NULL, 3, 3); 618INSERT INTO FKTABLE VALUES (NULL, 2, 7, 4); 619INSERT INTO FKTABLE VALUES (NULL, 3, 4, 5); 620-- Insert a failed values 621INSERT INTO FKTABLE VALUES (1, 2, 7, 6); 622ERROR: insert or update on table "fktable" violates foreign key constraint "constrname3" 623DETAIL: Key (ftest1, ftest2, ftest3)=(1, 2, 7) is not present in table "pktable". 624-- Show FKTABLE 625SELECT * from FKTABLE; 626 ftest1 | ftest2 | ftest3 | ftest4 627--------+--------+--------+-------- 628 1 | 2 | 3 | 1 629 2 | 3 | 4 | 1 630 2 | 4 | 5 | 1 631 | 2 | 3 | 2 632 2 | | 3 | 3 633 | 2 | 7 | 4 634 | 3 | 4 | 5 635(7 rows) 636 637-- Try to update something that will fail 638UPDATE PKTABLE set ptest2=5 where ptest2=2; 639ERROR: insert or update on table "fktable" violates foreign key constraint "constrname3" 640DETAIL: Key (ftest1, ftest2, ftest3)=(0, -1, -2) is not present in table "pktable". 641-- Try to update something that will set default 642UPDATE PKTABLE set ptest1=0, ptest2=-1, ptest3=-2 where ptest2=2; 643UPDATE PKTABLE set ptest2=10 where ptest2=4; 644-- Try to update something that should not set default 645UPDATE PKTABLE set ptest2=2 WHERE ptest2=3 and ptest1=1; 646-- Show PKTABLE and FKTABLE 647SELECT * from PKTABLE; 648 ptest1 | ptest2 | ptest3 | ptest4 649--------+--------+--------+-------- 650 2 | 3 | 4 | test3 651 2 | -1 | 5 | test5 652 0 | -1 | -2 | test1 653 2 | 10 | 5 | test4 654 1 | 2 | 3 | test2 655(5 rows) 656 657SELECT * from FKTABLE; 658 ftest1 | ftest2 | ftest3 | ftest4 659--------+--------+--------+-------- 660 2 | 3 | 4 | 1 661 | 2 | 3 | 2 662 2 | | 3 | 3 663 | 2 | 7 | 4 664 | 3 | 4 | 5 665 0 | -1 | -2 | 1 666 0 | -1 | -2 | 1 667(7 rows) 668 669-- Try to delete something that should set null 670DELETE FROM PKTABLE where ptest1=2 and ptest2=3 and ptest3=4; 671-- Show PKTABLE and FKTABLE 672SELECT * from PKTABLE; 673 ptest1 | ptest2 | ptest3 | ptest4 674--------+--------+--------+-------- 675 2 | -1 | 5 | test5 676 0 | -1 | -2 | test1 677 2 | 10 | 5 | test4 678 1 | 2 | 3 | test2 679(4 rows) 680 681SELECT * from FKTABLE; 682 ftest1 | ftest2 | ftest3 | ftest4 683--------+--------+--------+-------- 684 | 2 | 3 | 2 685 2 | | 3 | 3 686 | 2 | 7 | 4 687 | 3 | 4 | 5 688 0 | -1 | -2 | 1 689 0 | -1 | -2 | 1 690 | | | 1 691(7 rows) 692 693-- Try to delete something that should not set null 694DELETE FROM PKTABLE where ptest2=-1 and ptest3=5; 695-- Show PKTABLE and FKTABLE 696SELECT * from PKTABLE; 697 ptest1 | ptest2 | ptest3 | ptest4 698--------+--------+--------+-------- 699 0 | -1 | -2 | test1 700 2 | 10 | 5 | test4 701 1 | 2 | 3 | test2 702(3 rows) 703 704SELECT * from FKTABLE; 705 ftest1 | ftest2 | ftest3 | ftest4 706--------+--------+--------+-------- 707 | 2 | 3 | 2 708 2 | | 3 | 3 709 | 2 | 7 | 4 710 | 3 | 4 | 5 711 0 | -1 | -2 | 1 712 0 | -1 | -2 | 1 713 | | | 1 714(7 rows) 715 716DROP TABLE FKTABLE; 717DROP TABLE PKTABLE; 718CREATE TABLE PKTABLE (ptest1 int PRIMARY KEY); 719CREATE TABLE FKTABLE_FAIL1 ( ftest1 int, CONSTRAINT fkfail1 FOREIGN KEY (ftest2) REFERENCES PKTABLE); 720ERROR: column "ftest2" referenced in foreign key constraint does not exist 721CREATE TABLE FKTABLE_FAIL2 ( ftest1 int, CONSTRAINT fkfail1 FOREIGN KEY (ftest1) REFERENCES PKTABLE(ptest2)); 722ERROR: column "ptest2" referenced in foreign key constraint does not exist 723DROP TABLE FKTABLE_FAIL1; 724ERROR: table "fktable_fail1" does not exist 725DROP TABLE FKTABLE_FAIL2; 726ERROR: table "fktable_fail2" does not exist 727DROP TABLE PKTABLE; 728-- Test for referencing column number smaller than referenced constraint 729CREATE TABLE PKTABLE (ptest1 int, ptest2 int, UNIQUE(ptest1, ptest2)); 730CREATE TABLE FKTABLE_FAIL1 (ftest1 int REFERENCES pktable(ptest1)); 731ERROR: there is no unique constraint matching given keys for referenced table "pktable" 732DROP TABLE FKTABLE_FAIL1; 733ERROR: table "fktable_fail1" does not exist 734DROP TABLE PKTABLE; 735-- 736-- Tests for mismatched types 737-- 738-- Basic one column, two table setup 739CREATE TABLE PKTABLE (ptest1 int PRIMARY KEY); 740INSERT INTO PKTABLE VALUES(42); 741-- This next should fail, because int=inet does not exist 742CREATE TABLE FKTABLE (ftest1 inet REFERENCES pktable); 743ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented 744DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: inet and integer. 745-- This should also fail for the same reason, but here we 746-- give the column name 747CREATE TABLE FKTABLE (ftest1 inet REFERENCES pktable(ptest1)); 748ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented 749DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: inet and integer. 750-- This should succeed, even though they are different types, 751-- because int=int8 exists and is a member of the integer opfamily 752CREATE TABLE FKTABLE (ftest1 int8 REFERENCES pktable); 753-- Check it actually works 754INSERT INTO FKTABLE VALUES(42); -- should succeed 755INSERT INTO FKTABLE VALUES(43); -- should fail 756ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_fkey" 757DETAIL: Key (ftest1)=(43) is not present in table "pktable". 758UPDATE FKTABLE SET ftest1 = ftest1; -- should succeed 759UPDATE FKTABLE SET ftest1 = ftest1 + 1; -- should fail 760ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_fkey" 761DETAIL: Key (ftest1)=(43) is not present in table "pktable". 762DROP TABLE FKTABLE; 763-- This should fail, because we'd have to cast numeric to int which is 764-- not an implicit coercion (or use numeric=numeric, but that's not part 765-- of the integer opfamily) 766CREATE TABLE FKTABLE (ftest1 numeric REFERENCES pktable); 767ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented 768DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: numeric and integer. 769DROP TABLE PKTABLE; 770-- On the other hand, this should work because int implicitly promotes to 771-- numeric, and we allow promotion on the FK side 772CREATE TABLE PKTABLE (ptest1 numeric PRIMARY KEY); 773INSERT INTO PKTABLE VALUES(42); 774CREATE TABLE FKTABLE (ftest1 int REFERENCES pktable); 775-- Check it actually works 776INSERT INTO FKTABLE VALUES(42); -- should succeed 777INSERT INTO FKTABLE VALUES(43); -- should fail 778ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_fkey" 779DETAIL: Key (ftest1)=(43) is not present in table "pktable". 780UPDATE FKTABLE SET ftest1 = ftest1; -- should succeed 781UPDATE FKTABLE SET ftest1 = ftest1 + 1; -- should fail 782ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_fkey" 783DETAIL: Key (ftest1)=(43) is not present in table "pktable". 784DROP TABLE FKTABLE; 785DROP TABLE PKTABLE; 786-- Two columns, two tables 787CREATE TABLE PKTABLE (ptest1 int, ptest2 inet, PRIMARY KEY(ptest1, ptest2)); 788-- This should fail, because we just chose really odd types 789CREATE TABLE FKTABLE (ftest1 cidr, ftest2 timestamp, FOREIGN KEY(ftest1, ftest2) REFERENCES pktable); 790ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented 791DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: cidr and integer. 792-- Again, so should this... 793CREATE TABLE FKTABLE (ftest1 cidr, ftest2 timestamp, FOREIGN KEY(ftest1, ftest2) REFERENCES pktable(ptest1, ptest2)); 794ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented 795DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: cidr and integer. 796-- This fails because we mixed up the column ordering 797CREATE TABLE FKTABLE (ftest1 int, ftest2 inet, FOREIGN KEY(ftest2, ftest1) REFERENCES pktable); 798ERROR: foreign key constraint "fktable_ftest2_fkey" cannot be implemented 799DETAIL: Key columns "ftest2" and "ptest1" are of incompatible types: inet and integer. 800-- As does this... 801CREATE TABLE FKTABLE (ftest1 int, ftest2 inet, FOREIGN KEY(ftest2, ftest1) REFERENCES pktable(ptest1, ptest2)); 802ERROR: foreign key constraint "fktable_ftest2_fkey" cannot be implemented 803DETAIL: Key columns "ftest2" and "ptest1" are of incompatible types: inet and integer. 804-- And again.. 805CREATE TABLE FKTABLE (ftest1 int, ftest2 inet, FOREIGN KEY(ftest1, ftest2) REFERENCES pktable(ptest2, ptest1)); 806ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented 807DETAIL: Key columns "ftest1" and "ptest2" are of incompatible types: integer and inet. 808-- This works... 809CREATE TABLE FKTABLE (ftest1 int, ftest2 inet, FOREIGN KEY(ftest2, ftest1) REFERENCES pktable(ptest2, ptest1)); 810DROP TABLE FKTABLE; 811-- As does this 812CREATE TABLE FKTABLE (ftest1 int, ftest2 inet, FOREIGN KEY(ftest1, ftest2) REFERENCES pktable(ptest1, ptest2)); 813DROP TABLE FKTABLE; 814DROP TABLE PKTABLE; 815-- Two columns, same table 816-- Make sure this still works... 817CREATE TABLE PKTABLE (ptest1 int, ptest2 inet, ptest3 int, ptest4 inet, PRIMARY KEY(ptest1, ptest2), FOREIGN KEY(ptest3, 818ptest4) REFERENCES pktable(ptest1, ptest2)); 819DROP TABLE PKTABLE; 820-- And this, 821CREATE TABLE PKTABLE (ptest1 int, ptest2 inet, ptest3 int, ptest4 inet, PRIMARY KEY(ptest1, ptest2), FOREIGN KEY(ptest3, 822ptest4) REFERENCES pktable); 823DROP TABLE PKTABLE; 824-- This shouldn't (mixed up columns) 825CREATE TABLE PKTABLE (ptest1 int, ptest2 inet, ptest3 int, ptest4 inet, PRIMARY KEY(ptest1, ptest2), FOREIGN KEY(ptest3, 826ptest4) REFERENCES pktable(ptest2, ptest1)); 827ERROR: foreign key constraint "pktable_ptest3_fkey" cannot be implemented 828DETAIL: Key columns "ptest3" and "ptest2" are of incompatible types: integer and inet. 829-- Nor should this... (same reason, we have 4,3 referencing 1,2 which mismatches types 830CREATE TABLE PKTABLE (ptest1 int, ptest2 inet, ptest3 int, ptest4 inet, PRIMARY KEY(ptest1, ptest2), FOREIGN KEY(ptest4, 831ptest3) REFERENCES pktable(ptest1, ptest2)); 832ERROR: foreign key constraint "pktable_ptest4_fkey" cannot be implemented 833DETAIL: Key columns "ptest4" and "ptest1" are of incompatible types: inet and integer. 834-- Not this one either... Same as the last one except we didn't defined the columns being referenced. 835CREATE TABLE PKTABLE (ptest1 int, ptest2 inet, ptest3 int, ptest4 inet, PRIMARY KEY(ptest1, ptest2), FOREIGN KEY(ptest4, 836ptest3) REFERENCES pktable); 837ERROR: foreign key constraint "pktable_ptest4_fkey" cannot be implemented 838DETAIL: Key columns "ptest4" and "ptest1" are of incompatible types: inet and integer. 839-- 840-- Now some cases with inheritance 841-- Basic 2 table case: 1 column of matching types. 842create table pktable_base (base1 int not null); 843create table pktable (ptest1 int, primary key(base1), unique(base1, ptest1)) inherits (pktable_base); 844create table fktable (ftest1 int references pktable(base1)); 845-- now some ins, upd, del 846insert into pktable(base1) values (1); 847insert into pktable(base1) values (2); 848-- let's insert a non-existent fktable value 849insert into fktable(ftest1) values (3); 850ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_fkey" 851DETAIL: Key (ftest1)=(3) is not present in table "pktable". 852-- let's make a valid row for that 853insert into pktable(base1) values (3); 854insert into fktable(ftest1) values (3); 855-- let's try removing a row that should fail from pktable 856delete from pktable where base1>2; 857ERROR: update or delete on table "pktable" violates foreign key constraint "fktable_ftest1_fkey" on table "fktable" 858DETAIL: Key (base1)=(3) is still referenced from table "fktable". 859-- okay, let's try updating all of the base1 values to *4 860-- which should fail. 861update pktable set base1=base1*4; 862ERROR: update or delete on table "pktable" violates foreign key constraint "fktable_ftest1_fkey" on table "fktable" 863DETAIL: Key (base1)=(3) is still referenced from table "fktable". 864-- okay, let's try an update that should work. 865update pktable set base1=base1*4 where base1<3; 866-- and a delete that should work 867delete from pktable where base1>3; 868-- cleanup 869drop table fktable; 870delete from pktable; 871-- Now 2 columns 2 tables, matching types 872create table fktable (ftest1 int, ftest2 int, foreign key(ftest1, ftest2) references pktable(base1, ptest1)); 873-- now some ins, upd, del 874insert into pktable(base1, ptest1) values (1, 1); 875insert into pktable(base1, ptest1) values (2, 2); 876-- let's insert a non-existent fktable value 877insert into fktable(ftest1, ftest2) values (3, 1); 878ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_fkey" 879DETAIL: Key (ftest1, ftest2)=(3, 1) is not present in table "pktable". 880-- let's make a valid row for that 881insert into pktable(base1,ptest1) values (3, 1); 882insert into fktable(ftest1, ftest2) values (3, 1); 883-- let's try removing a row that should fail from pktable 884delete from pktable where base1>2; 885ERROR: update or delete on table "pktable" violates foreign key constraint "fktable_ftest1_fkey" on table "fktable" 886DETAIL: Key (base1, ptest1)=(3, 1) is still referenced from table "fktable". 887-- okay, let's try updating all of the base1 values to *4 888-- which should fail. 889update pktable set base1=base1*4; 890ERROR: update or delete on table "pktable" violates foreign key constraint "fktable_ftest1_fkey" on table "fktable" 891DETAIL: Key (base1, ptest1)=(3, 1) is still referenced from table "fktable". 892-- okay, let's try an update that should work. 893update pktable set base1=base1*4 where base1<3; 894-- and a delete that should work 895delete from pktable where base1>3; 896-- cleanup 897drop table fktable; 898drop table pktable; 899drop table pktable_base; 900-- Now we'll do one all in 1 table with 2 columns of matching types 901create table pktable_base(base1 int not null, base2 int); 902create table pktable(ptest1 int, ptest2 int, primary key(base1, ptest1), foreign key(base2, ptest2) references 903 pktable(base1, ptest1)) inherits (pktable_base); 904insert into pktable (base1, ptest1, base2, ptest2) values (1, 1, 1, 1); 905insert into pktable (base1, ptest1, base2, ptest2) values (2, 1, 1, 1); 906insert into pktable (base1, ptest1, base2, ptest2) values (2, 2, 2, 1); 907insert into pktable (base1, ptest1, base2, ptest2) values (1, 3, 2, 2); 908-- fails (3,2) isn't in base1, ptest1 909insert into pktable (base1, ptest1, base2, ptest2) values (2, 3, 3, 2); 910ERROR: insert or update on table "pktable" violates foreign key constraint "pktable_base2_fkey" 911DETAIL: Key (base2, ptest2)=(3, 2) is not present in table "pktable". 912-- fails (2,2) is being referenced 913delete from pktable where base1=2; 914ERROR: update or delete on table "pktable" violates foreign key constraint "pktable_base2_fkey" on table "pktable" 915DETAIL: Key (base1, ptest1)=(2, 2) is still referenced from table "pktable". 916-- fails (1,1) is being referenced (twice) 917update pktable set base1=3 where base1=1; 918ERROR: update or delete on table "pktable" violates foreign key constraint "pktable_base2_fkey" on table "pktable" 919DETAIL: Key (base1, ptest1)=(1, 1) is still referenced from table "pktable". 920-- this sequence of two deletes will work, since after the first there will be no (2,*) references 921delete from pktable where base2=2; 922delete from pktable where base1=2; 923drop table pktable; 924drop table pktable_base; 925-- 2 columns (2 tables), mismatched types 926create table pktable_base(base1 int not null); 927create table pktable(ptest1 inet, primary key(base1, ptest1)) inherits (pktable_base); 928-- just generally bad types (with and without column references on the referenced table) 929create table fktable(ftest1 cidr, ftest2 int[], foreign key (ftest1, ftest2) references pktable); 930ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented 931DETAIL: Key columns "ftest1" and "base1" are of incompatible types: cidr and integer. 932create table fktable(ftest1 cidr, ftest2 int[], foreign key (ftest1, ftest2) references pktable(base1, ptest1)); 933ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented 934DETAIL: Key columns "ftest1" and "base1" are of incompatible types: cidr and integer. 935-- let's mix up which columns reference which 936create table fktable(ftest1 int, ftest2 inet, foreign key(ftest2, ftest1) references pktable); 937ERROR: foreign key constraint "fktable_ftest2_fkey" cannot be implemented 938DETAIL: Key columns "ftest2" and "base1" are of incompatible types: inet and integer. 939create table fktable(ftest1 int, ftest2 inet, foreign key(ftest2, ftest1) references pktable(base1, ptest1)); 940ERROR: foreign key constraint "fktable_ftest2_fkey" cannot be implemented 941DETAIL: Key columns "ftest2" and "base1" are of incompatible types: inet and integer. 942create table fktable(ftest1 int, ftest2 inet, foreign key(ftest1, ftest2) references pktable(ptest1, base1)); 943ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented 944DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: integer and inet. 945drop table pktable; 946drop table pktable_base; 947-- 2 columns (1 table), mismatched types 948create table pktable_base(base1 int not null, base2 int); 949create table pktable(ptest1 inet, ptest2 inet[], primary key(base1, ptest1), foreign key(base2, ptest2) references 950 pktable(base1, ptest1)) inherits (pktable_base); 951ERROR: foreign key constraint "pktable_base2_fkey" cannot be implemented 952DETAIL: Key columns "ptest2" and "ptest1" are of incompatible types: inet[] and inet. 953create table pktable(ptest1 inet, ptest2 inet, primary key(base1, ptest1), foreign key(base2, ptest2) references 954 pktable(ptest1, base1)) inherits (pktable_base); 955ERROR: foreign key constraint "pktable_base2_fkey" cannot be implemented 956DETAIL: Key columns "base2" and "ptest1" are of incompatible types: integer and inet. 957create table pktable(ptest1 inet, ptest2 inet, primary key(base1, ptest1), foreign key(ptest2, base2) references 958 pktable(base1, ptest1)) inherits (pktable_base); 959ERROR: foreign key constraint "pktable_ptest2_fkey" cannot be implemented 960DETAIL: Key columns "ptest2" and "base1" are of incompatible types: inet and integer. 961create table pktable(ptest1 inet, ptest2 inet, primary key(base1, ptest1), foreign key(ptest2, base2) references 962 pktable(base1, ptest1)) inherits (pktable_base); 963ERROR: foreign key constraint "pktable_ptest2_fkey" cannot be implemented 964DETAIL: Key columns "ptest2" and "base1" are of incompatible types: inet and integer. 965drop table pktable; 966ERROR: table "pktable" does not exist 967drop table pktable_base; 968-- 969-- Deferrable constraints 970-- (right now, only FOREIGN KEY constraints can be deferred) 971-- 972-- deferrable, explicitly deferred 973CREATE TABLE pktable ( 974 id INT4 PRIMARY KEY, 975 other INT4 976); 977CREATE TABLE fktable ( 978 id INT4 PRIMARY KEY, 979 fk INT4 REFERENCES pktable DEFERRABLE 980); 981-- default to immediate: should fail 982INSERT INTO fktable VALUES (5, 10); 983ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey" 984DETAIL: Key (fk)=(10) is not present in table "pktable". 985-- explicitly defer the constraint 986BEGIN; 987SET CONSTRAINTS ALL DEFERRED; 988INSERT INTO fktable VALUES (10, 15); 989INSERT INTO pktable VALUES (15, 0); -- make the FK insert valid 990COMMIT; 991DROP TABLE fktable, pktable; 992-- deferrable, initially deferred 993CREATE TABLE pktable ( 994 id INT4 PRIMARY KEY, 995 other INT4 996); 997CREATE TABLE fktable ( 998 id INT4 PRIMARY KEY, 999 fk INT4 REFERENCES pktable DEFERRABLE INITIALLY DEFERRED 1000); 1001-- default to deferred, should succeed 1002BEGIN; 1003INSERT INTO fktable VALUES (100, 200); 1004INSERT INTO pktable VALUES (200, 500); -- make the FK insert valid 1005COMMIT; 1006-- default to deferred, explicitly make immediate 1007BEGIN; 1008SET CONSTRAINTS ALL IMMEDIATE; 1009-- should fail 1010INSERT INTO fktable VALUES (500, 1000); 1011ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey" 1012DETAIL: Key (fk)=(1000) is not present in table "pktable". 1013COMMIT; 1014DROP TABLE fktable, pktable; 1015-- tricky behavior: according to SQL99, if a deferred constraint is set 1016-- to 'immediate' mode, it should be checked for validity *immediately*, 1017-- not when the current transaction commits (i.e. the mode change applies 1018-- retroactively) 1019CREATE TABLE pktable ( 1020 id INT4 PRIMARY KEY, 1021 other INT4 1022); 1023CREATE TABLE fktable ( 1024 id INT4 PRIMARY KEY, 1025 fk INT4 REFERENCES pktable DEFERRABLE 1026); 1027BEGIN; 1028SET CONSTRAINTS ALL DEFERRED; 1029-- should succeed, for now 1030INSERT INTO fktable VALUES (1000, 2000); 1031-- should cause transaction abort, due to preceding error 1032SET CONSTRAINTS ALL IMMEDIATE; 1033ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey" 1034DETAIL: Key (fk)=(2000) is not present in table "pktable". 1035INSERT INTO pktable VALUES (2000, 3); -- too late 1036ERROR: current transaction is aborted, commands ignored until end of transaction block 1037COMMIT; 1038DROP TABLE fktable, pktable; 1039-- deferrable, initially deferred 1040CREATE TABLE pktable ( 1041 id INT4 PRIMARY KEY, 1042 other INT4 1043); 1044CREATE TABLE fktable ( 1045 id INT4 PRIMARY KEY, 1046 fk INT4 REFERENCES pktable DEFERRABLE INITIALLY DEFERRED 1047); 1048BEGIN; 1049-- no error here 1050INSERT INTO fktable VALUES (100, 200); 1051-- error here on commit 1052COMMIT; 1053ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey" 1054DETAIL: Key (fk)=(200) is not present in table "pktable". 1055DROP TABLE pktable, fktable; 1056-- test notice about expensive referential integrity checks, 1057-- where the index cannot be used because of type incompatibilities. 1058CREATE TEMP TABLE pktable ( 1059 id1 INT4 PRIMARY KEY, 1060 id2 VARCHAR(4) UNIQUE, 1061 id3 REAL UNIQUE, 1062 UNIQUE(id1, id2, id3) 1063); 1064CREATE TEMP TABLE fktable ( 1065 x1 INT4 REFERENCES pktable(id1), 1066 x2 VARCHAR(4) REFERENCES pktable(id2), 1067 x3 REAL REFERENCES pktable(id3), 1068 x4 TEXT, 1069 x5 INT2 1070); 1071-- check individual constraints with alter table. 1072-- should fail 1073-- varchar does not promote to real 1074ALTER TABLE fktable ADD CONSTRAINT fk_2_3 1075FOREIGN KEY (x2) REFERENCES pktable(id3); 1076ERROR: foreign key constraint "fk_2_3" cannot be implemented 1077DETAIL: Key columns "x2" and "id3" are of incompatible types: character varying and real. 1078-- nor to int4 1079ALTER TABLE fktable ADD CONSTRAINT fk_2_1 1080FOREIGN KEY (x2) REFERENCES pktable(id1); 1081ERROR: foreign key constraint "fk_2_1" cannot be implemented 1082DETAIL: Key columns "x2" and "id1" are of incompatible types: character varying and integer. 1083-- real does not promote to int4 1084ALTER TABLE fktable ADD CONSTRAINT fk_3_1 1085FOREIGN KEY (x3) REFERENCES pktable(id1); 1086ERROR: foreign key constraint "fk_3_1" cannot be implemented 1087DETAIL: Key columns "x3" and "id1" are of incompatible types: real and integer. 1088-- int4 does not promote to text 1089ALTER TABLE fktable ADD CONSTRAINT fk_1_2 1090FOREIGN KEY (x1) REFERENCES pktable(id2); 1091ERROR: foreign key constraint "fk_1_2" cannot be implemented 1092DETAIL: Key columns "x1" and "id2" are of incompatible types: integer and character varying. 1093-- should succeed 1094-- int4 promotes to real 1095ALTER TABLE fktable ADD CONSTRAINT fk_1_3 1096FOREIGN KEY (x1) REFERENCES pktable(id3); 1097-- text is compatible with varchar 1098ALTER TABLE fktable ADD CONSTRAINT fk_4_2 1099FOREIGN KEY (x4) REFERENCES pktable(id2); 1100-- int2 is part of integer opfamily as of 8.0 1101ALTER TABLE fktable ADD CONSTRAINT fk_5_1 1102FOREIGN KEY (x5) REFERENCES pktable(id1); 1103-- check multikey cases, especially out-of-order column lists 1104-- these should work 1105ALTER TABLE fktable ADD CONSTRAINT fk_123_123 1106FOREIGN KEY (x1,x2,x3) REFERENCES pktable(id1,id2,id3); 1107ALTER TABLE fktable ADD CONSTRAINT fk_213_213 1108FOREIGN KEY (x2,x1,x3) REFERENCES pktable(id2,id1,id3); 1109ALTER TABLE fktable ADD CONSTRAINT fk_253_213 1110FOREIGN KEY (x2,x5,x3) REFERENCES pktable(id2,id1,id3); 1111-- these should fail 1112ALTER TABLE fktable ADD CONSTRAINT fk_123_231 1113FOREIGN KEY (x1,x2,x3) REFERENCES pktable(id2,id3,id1); 1114ERROR: foreign key constraint "fk_123_231" cannot be implemented 1115DETAIL: Key columns "x1" and "id2" are of incompatible types: integer and character varying. 1116ALTER TABLE fktable ADD CONSTRAINT fk_241_132 1117FOREIGN KEY (x2,x4,x1) REFERENCES pktable(id1,id3,id2); 1118ERROR: foreign key constraint "fk_241_132" cannot be implemented 1119DETAIL: Key columns "x2" and "id1" are of incompatible types: character varying and integer. 1120DROP TABLE pktable, fktable; 1121-- test a tricky case: we can elide firing the FK check trigger during 1122-- an UPDATE if the UPDATE did not change the foreign key 1123-- field. However, we can't do this if our transaction was the one that 1124-- created the updated row and the trigger is deferred, since our UPDATE 1125-- will have invalidated the original newly-inserted tuple, and therefore 1126-- cause the on-INSERT RI trigger not to be fired. 1127CREATE TEMP TABLE pktable ( 1128 id int primary key, 1129 other int 1130); 1131CREATE TEMP TABLE fktable ( 1132 id int primary key, 1133 fk int references pktable deferrable initially deferred 1134); 1135INSERT INTO pktable VALUES (5, 10); 1136BEGIN; 1137-- doesn't match PK, but no error yet 1138INSERT INTO fktable VALUES (0, 20); 1139-- don't change FK 1140UPDATE fktable SET id = id + 1; 1141-- should catch error from initial INSERT 1142COMMIT; 1143ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey" 1144DETAIL: Key (fk)=(20) is not present in table "pktable". 1145-- check same case when insert is in a different subtransaction than update 1146BEGIN; 1147-- doesn't match PK, but no error yet 1148INSERT INTO fktable VALUES (0, 20); 1149-- UPDATE will be in a subxact 1150SAVEPOINT savept1; 1151-- don't change FK 1152UPDATE fktable SET id = id + 1; 1153-- should catch error from initial INSERT 1154COMMIT; 1155ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey" 1156DETAIL: Key (fk)=(20) is not present in table "pktable". 1157BEGIN; 1158-- INSERT will be in a subxact 1159SAVEPOINT savept1; 1160-- doesn't match PK, but no error yet 1161INSERT INTO fktable VALUES (0, 20); 1162RELEASE SAVEPOINT savept1; 1163-- don't change FK 1164UPDATE fktable SET id = id + 1; 1165-- should catch error from initial INSERT 1166COMMIT; 1167ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey" 1168DETAIL: Key (fk)=(20) is not present in table "pktable". 1169BEGIN; 1170-- doesn't match PK, but no error yet 1171INSERT INTO fktable VALUES (0, 20); 1172-- UPDATE will be in a subxact 1173SAVEPOINT savept1; 1174-- don't change FK 1175UPDATE fktable SET id = id + 1; 1176-- Roll back the UPDATE 1177ROLLBACK TO savept1; 1178-- should catch error from initial INSERT 1179COMMIT; 1180ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey" 1181DETAIL: Key (fk)=(20) is not present in table "pktable". 1182-- 1183-- check ALTER CONSTRAINT 1184-- 1185INSERT INTO fktable VALUES (1, 5); 1186ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey DEFERRABLE INITIALLY IMMEDIATE; 1187BEGIN; 1188-- doesn't match FK, should throw error now 1189UPDATE pktable SET id = 10 WHERE id = 5; 1190ERROR: update or delete on table "pktable" violates foreign key constraint "fktable_fk_fkey" on table "fktable" 1191DETAIL: Key (id)=(5) is still referenced from table "fktable". 1192COMMIT; 1193BEGIN; 1194-- doesn't match PK, should throw error now 1195INSERT INTO fktable VALUES (0, 20); 1196ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey" 1197DETAIL: Key (fk)=(20) is not present in table "pktable". 1198COMMIT; 1199-- try additional syntax 1200ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NOT DEFERRABLE; 1201-- illegal option 1202ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NOT DEFERRABLE INITIALLY DEFERRED; 1203ERROR: constraint declared INITIALLY DEFERRED must be DEFERRABLE 1204LINE 1: ...e ALTER CONSTRAINT fktable_fk_fkey NOT DEFERRABLE INITIALLY ... 1205 ^ 1206-- test order of firing of FK triggers when several RI-induced changes need to 1207-- be made to the same row. This was broken by subtransaction-related 1208-- changes in 8.0. 1209CREATE TEMP TABLE users ( 1210 id INT PRIMARY KEY, 1211 name VARCHAR NOT NULL 1212); 1213INSERT INTO users VALUES (1, 'Jozko'); 1214INSERT INTO users VALUES (2, 'Ferko'); 1215INSERT INTO users VALUES (3, 'Samko'); 1216CREATE TEMP TABLE tasks ( 1217 id INT PRIMARY KEY, 1218 owner INT REFERENCES users ON UPDATE CASCADE ON DELETE SET NULL, 1219 worker INT REFERENCES users ON UPDATE CASCADE ON DELETE SET NULL, 1220 checked_by INT REFERENCES users ON UPDATE CASCADE ON DELETE SET NULL 1221); 1222INSERT INTO tasks VALUES (1,1,NULL,NULL); 1223INSERT INTO tasks VALUES (2,2,2,NULL); 1224INSERT INTO tasks VALUES (3,3,3,3); 1225SELECT * FROM tasks; 1226 id | owner | worker | checked_by 1227----+-------+--------+------------ 1228 1 | 1 | | 1229 2 | 2 | 2 | 1230 3 | 3 | 3 | 3 1231(3 rows) 1232 1233UPDATE users SET id = 4 WHERE id = 3; 1234SELECT * FROM tasks; 1235 id | owner | worker | checked_by 1236----+-------+--------+------------ 1237 1 | 1 | | 1238 2 | 2 | 2 | 1239 3 | 4 | 4 | 4 1240(3 rows) 1241 1242DELETE FROM users WHERE id = 4; 1243SELECT * FROM tasks; 1244 id | owner | worker | checked_by 1245----+-------+--------+------------ 1246 1 | 1 | | 1247 2 | 2 | 2 | 1248 3 | | | 1249(3 rows) 1250 1251-- could fail with only 2 changes to make, if row was already updated 1252BEGIN; 1253UPDATE tasks set id=id WHERE id=2; 1254SELECT * FROM tasks; 1255 id | owner | worker | checked_by 1256----+-------+--------+------------ 1257 1 | 1 | | 1258 3 | | | 1259 2 | 2 | 2 | 1260(3 rows) 1261 1262DELETE FROM users WHERE id = 2; 1263SELECT * FROM tasks; 1264 id | owner | worker | checked_by 1265----+-------+--------+------------ 1266 1 | 1 | | 1267 3 | | | 1268 2 | | | 1269(3 rows) 1270 1271COMMIT; 1272-- 1273-- Test self-referential FK with CASCADE (bug #6268) 1274-- 1275create temp table selfref ( 1276 a int primary key, 1277 b int, 1278 foreign key (b) references selfref (a) 1279 on update cascade on delete cascade 1280); 1281insert into selfref (a, b) 1282values 1283 (0, 0), 1284 (1, 1); 1285begin; 1286 update selfref set a = 123 where a = 0; 1287 select a, b from selfref; 1288 a | b 1289-----+----- 1290 1 | 1 1291 123 | 123 1292(2 rows) 1293 1294 update selfref set a = 456 where a = 123; 1295 select a, b from selfref; 1296 a | b 1297-----+----- 1298 1 | 1 1299 456 | 456 1300(2 rows) 1301 1302commit; 1303-- 1304-- Test that SET DEFAULT actions recognize updates to default values 1305-- 1306create temp table defp (f1 int primary key); 1307create temp table defc (f1 int default 0 1308 references defp on delete set default); 1309insert into defp values (0), (1), (2); 1310insert into defc values (2); 1311select * from defc; 1312 f1 1313---- 1314 2 1315(1 row) 1316 1317delete from defp where f1 = 2; 1318select * from defc; 1319 f1 1320---- 1321 0 1322(1 row) 1323 1324delete from defp where f1 = 0; -- fail 1325ERROR: update or delete on table "defp" violates foreign key constraint "defc_f1_fkey" on table "defc" 1326DETAIL: Key (f1)=(0) is still referenced from table "defc". 1327alter table defc alter column f1 set default 1; 1328delete from defp where f1 = 0; 1329select * from defc; 1330 f1 1331---- 1332 1 1333(1 row) 1334 1335delete from defp where f1 = 1; -- fail 1336ERROR: update or delete on table "defp" violates foreign key constraint "defc_f1_fkey" on table "defc" 1337DETAIL: Key (f1)=(1) is still referenced from table "defc". 1338-- 1339-- Test the difference between NO ACTION and RESTRICT 1340-- 1341create temp table pp (f1 int primary key); 1342create temp table cc (f1 int references pp on update no action); 1343insert into pp values(12); 1344insert into pp values(11); 1345update pp set f1=f1+1; 1346insert into cc values(13); 1347update pp set f1=f1+1; 1348update pp set f1=f1+1; -- fail 1349ERROR: update or delete on table "pp" violates foreign key constraint "cc_f1_fkey" on table "cc" 1350DETAIL: Key (f1)=(13) is still referenced from table "cc". 1351drop table pp, cc; 1352create temp table pp (f1 int primary key); 1353create temp table cc (f1 int references pp on update restrict); 1354insert into pp values(12); 1355insert into pp values(11); 1356update pp set f1=f1+1; 1357insert into cc values(13); 1358update pp set f1=f1+1; -- fail 1359ERROR: update or delete on table "pp" violates foreign key constraint "cc_f1_fkey" on table "cc" 1360DETAIL: Key (f1)=(13) is still referenced from table "cc". 1361drop table pp, cc; 1362-- 1363-- Test interaction of foreign-key optimization with rules (bug #14219) 1364-- 1365create temp table t1 (a integer primary key, b text); 1366create temp table t2 (a integer primary key, b integer references t1); 1367create rule r1 as on delete to t1 do delete from t2 where t2.b = old.a; 1368explain (costs off) delete from t1 where a = 1; 1369 QUERY PLAN 1370-------------------------------------------- 1371 Delete on t2 1372 -> Nested Loop 1373 -> Index Scan using t1_pkey on t1 1374 Index Cond: (a = 1) 1375 -> Seq Scan on t2 1376 Filter: (b = 1) 1377 1378 Delete on t1 1379 -> Index Scan using t1_pkey on t1 1380 Index Cond: (a = 1) 1381(10 rows) 1382 1383delete from t1 where a = 1; 1384-- 1385-- Test deferred FK check on a tuple deleted by a rolled-back subtransaction 1386-- 1387create table pktable2(f1 int primary key); 1388create table fktable2(f1 int references pktable2 deferrable initially deferred); 1389insert into pktable2 values(1); 1390begin; 1391insert into fktable2 values(1); 1392savepoint x; 1393delete from fktable2; 1394rollback to x; 1395commit; 1396begin; 1397insert into fktable2 values(2); 1398savepoint x; 1399delete from fktable2; 1400rollback to x; 1401commit; -- fail 1402ERROR: insert or update on table "fktable2" violates foreign key constraint "fktable2_f1_fkey" 1403DETAIL: Key (f1)=(2) is not present in table "pktable2". 1404-- 1405-- Test that we prevent dropping FK constraint with pending trigger events 1406-- 1407begin; 1408insert into fktable2 values(2); 1409alter table fktable2 drop constraint fktable2_f1_fkey; 1410ERROR: cannot ALTER TABLE "fktable2" because it has pending trigger events 1411commit; 1412begin; 1413delete from pktable2 where f1 = 1; 1414alter table fktable2 drop constraint fktable2_f1_fkey; 1415ERROR: cannot ALTER TABLE "pktable2" because it has pending trigger events 1416commit; 1417drop table pktable2, fktable2; 1418