src/test/regress/sql/foreign_key.sql

   1 --
   2 -- FOREIGN KEY
   3 --
   4
   5 -- MATCH FULL
   6 --
   7 -- First test, check and cascade
   8 --
   9 CREATE TABLE PKTABLE ( ptest1 int PRIMARY KEY, ptest2 text );
  10 CREATE TABLE FKTABLE ( ftest1 int REFERENCES PKTABLE MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE, ftest2 int );
  11
  12 -- Insert test data into PKTABLE
  13 INSERT INTO PKTABLE VALUES (1, 'Test1');
  14 INSERT INTO PKTABLE VALUES (2, 'Test2');
  15 INSERT INTO PKTABLE VALUES (3, 'Test3');
  16 INSERT INTO PKTABLE VALUES (4, 'Test4');
  17 INSERT INTO PKTABLE VALUES (5, 'Test5');
  18
  19 -- Insert successful rows into FK TABLE
  20 INSERT INTO FKTABLE VALUES (1, 2);
  21 INSERT INTO FKTABLE VALUES (2, 3);
  22 INSERT INTO FKTABLE VALUES (3, 4);
  23 INSERT INTO FKTABLE VALUES (NULL, 1);
  24
  25 -- Insert a failed row into FK TABLE
  26 INSERT INTO FKTABLE VALUES (100, 2);
  27
  28 -- Check FKTABLE
  29 SELECT * FROM FKTABLE;
  30
  31 -- Delete a row from PK TABLE
  32 DELETE FROM PKTABLE WHERE ptest1=1;
  33
  34 -- Check FKTABLE for removal of matched row
  35 SELECT * FROM FKTABLE;
  36
  37 -- Update a row from PK TABLE
  38 UPDATE PKTABLE SET ptest1=1 WHERE ptest1=2;
  39
  40 -- Check FKTABLE for update of matched row
  41 SELECT * FROM FKTABLE;
  42
  43 DROP TABLE FKTABLE;
  44 DROP TABLE PKTABLE;
  45
  46 --
  47 -- check set NULL and table constraint on multiple columns
  48 --
  49 CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 text, PRIMARY KEY(ptest1, ptest2) );
  50 CREATE TABLE FKTABLE ( ftest1 int, ftest2 int, ftest3 int, CONSTRAINT constrname FOREIGN KEY(ftest1, ftest2)
  51                        REFERENCES PKTABLE MATCH FULL ON DELETE SET NULL ON UPDATE SET NULL);
  52
  53 -- Test comments
  54 COMMENT ON CONSTRAINT constrname_wrong ON FKTABLE IS 'fk constraint comment';
  55 COMMENT ON CONSTRAINT constrname ON FKTABLE IS 'fk constraint comment';
  56 COMMENT ON CONSTRAINT constrname ON FKTABLE IS NULL;
  57
  58 -- Insert test data into PKTABLE
  59 INSERT INTO PKTABLE VALUES (1, 2, 'Test1');
  60 INSERT INTO PKTABLE VALUES (1, 3, 'Test1-2');
  61 INSERT INTO PKTABLE VALUES (2, 4, 'Test2');
  62 INSERT INTO PKTABLE VALUES (3, 6, 'Test3');
  63 INSERT INTO PKTABLE VALUES (4, 8, 'Test4');
  64 INSERT INTO PKTABLE VALUES (5, 10, 'Test5');
  65
  66 -- Insert successful rows into FK TABLE
  67 INSERT INTO FKTABLE VALUES (1, 2, 4);
  68 INSERT INTO FKTABLE VALUES (1, 3, 5);
  69 INSERT INTO FKTABLE VALUES (2, 4, 8);
  70 INSERT INTO FKTABLE VALUES (3, 6, 12);
  71 INSERT INTO FKTABLE VALUES (NULL, NULL, 0);
  72
  73 -- Insert failed rows into FK TABLE
  74 INSERT INTO FKTABLE VALUES (100, 2, 4);
  75 INSERT INTO FKTABLE VALUES (2, 2, 4);
  76 INSERT INTO FKTABLE VALUES (NULL, 2, 4);
  77 INSERT INTO FKTABLE VALUES (1, NULL, 4);
  78
  79 -- Check FKTABLE
  80 SELECT * FROM FKTABLE;
  81
  82 -- Delete a row from PK TABLE
  83 DELETE FROM PKTABLE WHERE ptest1=1 and ptest2=2;
  84
  85 -- Check FKTABLE for removal of matched row
  86 SELECT * FROM FKTABLE;
  87
  88 -- Delete another row from PK TABLE
  89 DELETE FROM PKTABLE WHERE ptest1=5 and ptest2=10;
  90
  91 -- Check FKTABLE (should be no change)
  92 SELECT * FROM FKTABLE;
  93
  94 -- Update a row from PK TABLE
  95 UPDATE PKTABLE SET ptest1=1 WHERE ptest1=2;
  96
  97 -- Check FKTABLE for update of matched row
  98 SELECT * FROM FKTABLE;
  99
 100 -- Try altering the column type where foreign keys are involved
 101 ALTER TABLE PKTABLE ALTER COLUMN ptest1 TYPE bigint;
 102 ALTER TABLE FKTABLE ALTER COLUMN ftest1 TYPE bigint;
 103 SELECT * FROM PKTABLE;
 104 SELECT * FROM FKTABLE;
 105
 106 DROP TABLE PKTABLE CASCADE;
 107 DROP TABLE FKTABLE;
 108
 109 --
 110 -- check set default and table constraint on multiple columns
 111 --
 112 CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 text, PRIMARY KEY(ptest1, ptest2) );
 113 CREATE TABLE FKTABLE ( ftest1 int DEFAULT -1, ftest2 int DEFAULT -2, ftest3 int, CONSTRAINT constrname2 FOREIGN KEY(ftest1, ftest2)
 114                        REFERENCES PKTABLE MATCH FULL ON DELETE SET DEFAULT ON UPDATE SET DEFAULT);
 115
 116 -- Insert a value in PKTABLE for default
 117 INSERT INTO PKTABLE VALUES (-1, -2, 'The Default!');
 118
 119 -- Insert test data into PKTABLE
 120 INSERT INTO PKTABLE VALUES (1, 2, 'Test1');
 121 INSERT INTO PKTABLE VALUES (1, 3, 'Test1-2');
 122 INSERT INTO PKTABLE VALUES (2, 4, 'Test2');
 123 INSERT INTO PKTABLE VALUES (3, 6, 'Test3');
 124 INSERT INTO PKTABLE VALUES (4, 8, 'Test4');
 125 INSERT INTO PKTABLE VALUES (5, 10, 'Test5');
 126
 127 -- Insert successful rows into FK TABLE
 128 INSERT INTO FKTABLE VALUES (1, 2, 4);
 129 INSERT INTO FKTABLE VALUES (1, 3, 5);
 130 INSERT INTO FKTABLE VALUES (2, 4, 8);
 131 INSERT INTO FKTABLE VALUES (3, 6, 12);
 132 INSERT INTO FKTABLE VALUES (NULL, NULL, 0);
 133
 134 -- Insert failed rows into FK TABLE
 135 INSERT INTO FKTABLE VALUES (100, 2, 4);
 136 INSERT INTO FKTABLE VALUES (2, 2, 4);
 137 INSERT INTO FKTABLE VALUES (NULL, 2, 4);
 138 INSERT INTO FKTABLE VALUES (1, NULL, 4);
 139
 140 -- Check FKTABLE
 141 SELECT * FROM FKTABLE;
 142
 143 -- Delete a row from PK TABLE
 144 DELETE FROM PKTABLE WHERE ptest1=1 and ptest2=2;
 145
 146 -- Check FKTABLE to check for removal
 147 SELECT * FROM FKTABLE;
 148
 149 -- Delete another row from PK TABLE
 150 DELETE FROM PKTABLE WHERE ptest1=5 and ptest2=10;
 151
 152 -- Check FKTABLE (should be no change)
 153 SELECT * FROM FKTABLE;
 154
 155 -- Update a row from PK TABLE
 156 UPDATE PKTABLE SET ptest1=1 WHERE ptest1=2;
 157
 158 -- Check FKTABLE for update of matched row
 159 SELECT * FROM FKTABLE;
 160
 161 -- this should fail for lack of CASCADE
 162 DROP TABLE PKTABLE;
 163 DROP TABLE PKTABLE CASCADE;
 164 DROP TABLE FKTABLE;
 165
 166
 167 --
 168 -- First test, check with no on delete or on update
 169 --
 170 CREATE TABLE PKTABLE ( ptest1 int PRIMARY KEY, ptest2 text );
 171 CREATE TABLE FKTABLE ( ftest1 int REFERENCES PKTABLE MATCH FULL, ftest2 int );
 172
 173 -- Insert test data into PKTABLE
 174 INSERT INTO PKTABLE VALUES (1, 'Test1');
 175 INSERT INTO PKTABLE VALUES (2, 'Test2');
 176 INSERT INTO PKTABLE VALUES (3, 'Test3');
 177 INSERT INTO PKTABLE VALUES (4, 'Test4');
 178 INSERT INTO PKTABLE VALUES (5, 'Test5');
 179
 180 -- Insert successful rows into FK TABLE
 181 INSERT INTO FKTABLE VALUES (1, 2);
 182 INSERT INTO FKTABLE VALUES (2, 3);
 183 INSERT INTO FKTABLE VALUES (3, 4);
 184 INSERT INTO FKTABLE VALUES (NULL, 1);
 185
 186 -- Insert a failed row into FK TABLE
 187 INSERT INTO FKTABLE VALUES (100, 2);
 188
 189 -- Check FKTABLE
 190 SELECT * FROM FKTABLE;
 191
 192 -- Check PKTABLE
 193 SELECT * FROM PKTABLE;
 194
 195 -- Delete a row from PK TABLE (should fail)
 196 DELETE FROM PKTABLE WHERE ptest1=1;
 197
 198 -- Delete a row from PK TABLE (should succeed)
 199 DELETE FROM PKTABLE WHERE ptest1=5;
 200
 201 -- Check PKTABLE for deletes
 202 SELECT * FROM PKTABLE;
 203
 204 -- Update a row from PK TABLE (should fail)
 205 UPDATE PKTABLE SET ptest1=0 WHERE ptest1=2;
 206
 207 -- Update a row from PK TABLE (should succeed)
 208 UPDATE PKTABLE SET ptest1=0 WHERE ptest1=4;
 209
 210 -- Check PKTABLE for updates
 211 SELECT * FROM PKTABLE;
 212
 213 DROP TABLE FKTABLE;
 214 DROP TABLE PKTABLE;
 215
 216
 217 -- MATCH unspecified
 218
 219 -- Base test restricting update/delete
 220 CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2, ptest3) );
 221 CREATE TABLE FKTABLE ( ftest1 int, ftest2 int, ftest3 int, ftest4 int,  CONSTRAINT constrname3
 222                         FOREIGN KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE);
 223
 224 -- Insert Primary Key values
 225 INSERT INTO PKTABLE VALUES (1, 2, 3, 'test1');
 226 INSERT INTO PKTABLE VALUES (1, 3, 3, 'test2');
 227 INSERT INTO PKTABLE VALUES (2, 3, 4, 'test3');
 228 INSERT INTO PKTABLE VALUES (2, 4, 5, 'test4');
 229
 230 -- Insert Foreign Key values
 231 INSERT INTO FKTABLE VALUES (1, 2, 3, 1);
 232 INSERT INTO FKTABLE VALUES (NULL, 2, 3, 2);
 233 INSERT INTO FKTABLE VALUES (2, NULL, 3, 3);
 234 INSERT INTO FKTABLE VALUES (NULL, 2, 7, 4);
 235 INSERT INTO FKTABLE VALUES (NULL, 3, 4, 5);
 236
 237 -- Insert a failed values
 238 INSERT INTO FKTABLE VALUES (1, 2, 7, 6);
 239
 240 -- Show FKTABLE
 241 SELECT * from FKTABLE;
 242
 243 -- Try to update something that should fail
 244 UPDATE PKTABLE set ptest2=5 where ptest2=2;
 245
 246 -- Try to update something that should succeed
 247 UPDATE PKTABLE set ptest1=1 WHERE ptest2=3;
 248
 249 -- Try to delete something that should fail
 250 DELETE FROM PKTABLE where ptest1=1 and ptest2=2 and ptest3=3;
 251
 252 -- Try to delete something that should work
 253 DELETE FROM PKTABLE where ptest1=2;
 254
 255 -- Show PKTABLE and FKTABLE
 256 SELECT * from PKTABLE;
 257
 258 SELECT * from FKTABLE;
 259
 260 DROP TABLE FKTABLE;
 261 DROP TABLE PKTABLE;
 262
 263 -- cascade update/delete
 264 CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2, ptest3) );
 265 CREATE TABLE FKTABLE ( ftest1 int, ftest2 int, ftest3 int, ftest4 int,  CONSTRAINT constrname3
 266                         FOREIGN KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE
 267                         ON DELETE CASCADE ON UPDATE CASCADE);
 268
 269 -- Insert Primary Key values
 270 INSERT INTO PKTABLE VALUES (1, 2, 3, 'test1');
 271 INSERT INTO PKTABLE VALUES (1, 3, 3, 'test2');
 272 INSERT INTO PKTABLE VALUES (2, 3, 4, 'test3');
 273 INSERT INTO PKTABLE VALUES (2, 4, 5, 'test4');
 274
 275 -- Insert Foreign Key values
 276 INSERT INTO FKTABLE VALUES (1, 2, 3, 1);
 277 INSERT INTO FKTABLE VALUES (NULL, 2, 3, 2);
 278 INSERT INTO FKTABLE VALUES (2, NULL, 3, 3);
 279 INSERT INTO FKTABLE VALUES (NULL, 2, 7, 4);
 280 INSERT INTO FKTABLE VALUES (NULL, 3, 4, 5);
 281
 282 -- Insert a failed values
 283 INSERT INTO FKTABLE VALUES (1, 2, 7, 6);
 284
 285 -- Show FKTABLE
 286 SELECT * from FKTABLE;
 287
 288 -- Try to update something that will cascade
 289 UPDATE PKTABLE set ptest2=5 where ptest2=2;
 290
 291 -- Try to update something that should not cascade
 292 UPDATE PKTABLE set ptest1=1 WHERE ptest2=3;
 293
 294 -- Show PKTABLE and FKTABLE
 295 SELECT * from PKTABLE;
 296 SELECT * from FKTABLE;
 297
 298 -- Try to delete something that should cascade
 299 DELETE FROM PKTABLE where ptest1=1 and ptest2=5 and ptest3=3;
 300
 301 -- Show PKTABLE and FKTABLE
 302 SELECT * from PKTABLE;
 303 SELECT * from FKTABLE;
 304
 305 -- Try to delete something that should not have a cascade
 306 DELETE FROM PKTABLE where ptest1=2;
 307
 308 -- Show PKTABLE and FKTABLE
 309 SELECT * from PKTABLE;
 310 SELECT * from FKTABLE;
 311
 312 DROP TABLE FKTABLE;
 313 DROP TABLE PKTABLE;
 314
 315 -- set null update / set default delete
 316 CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2, ptest3) );
 317 CREATE TABLE FKTABLE ( ftest1 int DEFAULT 0, ftest2 int, ftest3 int, ftest4 int,  CONSTRAINT constrname3
 318                         FOREIGN KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE
 319                         ON DELETE SET DEFAULT ON UPDATE SET NULL);
 320
 321 -- Insert Primary Key values
 322 INSERT INTO PKTABLE VALUES (1, 2, 3, 'test1');
 323 INSERT INTO PKTABLE VALUES (1, 3, 3, 'test2');
 324 INSERT INTO PKTABLE VALUES (2, 3, 4, 'test3');
 325 INSERT INTO PKTABLE VALUES (2, 4, 5, 'test4');
 326
 327 -- Insert Foreign Key values
 328 INSERT INTO FKTABLE VALUES (1, 2, 3, 1);
 329 INSERT INTO FKTABLE VALUES (2, 3, 4, 1);
 330 INSERT INTO FKTABLE VALUES (NULL, 2, 3, 2);
 331 INSERT INTO FKTABLE VALUES (2, NULL, 3, 3);
 332 INSERT INTO FKTABLE VALUES (NULL, 2, 7, 4);
 333 INSERT INTO FKTABLE VALUES (NULL, 3, 4, 5);
 334
 335 -- Insert a failed values
 336 INSERT INTO FKTABLE VALUES (1, 2, 7, 6);
 337
 338 -- Show FKTABLE
 339 SELECT * from FKTABLE;
 340
 341 -- Try to update something that will set null
 342 UPDATE PKTABLE set ptest2=5 where ptest2=2;
 343
 344 -- Try to update something that should not set null
 345 UPDATE PKTABLE set ptest2=2 WHERE ptest2=3 and ptest1=1;
 346
 347 -- Show PKTABLE and FKTABLE
 348 SELECT * from PKTABLE;
 349 SELECT * from FKTABLE;
 350
 351 -- Try to delete something that should set default
 352 DELETE FROM PKTABLE where ptest1=2 and ptest2=3 and ptest3=4;
 353
 354 -- Show PKTABLE and FKTABLE
 355 SELECT * from PKTABLE;
 356 SELECT * from FKTABLE;
 357
 358 -- Try to delete something that should not set default
 359 DELETE FROM PKTABLE where ptest2=5;
 360
 361 -- Show PKTABLE and FKTABLE
 362 SELECT * from PKTABLE;
 363 SELECT * from FKTABLE;
 364
 365 DROP TABLE FKTABLE;
 366 DROP TABLE PKTABLE;
 367
 368 -- set default update / set null delete
 369 CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2, ptest3) );
 370 CREATE TABLE FKTABLE ( ftest1 int DEFAULT 0, ftest2 int DEFAULT -1, ftest3 int, ftest4 int,  CONSTRAINT constrname3
 371                         FOREIGN KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE
 372                         ON DELETE SET NULL ON UPDATE SET DEFAULT);
 373
 374 -- Insert Primary Key values
 375 INSERT INTO PKTABLE VALUES (1, 2, 3, 'test1');
 376 INSERT INTO PKTABLE VALUES (1, 3, 3, 'test2');
 377 INSERT INTO PKTABLE VALUES (2, 3, 4, 'test3');
 378 INSERT INTO PKTABLE VALUES (2, 4, 5, 'test4');
 379 INSERT INTO PKTABLE VALUES (2, -1, 5, 'test5');
 380
 381 -- Insert Foreign Key values
 382 INSERT INTO FKTABLE VALUES (1, 2, 3, 1);
 383 INSERT INTO FKTABLE VALUES (2, 3, 4, 1);
 384 INSERT INTO FKTABLE VALUES (2, 4, 5, 1);
 385 INSERT INTO FKTABLE VALUES (NULL, 2, 3, 2);
 386 INSERT INTO FKTABLE VALUES (2, NULL, 3, 3);
 387 INSERT INTO FKTABLE VALUES (NULL, 2, 7, 4);
 388 INSERT INTO FKTABLE VALUES (NULL, 3, 4, 5);
 389
 390 -- Insert a failed values
 391 INSERT INTO FKTABLE VALUES (1, 2, 7, 6);
 392
 393 -- Show FKTABLE
 394 SELECT * from FKTABLE;
 395
 396 -- Try to update something that will fail
 397 UPDATE PKTABLE set ptest2=5 where ptest2=2;
 398
 399 -- Try to update something that will set default
 400 UPDATE PKTABLE set ptest1=0, ptest2=5, ptest3=10 where ptest2=2;
 401 UPDATE PKTABLE set ptest2=10 where ptest2=4;
 402
 403 -- Try to update something that should not set default
 404 UPDATE PKTABLE set ptest2=2 WHERE ptest2=3 and ptest1=1;
 405
 406 -- Show PKTABLE and FKTABLE
 407 SELECT * from PKTABLE;
 408 SELECT * from FKTABLE;
 409
 410 -- Try to delete something that should set null
 411 DELETE FROM PKTABLE where ptest1=2 and ptest2=3 and ptest3=4;
 412
 413 -- Show PKTABLE and FKTABLE
 414 SELECT * from PKTABLE;
 415 SELECT * from FKTABLE;
 416
 417 -- Try to delete something that should not set null
 418 DELETE FROM PKTABLE where ptest2=5;
 419
 420 -- Show PKTABLE and FKTABLE
 421 SELECT * from PKTABLE;
 422 SELECT * from FKTABLE;
 423
 424 DROP TABLE FKTABLE;
 425 DROP TABLE PKTABLE;
 426
 427 CREATE TABLE PKTABLE (ptest1 int PRIMARY KEY);
 428 CREATE TABLE FKTABLE_FAIL1 ( ftest1 int, CONSTRAINT fkfail1 FOREIGN KEY (ftest2) REFERENCES PKTABLE);
 429 CREATE TABLE FKTABLE_FAIL2 ( ftest1 int, CONSTRAINT fkfail1 FOREIGN KEY (ftest1) REFERENCES PKTABLE(ptest2));
 430
 431 DROP TABLE FKTABLE_FAIL1;
 432 DROP TABLE FKTABLE_FAIL2;
 433 DROP TABLE PKTABLE;
 434
 435 -- Test for referencing column number smaller than referenced constraint
 436 CREATE TABLE PKTABLE (ptest1 int, ptest2 int, UNIQUE(ptest1, ptest2));
 437 CREATE TABLE FKTABLE_FAIL1 (ftest1 int REFERENCES pktable(ptest1));
 438
 439 DROP TABLE FKTABLE_FAIL1;
 440 DROP TABLE PKTABLE;
 441
 442 --
 443 -- Tests for mismatched types
 444 --
 445 -- Basic one column, two table setup
 446 CREATE TABLE PKTABLE (ptest1 int PRIMARY KEY);
 447 INSERT INTO PKTABLE VALUES(42);
 448 -- This next should fail, because int=inet does not exist
 449 CREATE TABLE FKTABLE (ftest1 inet REFERENCES pktable);
 450 -- This should also fail for the same reason, but here we
 451 -- give the column name
 452 CREATE TABLE FKTABLE (ftest1 inet REFERENCES pktable(ptest1));
 453 -- This should succeed, even though they are different types,
 454 -- because int=int8 exists and is a member of the integer opfamily
 455 CREATE TABLE FKTABLE (ftest1 int8 REFERENCES pktable);
 456 -- Check it actually works
 457 INSERT INTO FKTABLE VALUES(42);         -- should succeed
 458 INSERT INTO FKTABLE VALUES(43);         -- should fail
 459 UPDATE FKTABLE SET ftest1 = ftest1;     -- should succeed
 460 UPDATE FKTABLE SET ftest1 = ftest1 + 1; -- should fail
 461 DROP TABLE FKTABLE;
 462 -- This should fail, because we'd have to cast numeric to int which is
 463 -- not an implicit coercion (or use numeric=numeric, but that's not part
 464 -- of the integer opfamily)
 465 CREATE TABLE FKTABLE (ftest1 numeric REFERENCES pktable);
 466 DROP TABLE PKTABLE;
 467 -- On the other hand, this should work because int implicitly promotes to
 468 -- numeric, and we allow promotion on the FK side
 469 CREATE TABLE PKTABLE (ptest1 numeric PRIMARY KEY);
 470 INSERT INTO PKTABLE VALUES(42);
 471 CREATE TABLE FKTABLE (ftest1 int REFERENCES pktable);
 472 -- Check it actually works
 473 INSERT INTO FKTABLE VALUES(42);         -- should succeed
 474 INSERT INTO FKTABLE VALUES(43);         -- should fail
 475 UPDATE FKTABLE SET ftest1 = ftest1;     -- should succeed
 476 UPDATE FKTABLE SET ftest1 = ftest1 + 1; -- should fail
 477 DROP TABLE FKTABLE;
 478 DROP TABLE PKTABLE;
 479
 480 -- Two columns, two tables
 481 CREATE TABLE PKTABLE (ptest1 int, ptest2 inet, PRIMARY KEY(ptest1, ptest2));
 482 -- This should fail, because we just chose really odd types
 483 CREATE TABLE FKTABLE (ftest1 cidr, ftest2 timestamp, FOREIGN KEY(ftest1, ftest2) REFERENCES pktable);
 484 -- Again, so should this...
 485 CREATE TABLE FKTABLE (ftest1 cidr, ftest2 timestamp, FOREIGN KEY(ftest1, ftest2) REFERENCES pktable(ptest1, ptest2));
 486 -- This fails because we mixed up the column ordering
 487 CREATE TABLE FKTABLE (ftest1 int, ftest2 inet, FOREIGN KEY(ftest2, ftest1) REFERENCES pktable);
 488 -- As does this...
 489 CREATE TABLE FKTABLE (ftest1 int, ftest2 inet, FOREIGN KEY(ftest2, ftest1) REFERENCES pktable(ptest1, ptest2));
 490 -- And again..
 491 CREATE TABLE FKTABLE (ftest1 int, ftest2 inet, FOREIGN KEY(ftest1, ftest2) REFERENCES pktable(ptest2, ptest1));
 492 -- This works...
 493 CREATE TABLE FKTABLE (ftest1 int, ftest2 inet, FOREIGN KEY(ftest2, ftest1) REFERENCES pktable(ptest2, ptest1));
 494 DROP TABLE FKTABLE;
 495 -- As does this
 496 CREATE TABLE FKTABLE (ftest1 int, ftest2 inet, FOREIGN KEY(ftest1, ftest2) REFERENCES pktable(ptest1, ptest2));
 497 DROP TABLE FKTABLE;
 498 DROP TABLE PKTABLE;
 499
 500 -- Two columns, same table
 501 -- Make sure this still works...
 502 CREATE TABLE PKTABLE (ptest1 int, ptest2 inet, ptest3 int, ptest4 inet, PRIMARY KEY(ptest1, ptest2), FOREIGN KEY(ptest3,
 503 ptest4) REFERENCES pktable(ptest1, ptest2));
 504 DROP TABLE PKTABLE;
 505 -- And this,
 506 CREATE TABLE PKTABLE (ptest1 int, ptest2 inet, ptest3 int, ptest4 inet, PRIMARY KEY(ptest1, ptest2), FOREIGN KEY(ptest3,
 507 ptest4) REFERENCES pktable);
 508 DROP TABLE PKTABLE;
 509 -- This shouldn't (mixed up columns)
 510 CREATE TABLE PKTABLE (ptest1 int, ptest2 inet, ptest3 int, ptest4 inet, PRIMARY KEY(ptest1, ptest2), FOREIGN KEY(ptest3,
 511 ptest4) REFERENCES pktable(ptest2, ptest1));
 512 -- Nor should this... (same reason, we have 4,3 referencing 1,2 which mismatches types
 513 CREATE TABLE PKTABLE (ptest1 int, ptest2 inet, ptest3 int, ptest4 inet, PRIMARY KEY(ptest1, ptest2), FOREIGN KEY(ptest4,
 514 ptest3) REFERENCES pktable(ptest1, ptest2));
 515 -- Not this one either... Same as the last one except we didn't defined the columns being referenced.
 516 CREATE TABLE PKTABLE (ptest1 int, ptest2 inet, ptest3 int, ptest4 inet, PRIMARY KEY(ptest1, ptest2), FOREIGN KEY(ptest4,
 517 ptest3) REFERENCES pktable);
 518
 519 --
 520 -- Now some cases with inheritance
 521 -- Basic 2 table case: 1 column of matching types.
 522 create table pktable_base (base1 int not null);
 523 create table pktable (ptest1 int, primary key(base1), unique(base1, ptest1)) inherits (pktable_base);
 524 create table fktable (ftest1 int references pktable(base1));
 525 -- now some ins, upd, del
 526 insert into pktable(base1) values (1);
 527 insert into pktable(base1) values (2);
 528 --  let's insert a non-existant fktable value
 529 insert into fktable(ftest1) values (3);
 530 --  let's make a valid row for that
 531 insert into pktable(base1) values (3);
 532 insert into fktable(ftest1) values (3);
 533 -- let's try removing a row that should fail from pktable
 534 delete from pktable where base1>2;
 535 -- okay, let's try updating all of the base1 values to *4
 536 -- which should fail.
 537 update pktable set base1=base1*4;
 538 -- okay, let's try an update that should work.
 539 update pktable set base1=base1*4 where base1<3;
 540 -- and a delete that should work
 541 delete from pktable where base1>3;
 542 -- cleanup
 543 drop table fktable;
 544 delete from pktable;
 545
 546 -- Now 2 columns 2 tables, matching types
 547 create table fktable (ftest1 int, ftest2 int, foreign key(ftest1, ftest2) references pktable(base1, ptest1));
 548 -- now some ins, upd, del
 549 insert into pktable(base1, ptest1) values (1, 1);
 550 insert into pktable(base1, ptest1) values (2, 2);
 551 --  let's insert a non-existant fktable value
 552 insert into fktable(ftest1, ftest2) values (3, 1);
 553 --  let's make a valid row for that
 554 insert into pktable(base1,ptest1) values (3, 1);
 555 insert into fktable(ftest1, ftest2) values (3, 1);
 556 -- let's try removing a row that should fail from pktable
 557 delete from pktable where base1>2;
 558 -- okay, let's try updating all of the base1 values to *4
 559 -- which should fail.
 560 update pktable set base1=base1*4;
 561 -- okay, let's try an update that should work.
 562 update pktable set base1=base1*4 where base1<3;
 563 -- and a delete that should work
 564 delete from pktable where base1>3;
 565 -- cleanup
 566 drop table fktable;
 567 drop table pktable;
 568 drop table pktable_base;
 569
 570 -- Now we'll do one all in 1 table with 2 columns of matching types
 571 create table pktable_base(base1 int not null, base2 int);
 572 create table pktable(ptest1 int, ptest2 int, primary key(base1, ptest1), foreign key(base2, ptest2) references
 573                                              pktable(base1, ptest1)) inherits (pktable_base);
 574 insert into pktable (base1, ptest1, base2, ptest2) values (1, 1, 1, 1);
 575 insert into pktable (base1, ptest1, base2, ptest2) values (2, 1, 1, 1);
 576 insert into pktable (base1, ptest1, base2, ptest2) values (2, 2, 2, 1);
 577 insert into pktable (base1, ptest1, base2, ptest2) values (1, 3, 2, 2);
 578 -- fails (3,2) isn't in base1, ptest1
 579 insert into pktable (base1, ptest1, base2, ptest2) values (2, 3, 3, 2);
 580 -- fails (2,2) is being referenced
 581 delete from pktable where base1=2;
 582 -- fails (1,1) is being referenced (twice)
 583 update pktable set base1=3 where base1=1;
 584 -- this sequence of two deletes will work, since after the first there will be no (2,*) references
 585 delete from pktable where base2=2;
 586 delete from pktable where base1=2;
 587 drop table pktable;
 588 drop table pktable_base;
 589
 590 -- 2 columns (2 tables), mismatched types
 591 create table pktable_base(base1 int not null);
 592 create table pktable(ptest1 inet, primary key(base1, ptest1)) inherits (pktable_base);
 593 -- just generally bad types (with and without column references on the referenced table)
 594 create table fktable(ftest1 cidr, ftest2 int[], foreign key (ftest1, ftest2) references pktable);
 595 create table fktable(ftest1 cidr, ftest2 int[], foreign key (ftest1, ftest2) references pktable(base1, ptest1));
 596 -- let's mix up which columns reference which
 597 create table fktable(ftest1 int, ftest2 inet, foreign key(ftest2, ftest1) references pktable);
 598 create table fktable(ftest1 int, ftest2 inet, foreign key(ftest2, ftest1) references pktable(base1, ptest1));
 599 create table fktable(ftest1 int, ftest2 inet, foreign key(ftest1, ftest2) references pktable(ptest1, base1));
 600 drop table pktable;
 601 drop table pktable_base;
 602
 603 -- 2 columns (1 table), mismatched types
 604 create table pktable_base(base1 int not null, base2 int);
 605 create table pktable(ptest1 inet, ptest2 inet[], primary key(base1, ptest1), foreign key(base2, ptest2) references
 606                                              pktable(base1, ptest1)) inherits (pktable_base);
 607 create table pktable(ptest1 inet, ptest2 inet, primary key(base1, ptest1), foreign key(base2, ptest2) references
 608                                              pktable(ptest1, base1)) inherits (pktable_base);
 609 create table pktable(ptest1 inet, ptest2 inet, primary key(base1, ptest1), foreign key(ptest2, base2) references
 610                                              pktable(base1, ptest1)) inherits (pktable_base);
 611 create table pktable(ptest1 inet, ptest2 inet, primary key(base1, ptest1), foreign key(ptest2, base2) references
 612                                              pktable(base1, ptest1)) inherits (pktable_base);
 613 drop table pktable;
 614 drop table pktable_base;
 615
 616 --
 617 -- Deferrable constraints
 618 --              (right now, only FOREIGN KEY constraints can be deferred)
 619 --
 620
 621 -- deferrable, explicitly deferred
 622 CREATE TABLE pktable (
 623         id              INT4 PRIMARY KEY,
 624         other   INT4
 625 );
 626
 627 CREATE TABLE fktable (
 628         id              INT4 PRIMARY KEY,
 629         fk              INT4 REFERENCES pktable DEFERRABLE
 630 );
 631
 632 -- default to immediate: should fail
 633 INSERT INTO fktable VALUES (5, 10);
 634
 635 -- explicitly defer the constraint
 636 BEGIN;
 637
 638 SET CONSTRAINTS ALL DEFERRED;
 639
 640 INSERT INTO fktable VALUES (10, 15);
 641 INSERT INTO pktable VALUES (15, 0); -- make the FK insert valid
 642
 643 COMMIT;
 644
 645 DROP TABLE fktable, pktable;
 646
 647 -- deferrable, initially deferred
 648 CREATE TABLE pktable (
 649         id              INT4 PRIMARY KEY,
 650         other   INT4
 651 );
 652
 653 CREATE TABLE fktable (
 654         id              INT4 PRIMARY KEY,
 655         fk              INT4 REFERENCES pktable DEFERRABLE INITIALLY DEFERRED
 656 );
 657
 658 -- default to deferred, should succeed
 659 BEGIN;
 660
 661 INSERT INTO fktable VALUES (100, 200);
 662 INSERT INTO pktable VALUES (200, 500); -- make the FK insert valid
 663
 664 COMMIT;
 665
 666 -- default to deferred, explicitly make immediate
 667 BEGIN;
 668
 669 SET CONSTRAINTS ALL IMMEDIATE;
 670
 671 -- should fail
 672 INSERT INTO fktable VALUES (500, 1000);
 673
 674 COMMIT;
 675
 676 DROP TABLE fktable, pktable;
 677
 678 -- tricky behavior: according to SQL99, if a deferred constraint is set
 679 -- to 'immediate' mode, it should be checked for validity *immediately*,
 680 -- not when the current transaction commits (i.e. the mode change applies
 681 -- retroactively)
 682 CREATE TABLE pktable (
 683         id              INT4 PRIMARY KEY,
 684         other   INT4
 685 );
 686
 687 CREATE TABLE fktable (
 688         id              INT4 PRIMARY KEY,
 689         fk              INT4 REFERENCES pktable DEFERRABLE
 690 );
 691
 692 BEGIN;
 693
 694 SET CONSTRAINTS ALL DEFERRED;
 695
 696 -- should succeed, for now
 697 INSERT INTO fktable VALUES (1000, 2000);
 698
 699 -- should cause transaction abort, due to preceding error
 700 SET CONSTRAINTS ALL IMMEDIATE;
 701
 702 INSERT INTO pktable VALUES (2000, 3); -- too late
 703
 704 COMMIT;
 705
 706 DROP TABLE fktable, pktable;
 707
 708 -- deferrable, initially deferred
 709 CREATE TABLE pktable (
 710         id              INT4 PRIMARY KEY,
 711         other   INT4
 712 );
 713
 714 CREATE TABLE fktable (
 715         id              INT4 PRIMARY KEY,
 716         fk              INT4 REFERENCES pktable DEFERRABLE INITIALLY DEFERRED
 717 );
 718
 719 BEGIN;
 720
 721 -- no error here
 722 INSERT INTO fktable VALUES (100, 200);
 723
 724 -- error here on commit
 725 COMMIT;
 726
 727 DROP TABLE pktable, fktable;
 728
 729 -- test notice about expensive referential integrity checks,
 730 -- where the index cannot be used because of type incompatibilities.
 731
 732 CREATE TEMP TABLE pktable (
 733         id1     INT4 PRIMARY KEY,
 734         id2     VARCHAR(4) UNIQUE,
 735         id3     REAL UNIQUE,
 736         UNIQUE(id1, id2, id3)
 737 );
 738
 739 CREATE TEMP TABLE fktable (
 740         x1      INT4 REFERENCES pktable(id1),
 741         x2      VARCHAR(4) REFERENCES pktable(id2),
 742         x3      REAL REFERENCES pktable(id3),
 743         x4      TEXT,
 744         x5      INT2
 745 );
 746
 747 -- check individual constraints with alter table.
 748
 749 -- should fail
 750
 751 -- varchar does not promote to real
 752 ALTER TABLE fktable ADD CONSTRAINT fk_2_3
 753 FOREIGN KEY (x2) REFERENCES pktable(id3);
 754
 755 -- nor to int4
 756 ALTER TABLE fktable ADD CONSTRAINT fk_2_1
 757 FOREIGN KEY (x2) REFERENCES pktable(id1);
 758
 759 -- real does not promote to int4
 760 ALTER TABLE fktable ADD CONSTRAINT fk_3_1
 761 FOREIGN KEY (x3) REFERENCES pktable(id1);
 762
 763 -- int4 does not promote to text
 764 ALTER TABLE fktable ADD CONSTRAINT fk_1_2
 765 FOREIGN KEY (x1) REFERENCES pktable(id2);
 766
 767 -- should succeed
 768
 769 -- int4 promotes to real
 770 ALTER TABLE fktable ADD CONSTRAINT fk_1_3
 771 FOREIGN KEY (x1) REFERENCES pktable(id3);
 772
 773 -- text is compatible with varchar
 774 ALTER TABLE fktable ADD CONSTRAINT fk_4_2
 775 FOREIGN KEY (x4) REFERENCES pktable(id2);
 776
 777 -- int2 is part of integer opfamily as of 8.0
 778 ALTER TABLE fktable ADD CONSTRAINT fk_5_1
 779 FOREIGN KEY (x5) REFERENCES pktable(id1);
 780
 781 -- check multikey cases, especially out-of-order column lists
 782
 783 -- these should work
 784
 785 ALTER TABLE fktable ADD CONSTRAINT fk_123_123
 786 FOREIGN KEY (x1,x2,x3) REFERENCES pktable(id1,id2,id3);
 787
 788 ALTER TABLE fktable ADD CONSTRAINT fk_213_213
 789 FOREIGN KEY (x2,x1,x3) REFERENCES pktable(id2,id1,id3);
 790
 791 ALTER TABLE fktable ADD CONSTRAINT fk_253_213
 792 FOREIGN KEY (x2,x5,x3) REFERENCES pktable(id2,id1,id3);
 793
 794 -- these should fail
 795
 796 ALTER TABLE fktable ADD CONSTRAINT fk_123_231
 797 FOREIGN KEY (x1,x2,x3) REFERENCES pktable(id2,id3,id1);
 798
 799 ALTER TABLE fktable ADD CONSTRAINT fk_241_132
 800 FOREIGN KEY (x2,x4,x1) REFERENCES pktable(id1,id3,id2);
 801
 802 DROP TABLE pktable, fktable;
 803
 804 -- test a tricky case: we can elide firing the FK check trigger during
 805 -- an UPDATE if the UPDATE did not change the foreign key
 806 -- field. However, we can't do this if our transaction was the one that
 807 -- created the updated row and the trigger is deferred, since our UPDATE
 808 -- will have invalidated the original newly-inserted tuple, and therefore
 809 -- cause the on-INSERT RI trigger not to be fired.
 810
 811 CREATE TEMP TABLE pktable (
 812     id int primary key,
 813     other int
 814 );
 815
 816 CREATE TEMP TABLE fktable (
 817     id int primary key,
 818     fk int references pktable deferrable initially deferred
 819 );
 820
 821 INSERT INTO pktable VALUES (5, 10);
 822
 823 BEGIN;
 824
 825 -- doesn't match PK, but no error yet
 826 INSERT INTO fktable VALUES (0, 20);
 827
 828 -- don't change FK
 829 UPDATE fktable SET id = id + 1;
 830
 831 -- should catch error from initial INSERT
 832 COMMIT;
 833
 834 -- check same case when insert is in a different subtransaction than update
 835
 836 BEGIN;
 837
 838 -- doesn't match PK, but no error yet
 839 INSERT INTO fktable VALUES (0, 20);
 840
 841 -- UPDATE will be in a subxact
 842 SAVEPOINT savept1;
 843
 844 -- don't change FK
 845 UPDATE fktable SET id = id + 1;
 846
 847 -- should catch error from initial INSERT
 848 COMMIT;
 849
 850 BEGIN;
 851
 852 -- INSERT will be in a subxact
 853 SAVEPOINT savept1;
 854
 855 -- doesn't match PK, but no error yet
 856 INSERT INTO fktable VALUES (0, 20);
 857
 858 RELEASE SAVEPOINT savept1;
 859
 860 -- don't change FK
 861 UPDATE fktable SET id = id + 1;
 862
 863 -- should catch error from initial INSERT
 864 COMMIT;
 865
 866 BEGIN;
 867
 868 -- doesn't match PK, but no error yet
 869 INSERT INTO fktable VALUES (0, 20);
 870
 871 -- UPDATE will be in a subxact
 872 SAVEPOINT savept1;
 873
 874 -- don't change FK
 875 UPDATE fktable SET id = id + 1;
 876
 877 -- Roll back the UPDATE
 878 ROLLBACK TO savept1;
 879
 880 -- should catch error from initial INSERT
 881 COMMIT;
 882
 883 -- test order of firing of FK triggers when several RI-induced changes need to
 884 -- be made to the same row.  This was broken by subtransaction-related
 885 -- changes in 8.0.
 886
 887 CREATE TEMP TABLE users (
 888   id INT PRIMARY KEY,
 889   name VARCHAR NOT NULL
 890 );
 891
 892 INSERT INTO users VALUES (1, 'Jozko');
 893 INSERT INTO users VALUES (2, 'Ferko');
 894 INSERT INTO users VALUES (3, 'Samko');
 895
 896 CREATE TEMP TABLE tasks (
 897   id INT PRIMARY KEY,
 898   owner INT REFERENCES users ON UPDATE CASCADE ON DELETE SET NULL,
 899   worker INT REFERENCES users ON UPDATE CASCADE ON DELETE SET NULL,
 900   checked_by INT REFERENCES users ON UPDATE CASCADE ON DELETE SET NULL
 901 );
 902
 903 INSERT INTO tasks VALUES (1,1,NULL,NULL);
 904 INSERT INTO tasks VALUES (2,2,2,NULL);
 905 INSERT INTO tasks VALUES (3,3,3,3);
 906
 907 SELECT * FROM tasks;
 908
 909 UPDATE users SET id = 4 WHERE id = 3;
 910
 911 SELECT * FROM tasks;
 912
 913 DELETE FROM users WHERE id = 4;
 914
 915 SELECT * FROM tasks;
 916
 917 -- could fail with only 2 changes to make, if row was already updated
 918 BEGIN;
 919 UPDATE tasks set id=id WHERE id=2;
 920 SELECT * FROM tasks;
 921 DELETE FROM users WHERE id = 2;
 922 SELECT * FROM tasks;
 923 COMMIT;