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