6
-- First test, check and cascade
8
CREATE TABLE PKTABLE ( ptest1 int PRIMARY KEY, ptest2 text );
9
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
10
CREATE TABLE FKTABLE ( ftest1 int REFERENCES PKTABLE MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE, ftest2 int );
11
-- Insert test data into PKTABLE
12
INSERT INTO PKTABLE VALUES (1, 'Test1');
13
INSERT INTO PKTABLE VALUES (2, 'Test2');
14
INSERT INTO PKTABLE VALUES (3, 'Test3');
15
INSERT INTO PKTABLE VALUES (4, 'Test4');
16
INSERT INTO PKTABLE VALUES (5, 'Test5');
17
-- Insert successful rows into FK TABLE
18
INSERT INTO FKTABLE VALUES (1, 2);
19
INSERT INTO FKTABLE VALUES (2, 3);
20
INSERT INTO FKTABLE VALUES (3, 4);
21
INSERT INTO FKTABLE VALUES (NULL, 1);
22
-- Insert a failed row into FK TABLE
23
INSERT INTO FKTABLE VALUES (100, 2);
24
ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_fkey"
25
DETAIL: Key (ftest1)=(100) is not present in table "pktable".
27
SELECT * FROM FKTABLE;
36
-- Delete a row from PK TABLE
37
DELETE FROM PKTABLE WHERE ptest1=1;
38
-- Check FKTABLE for removal of matched row
39
SELECT * FROM FKTABLE;
47
-- Update a row from PK TABLE
48
UPDATE PKTABLE SET ptest1=1 WHERE ptest1=2;
49
-- Check FKTABLE for update of matched row
50
SELECT * FROM FKTABLE;
61
-- check set NULL and table constraint on multiple columns
63
CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 text, PRIMARY KEY(ptest1, ptest2) );
64
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
65
CREATE TABLE FKTABLE ( ftest1 int, ftest2 int, ftest3 int, CONSTRAINT constrname FOREIGN KEY(ftest1, ftest2)
66
REFERENCES PKTABLE MATCH FULL ON DELETE SET NULL ON UPDATE SET NULL);
68
COMMENT ON CONSTRAINT constrname_wrong ON FKTABLE IS 'fk constraint comment';
69
ERROR: constraint "constrname_wrong" for table "fktable" does not exist
70
COMMENT ON CONSTRAINT constrname ON FKTABLE IS 'fk constraint comment';
71
COMMENT ON CONSTRAINT constrname ON FKTABLE IS NULL;
72
-- Insert test data into PKTABLE
73
INSERT INTO PKTABLE VALUES (1, 2, 'Test1');
74
INSERT INTO PKTABLE VALUES (1, 3, 'Test1-2');
75
INSERT INTO PKTABLE VALUES (2, 4, 'Test2');
76
INSERT INTO PKTABLE VALUES (3, 6, 'Test3');
77
INSERT INTO PKTABLE VALUES (4, 8, 'Test4');
78
INSERT INTO PKTABLE VALUES (5, 10, 'Test5');
79
-- Insert successful rows into FK TABLE
80
INSERT INTO FKTABLE VALUES (1, 2, 4);
81
INSERT INTO FKTABLE VALUES (1, 3, 5);
82
INSERT INTO FKTABLE VALUES (2, 4, 8);
83
INSERT INTO FKTABLE VALUES (3, 6, 12);
84
INSERT INTO FKTABLE VALUES (NULL, NULL, 0);
85
-- Insert failed rows into FK TABLE
86
INSERT INTO FKTABLE VALUES (100, 2, 4);
87
ERROR: insert or update on table "fktable" violates foreign key constraint "constrname"
88
DETAIL: Key (ftest1,ftest2)=(100,2) is not present in table "pktable".
89
INSERT INTO FKTABLE VALUES (2, 2, 4);
90
ERROR: insert or update on table "fktable" violates foreign key constraint "constrname"
91
DETAIL: Key (ftest1,ftest2)=(2,2) is not present in table "pktable".
92
INSERT INTO FKTABLE VALUES (NULL, 2, 4);
93
ERROR: insert or update on table "fktable" violates foreign key constraint "constrname"
94
DETAIL: MATCH FULL does not allow mixing of null and nonnull key values.
95
INSERT INTO FKTABLE VALUES (1, NULL, 4);
96
ERROR: insert or update on table "fktable" violates foreign key constraint "constrname"
97
DETAIL: MATCH FULL does not allow mixing of null and nonnull key values.
99
SELECT * FROM FKTABLE;
100
ftest1 | ftest2 | ftest3
101
--------+--------+--------
109
-- Delete a row from PK TABLE
110
DELETE FROM PKTABLE WHERE ptest1=1 and ptest2=2;
111
-- Check FKTABLE for removal of matched row
112
SELECT * FROM FKTABLE;
113
ftest1 | ftest2 | ftest3
114
--------+--------+--------
122
-- Delete another row from PK TABLE
123
DELETE FROM PKTABLE WHERE ptest1=5 and ptest2=10;
124
-- Check FKTABLE (should be no change)
125
SELECT * FROM FKTABLE;
126
ftest1 | ftest2 | ftest3
127
--------+--------+--------
135
-- Update a row from PK TABLE
136
UPDATE PKTABLE SET ptest1=1 WHERE ptest1=2;
137
-- Check FKTABLE for update of matched row
138
SELECT * FROM FKTABLE;
139
ftest1 | ftest2 | ftest3
140
--------+--------+--------
148
-- Try altering the column type where foreign keys are involved
149
ALTER TABLE PKTABLE ALTER COLUMN ptest1 TYPE bigint;
150
ALTER TABLE FKTABLE ALTER COLUMN ftest1 TYPE bigint;
151
SELECT * FROM PKTABLE;
152
ptest1 | ptest2 | ptest3
153
--------+--------+---------
160
SELECT * FROM FKTABLE;
161
ftest1 | ftest2 | ftest3
162
--------+--------+--------
170
DROP TABLE PKTABLE CASCADE;
171
NOTICE: drop cascades to constraint constrname on table fktable
174
-- check set default and table constraint on multiple columns
176
CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 text, PRIMARY KEY(ptest1, ptest2) );
177
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
178
CREATE TABLE FKTABLE ( ftest1 int DEFAULT -1, ftest2 int DEFAULT -2, ftest3 int, CONSTRAINT constrname2 FOREIGN KEY(ftest1, ftest2)
179
REFERENCES PKTABLE MATCH FULL ON DELETE SET DEFAULT ON UPDATE SET DEFAULT);
180
-- Insert a value in PKTABLE for default
181
INSERT INTO PKTABLE VALUES (-1, -2, 'The Default!');
182
-- Insert test data into PKTABLE
183
INSERT INTO PKTABLE VALUES (1, 2, 'Test1');
184
INSERT INTO PKTABLE VALUES (1, 3, 'Test1-2');
185
INSERT INTO PKTABLE VALUES (2, 4, 'Test2');
186
INSERT INTO PKTABLE VALUES (3, 6, 'Test3');
187
INSERT INTO PKTABLE VALUES (4, 8, 'Test4');
188
INSERT INTO PKTABLE VALUES (5, 10, 'Test5');
189
-- Insert successful rows into FK TABLE
190
INSERT INTO FKTABLE VALUES (1, 2, 4);
191
INSERT INTO FKTABLE VALUES (1, 3, 5);
192
INSERT INTO FKTABLE VALUES (2, 4, 8);
193
INSERT INTO FKTABLE VALUES (3, 6, 12);
194
INSERT INTO FKTABLE VALUES (NULL, NULL, 0);
195
-- Insert failed rows into FK TABLE
196
INSERT INTO FKTABLE VALUES (100, 2, 4);
197
ERROR: insert or update on table "fktable" violates foreign key constraint "constrname2"
198
DETAIL: Key (ftest1,ftest2)=(100,2) is not present in table "pktable".
199
INSERT INTO FKTABLE VALUES (2, 2, 4);
200
ERROR: insert or update on table "fktable" violates foreign key constraint "constrname2"
201
DETAIL: Key (ftest1,ftest2)=(2,2) is not present in table "pktable".
202
INSERT INTO FKTABLE VALUES (NULL, 2, 4);
203
ERROR: insert or update on table "fktable" violates foreign key constraint "constrname2"
204
DETAIL: MATCH FULL does not allow mixing of null and nonnull key values.
205
INSERT INTO FKTABLE VALUES (1, NULL, 4);
206
ERROR: insert or update on table "fktable" violates foreign key constraint "constrname2"
207
DETAIL: MATCH FULL does not allow mixing of null and nonnull key values.
209
SELECT * FROM FKTABLE;
210
ftest1 | ftest2 | ftest3
211
--------+--------+--------
219
-- Delete a row from PK TABLE
220
DELETE FROM PKTABLE WHERE ptest1=1 and ptest2=2;
221
-- Check FKTABLE to check for removal
222
SELECT * FROM FKTABLE;
223
ftest1 | ftest2 | ftest3
224
--------+--------+--------
232
-- Delete another row from PK TABLE
233
DELETE FROM PKTABLE WHERE ptest1=5 and ptest2=10;
234
-- Check FKTABLE (should be no change)
235
SELECT * FROM FKTABLE;
236
ftest1 | ftest2 | ftest3
237
--------+--------+--------
245
-- Update a row from PK TABLE
246
UPDATE PKTABLE SET ptest1=1 WHERE ptest1=2;
247
-- Check FKTABLE for update of matched row
248
SELECT * FROM FKTABLE;
249
ftest1 | ftest2 | ftest3
250
--------+--------+--------
258
-- this should fail for lack of CASCADE
260
ERROR: cannot drop table pktable because other objects depend on it
261
DETAIL: constraint constrname2 on table fktable depends on table pktable
262
HINT: Use DROP ... CASCADE to drop the dependent objects too.
263
DROP TABLE PKTABLE CASCADE;
264
NOTICE: drop cascades to constraint constrname2 on table fktable
267
-- First test, check with no on delete or on update
269
CREATE TABLE PKTABLE ( ptest1 int PRIMARY KEY, ptest2 text );
270
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
271
CREATE TABLE FKTABLE ( ftest1 int REFERENCES PKTABLE MATCH FULL, ftest2 int );
272
-- Insert test data into PKTABLE
273
INSERT INTO PKTABLE VALUES (1, 'Test1');
274
INSERT INTO PKTABLE VALUES (2, 'Test2');
275
INSERT INTO PKTABLE VALUES (3, 'Test3');
276
INSERT INTO PKTABLE VALUES (4, 'Test4');
277
INSERT INTO PKTABLE VALUES (5, 'Test5');
278
-- Insert successful rows into FK TABLE
279
INSERT INTO FKTABLE VALUES (1, 2);
280
INSERT INTO FKTABLE VALUES (2, 3);
281
INSERT INTO FKTABLE VALUES (3, 4);
282
INSERT INTO FKTABLE VALUES (NULL, 1);
283
-- Insert a failed row into FK TABLE
284
INSERT INTO FKTABLE VALUES (100, 2);
285
ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_fkey"
286
DETAIL: Key (ftest1)=(100) is not present in table "pktable".
288
SELECT * FROM FKTABLE;
298
SELECT * FROM PKTABLE;
308
-- Delete a row from PK TABLE (should fail)
309
DELETE FROM PKTABLE WHERE ptest1=1;
310
ERROR: update or delete on table "pktable" violates foreign key constraint "fktable_ftest1_fkey" on table "fktable"
311
DETAIL: Key (ptest1)=(1) is still referenced from table "fktable".
312
-- Delete a row from PK TABLE (should succeed)
313
DELETE FROM PKTABLE WHERE ptest1=5;
314
-- Check PKTABLE for deletes
315
SELECT * FROM PKTABLE;
324
-- Update a row from PK TABLE (should fail)
325
UPDATE PKTABLE SET ptest1=0 WHERE ptest1=2;
326
ERROR: update or delete on table "pktable" violates foreign key constraint "fktable_ftest1_fkey" on table "fktable"
327
DETAIL: Key (ptest1)=(2) is still referenced from table "fktable".
328
-- Update a row from PK TABLE (should succeed)
329
UPDATE PKTABLE SET ptest1=0 WHERE ptest1=4;
330
-- Check PKTABLE for updates
331
SELECT * FROM PKTABLE;
343
-- Base test restricting update/delete
344
CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2, ptest3) );
345
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
346
CREATE TABLE FKTABLE ( ftest1 int, ftest2 int, ftest3 int, ftest4 int, CONSTRAINT constrname3
347
FOREIGN KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE);
348
-- Insert Primary Key values
349
INSERT INTO PKTABLE VALUES (1, 2, 3, 'test1');
350
INSERT INTO PKTABLE VALUES (1, 3, 3, 'test2');
351
INSERT INTO PKTABLE VALUES (2, 3, 4, 'test3');
352
INSERT INTO PKTABLE VALUES (2, 4, 5, 'test4');
353
-- Insert Foreign Key values
354
INSERT INTO FKTABLE VALUES (1, 2, 3, 1);
355
INSERT INTO FKTABLE VALUES (NULL, 2, 3, 2);
356
INSERT INTO FKTABLE VALUES (2, NULL, 3, 3);
357
INSERT INTO FKTABLE VALUES (NULL, 2, 7, 4);
358
INSERT INTO FKTABLE VALUES (NULL, 3, 4, 5);
359
-- Insert a failed values
360
INSERT INTO FKTABLE VALUES (1, 2, 7, 6);
361
ERROR: insert or update on table "fktable" violates foreign key constraint "constrname3"
362
DETAIL: Key (ftest1,ftest2,ftest3)=(1,2,7) is not present in table "pktable".
364
SELECT * from FKTABLE;
365
ftest1 | ftest2 | ftest3 | ftest4
366
--------+--------+--------+--------
374
-- Try to update something that should fail
375
UPDATE PKTABLE set ptest2=5 where ptest2=2;
376
ERROR: update or delete on table "pktable" violates foreign key constraint "constrname3" on table "fktable"
377
DETAIL: Key (ptest1,ptest2,ptest3)=(1,2,3) is still referenced from table "fktable".
378
-- Try to update something that should succeed
379
UPDATE PKTABLE set ptest1=1 WHERE ptest2=3;
380
-- Try to delete something that should fail
381
DELETE FROM PKTABLE where ptest1=1 and ptest2=2 and ptest3=3;
382
ERROR: update or delete on table "pktable" violates foreign key constraint "constrname3" on table "fktable"
383
DETAIL: Key (ptest1,ptest2,ptest3)=(1,2,3) is still referenced from table "fktable".
384
-- Try to delete something that should work
385
DELETE FROM PKTABLE where ptest1=2;
386
-- Show PKTABLE and FKTABLE
387
SELECT * from PKTABLE;
388
ptest1 | ptest2 | ptest3 | ptest4
389
--------+--------+--------+--------
395
SELECT * from FKTABLE;
396
ftest1 | ftest2 | ftest3 | ftest4
397
--------+--------+--------+--------
407
-- cascade update/delete
408
CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2, ptest3) );
409
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
410
CREATE TABLE FKTABLE ( ftest1 int, ftest2 int, ftest3 int, ftest4 int, CONSTRAINT constrname3
411
FOREIGN KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE
412
ON DELETE CASCADE ON UPDATE CASCADE);
413
-- Insert Primary Key values
414
INSERT INTO PKTABLE VALUES (1, 2, 3, 'test1');
415
INSERT INTO PKTABLE VALUES (1, 3, 3, 'test2');
416
INSERT INTO PKTABLE VALUES (2, 3, 4, 'test3');
417
INSERT INTO PKTABLE VALUES (2, 4, 5, 'test4');
418
-- Insert Foreign Key values
419
INSERT INTO FKTABLE VALUES (1, 2, 3, 1);
420
INSERT INTO FKTABLE VALUES (NULL, 2, 3, 2);
421
INSERT INTO FKTABLE VALUES (2, NULL, 3, 3);
422
INSERT INTO FKTABLE VALUES (NULL, 2, 7, 4);
423
INSERT INTO FKTABLE VALUES (NULL, 3, 4, 5);
424
-- Insert a failed values
425
INSERT INTO FKTABLE VALUES (1, 2, 7, 6);
426
ERROR: insert or update on table "fktable" violates foreign key constraint "constrname3"
427
DETAIL: Key (ftest1,ftest2,ftest3)=(1,2,7) is not present in table "pktable".
429
SELECT * from FKTABLE;
430
ftest1 | ftest2 | ftest3 | ftest4
431
--------+--------+--------+--------
439
-- Try to update something that will cascade
440
UPDATE PKTABLE set ptest2=5 where ptest2=2;
441
-- Try to update something that should not cascade
442
UPDATE PKTABLE set ptest1=1 WHERE ptest2=3;
443
-- Show PKTABLE and FKTABLE
444
SELECT * from PKTABLE;
445
ptest1 | ptest2 | ptest3 | ptest4
446
--------+--------+--------+--------
453
SELECT * from FKTABLE;
454
ftest1 | ftest2 | ftest3 | ftest4
455
--------+--------+--------+--------
463
-- Try to delete something that should cascade
464
DELETE FROM PKTABLE where ptest1=1 and ptest2=5 and ptest3=3;
465
-- Show PKTABLE and FKTABLE
466
SELECT * from PKTABLE;
467
ptest1 | ptest2 | ptest3 | ptest4
468
--------+--------+--------+--------
474
SELECT * from FKTABLE;
475
ftest1 | ftest2 | ftest3 | ftest4
476
--------+--------+--------+--------
483
-- Try to delete something that should not have a cascade
484
DELETE FROM PKTABLE where ptest1=2;
485
-- Show PKTABLE and FKTABLE
486
SELECT * from PKTABLE;
487
ptest1 | ptest2 | ptest3 | ptest4
488
--------+--------+--------+--------
493
SELECT * from FKTABLE;
494
ftest1 | ftest2 | ftest3 | ftest4
495
--------+--------+--------+--------
504
-- set null update / set default delete
505
CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2, ptest3) );
506
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
507
CREATE TABLE FKTABLE ( ftest1 int DEFAULT 0, ftest2 int, ftest3 int, ftest4 int, CONSTRAINT constrname3
508
FOREIGN KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE
509
ON DELETE SET DEFAULT ON UPDATE SET NULL);
510
-- Insert Primary Key values
511
INSERT INTO PKTABLE VALUES (1, 2, 3, 'test1');
512
INSERT INTO PKTABLE VALUES (1, 3, 3, 'test2');
513
INSERT INTO PKTABLE VALUES (2, 3, 4, 'test3');
514
INSERT INTO PKTABLE VALUES (2, 4, 5, 'test4');
515
-- Insert Foreign Key values
516
INSERT INTO FKTABLE VALUES (1, 2, 3, 1);
517
INSERT INTO FKTABLE VALUES (2, 3, 4, 1);
518
INSERT INTO FKTABLE VALUES (NULL, 2, 3, 2);
519
INSERT INTO FKTABLE VALUES (2, NULL, 3, 3);
520
INSERT INTO FKTABLE VALUES (NULL, 2, 7, 4);
521
INSERT INTO FKTABLE VALUES (NULL, 3, 4, 5);
522
-- Insert a failed values
523
INSERT INTO FKTABLE VALUES (1, 2, 7, 6);
524
ERROR: insert or update on table "fktable" violates foreign key constraint "constrname3"
525
DETAIL: Key (ftest1,ftest2,ftest3)=(1,2,7) is not present in table "pktable".
527
SELECT * from FKTABLE;
528
ftest1 | ftest2 | ftest3 | ftest4
529
--------+--------+--------+--------
538
-- Try to update something that will set null
539
UPDATE PKTABLE set ptest2=5 where ptest2=2;
540
-- Try to update something that should not set null
541
UPDATE PKTABLE set ptest2=2 WHERE ptest2=3 and ptest1=1;
542
-- Show PKTABLE and FKTABLE
543
SELECT * from PKTABLE;
544
ptest1 | ptest2 | ptest3 | ptest4
545
--------+--------+--------+--------
552
SELECT * from FKTABLE;
553
ftest1 | ftest2 | ftest3 | ftest4
554
--------+--------+--------+--------
563
-- Try to delete something that should set default
564
DELETE FROM PKTABLE where ptest1=2 and ptest2=3 and ptest3=4;
565
-- Show PKTABLE and FKTABLE
566
SELECT * from PKTABLE;
567
ptest1 | ptest2 | ptest3 | ptest4
568
--------+--------+--------+--------
574
SELECT * from FKTABLE;
575
ftest1 | ftest2 | ftest3 | ftest4
576
--------+--------+--------+--------
585
-- Try to delete something that should not set default
586
DELETE FROM PKTABLE where ptest2=5;
587
-- Show PKTABLE and FKTABLE
588
SELECT * from PKTABLE;
589
ptest1 | ptest2 | ptest3 | ptest4
590
--------+--------+--------+--------
595
SELECT * from FKTABLE;
596
ftest1 | ftest2 | ftest3 | ftest4
597
--------+--------+--------+--------
608
-- set default update / set null delete
609
CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2, ptest3) );
610
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
611
CREATE TABLE FKTABLE ( ftest1 int DEFAULT 0, ftest2 int DEFAULT -1, ftest3 int, ftest4 int, CONSTRAINT constrname3
612
FOREIGN KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE
613
ON DELETE SET NULL ON UPDATE SET DEFAULT);
614
-- Insert Primary Key values
615
INSERT INTO PKTABLE VALUES (1, 2, 3, 'test1');
616
INSERT INTO PKTABLE VALUES (1, 3, 3, 'test2');
617
INSERT INTO PKTABLE VALUES (2, 3, 4, 'test3');
618
INSERT INTO PKTABLE VALUES (2, 4, 5, 'test4');
619
INSERT INTO PKTABLE VALUES (2, -1, 5, 'test5');
620
-- Insert Foreign Key values
621
INSERT INTO FKTABLE VALUES (1, 2, 3, 1);
622
INSERT INTO FKTABLE VALUES (2, 3, 4, 1);
623
INSERT INTO FKTABLE VALUES (2, 4, 5, 1);
624
INSERT INTO FKTABLE VALUES (NULL, 2, 3, 2);
625
INSERT INTO FKTABLE VALUES (2, NULL, 3, 3);
626
INSERT INTO FKTABLE VALUES (NULL, 2, 7, 4);
627
INSERT INTO FKTABLE VALUES (NULL, 3, 4, 5);
628
-- Insert a failed values
629
INSERT INTO FKTABLE VALUES (1, 2, 7, 6);
630
ERROR: insert or update on table "fktable" violates foreign key constraint "constrname3"
631
DETAIL: Key (ftest1,ftest2,ftest3)=(1,2,7) is not present in table "pktable".
633
SELECT * from FKTABLE;
634
ftest1 | ftest2 | ftest3 | ftest4
635
--------+--------+--------+--------
645
-- Try to update something that will fail
646
UPDATE PKTABLE set ptest2=5 where ptest2=2;
647
ERROR: insert or update on table "fktable" violates foreign key constraint "constrname3"
648
DETAIL: Key (ftest1,ftest2,ftest3)=(1,-1,3) is not present in table "pktable".
649
-- Try to update something that will set default
650
UPDATE PKTABLE set ptest1=0, ptest2=5, ptest3=10 where ptest2=2;
651
UPDATE PKTABLE set ptest2=10 where ptest2=4;
652
-- Try to update something that should not set default
653
UPDATE PKTABLE set ptest2=2 WHERE ptest2=3 and ptest1=1;
654
-- Show PKTABLE and FKTABLE
655
SELECT * from PKTABLE;
656
ptest1 | ptest2 | ptest3 | ptest4
657
--------+--------+--------+--------
665
SELECT * from FKTABLE;
666
ftest1 | ftest2 | ftest3 | ftest4
667
--------+--------+--------+--------
677
-- Try to delete something that should set null
678
DELETE FROM PKTABLE where ptest1=2 and ptest2=3 and ptest3=4;
679
-- Show PKTABLE and FKTABLE
680
SELECT * from PKTABLE;
681
ptest1 | ptest2 | ptest3 | ptest4
682
--------+--------+--------+--------
689
SELECT * from FKTABLE;
690
ftest1 | ftest2 | ftest3 | ftest4
691
--------+--------+--------+--------
701
-- Try to delete something that should not set null
702
DELETE FROM PKTABLE where ptest2=5;
703
-- Show PKTABLE and FKTABLE
704
SELECT * from PKTABLE;
705
ptest1 | ptest2 | ptest3 | ptest4
706
--------+--------+--------+--------
712
SELECT * from FKTABLE;
713
ftest1 | ftest2 | ftest3 | ftest4
714
--------+--------+--------+--------
726
CREATE TABLE PKTABLE (ptest1 int PRIMARY KEY);
727
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
728
CREATE TABLE FKTABLE_FAIL1 ( ftest1 int, CONSTRAINT fkfail1 FOREIGN KEY (ftest2) REFERENCES PKTABLE);
729
ERROR: column "ftest2" referenced in foreign key constraint does not exist
730
CREATE TABLE FKTABLE_FAIL2 ( ftest1 int, CONSTRAINT fkfail1 FOREIGN KEY (ftest1) REFERENCES PKTABLE(ptest2));
731
ERROR: column "ptest2" referenced in foreign key constraint does not exist
732
DROP TABLE FKTABLE_FAIL1;
733
ERROR: table "fktable_fail1" does not exist
734
DROP TABLE FKTABLE_FAIL2;
735
ERROR: table "fktable_fail2" does not exist
737
-- Test for referencing column number smaller than referenced constraint
738
CREATE TABLE PKTABLE (ptest1 int, ptest2 int, UNIQUE(ptest1, ptest2));
739
NOTICE: CREATE TABLE / UNIQUE will create implicit index "pktable_ptest1_key" for table "pktable"
740
CREATE TABLE FKTABLE_FAIL1 (ftest1 int REFERENCES pktable(ptest1));
741
ERROR: there is no unique constraint matching given keys for referenced table "pktable"
742
DROP TABLE FKTABLE_FAIL1;
743
ERROR: table "fktable_fail1" does not exist
746
-- Tests for mismatched types
748
-- Basic one column, two table setup
749
CREATE TABLE PKTABLE (ptest1 int PRIMARY KEY);
750
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
751
INSERT INTO PKTABLE VALUES(42);
752
-- This next should fail, because int=inet does not exist
753
CREATE TABLE FKTABLE (ftest1 inet REFERENCES pktable);
754
ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented
755
DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: inet and integer.
756
-- This should also fail for the same reason, but here we
757
-- give the column name
758
CREATE TABLE FKTABLE (ftest1 inet REFERENCES pktable(ptest1));
759
ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented
760
DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: inet and integer.
761
-- This should succeed, even though they are different types,
762
-- because int=int8 exists and is a member of the integer opfamily
763
CREATE TABLE FKTABLE (ftest1 int8 REFERENCES pktable);
764
-- Check it actually works
765
INSERT INTO FKTABLE VALUES(42); -- should succeed
766
INSERT INTO FKTABLE VALUES(43); -- should fail
767
ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_fkey"
768
DETAIL: Key (ftest1)=(43) is not present in table "pktable".
769
UPDATE FKTABLE SET ftest1 = ftest1; -- should succeed
770
UPDATE FKTABLE SET ftest1 = ftest1 + 1; -- should fail
771
ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_fkey"
772
DETAIL: Key (ftest1)=(43) is not present in table "pktable".
774
-- This should fail, because we'd have to cast numeric to int which is
775
-- not an implicit coercion (or use numeric=numeric, but that's not part
776
-- of the integer opfamily)
777
CREATE TABLE FKTABLE (ftest1 numeric REFERENCES pktable);
778
ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented
779
DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: numeric and integer.
781
-- On the other hand, this should work because int implicitly promotes to
782
-- numeric, and we allow promotion on the FK side
783
CREATE TABLE PKTABLE (ptest1 numeric PRIMARY KEY);
784
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
785
INSERT INTO PKTABLE VALUES(42);
786
CREATE TABLE FKTABLE (ftest1 int REFERENCES pktable);
787
-- Check it actually works
788
INSERT INTO FKTABLE VALUES(42); -- should succeed
789
INSERT INTO FKTABLE VALUES(43); -- should fail
790
ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_fkey"
791
DETAIL: Key (ftest1)=(43) is not present in table "pktable".
792
UPDATE FKTABLE SET ftest1 = ftest1; -- should succeed
793
UPDATE FKTABLE SET ftest1 = ftest1 + 1; -- should fail
794
ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_fkey"
795
DETAIL: Key (ftest1)=(43) is not present in table "pktable".
798
-- Two columns, two tables
799
CREATE TABLE PKTABLE (ptest1 int, ptest2 inet, PRIMARY KEY(ptest1, ptest2));
800
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
801
-- This should fail, because we just chose really odd types
802
CREATE TABLE FKTABLE (ftest1 cidr, ftest2 timestamp, FOREIGN KEY(ftest1, ftest2) REFERENCES pktable);
803
ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented
804
DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: cidr and integer.
805
-- Again, so should this...
806
CREATE TABLE FKTABLE (ftest1 cidr, ftest2 timestamp, FOREIGN KEY(ftest1, ftest2) REFERENCES pktable(ptest1, ptest2));
807
ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented
808
DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: cidr and integer.
809
-- This fails because we mixed up the column ordering
810
CREATE TABLE FKTABLE (ftest1 int, ftest2 inet, FOREIGN KEY(ftest2, ftest1) REFERENCES pktable);
811
ERROR: foreign key constraint "fktable_ftest2_fkey" cannot be implemented
812
DETAIL: Key columns "ftest2" and "ptest1" are of incompatible types: inet and integer.
814
CREATE TABLE FKTABLE (ftest1 int, ftest2 inet, FOREIGN KEY(ftest2, ftest1) REFERENCES pktable(ptest1, ptest2));
815
ERROR: foreign key constraint "fktable_ftest2_fkey" cannot be implemented
816
DETAIL: Key columns "ftest2" and "ptest1" are of incompatible types: inet and integer.
818
CREATE TABLE FKTABLE (ftest1 int, ftest2 inet, FOREIGN KEY(ftest1, ftest2) REFERENCES pktable(ptest2, ptest1));
819
ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented
820
DETAIL: Key columns "ftest1" and "ptest2" are of incompatible types: integer and inet.
822
CREATE TABLE FKTABLE (ftest1 int, ftest2 inet, FOREIGN KEY(ftest2, ftest1) REFERENCES pktable(ptest2, ptest1));
825
CREATE TABLE FKTABLE (ftest1 int, ftest2 inet, FOREIGN KEY(ftest1, ftest2) REFERENCES pktable(ptest1, ptest2));
828
-- Two columns, same table
829
-- Make sure this still works...
830
CREATE TABLE PKTABLE (ptest1 int, ptest2 inet, ptest3 int, ptest4 inet, PRIMARY KEY(ptest1, ptest2), FOREIGN KEY(ptest3,
831
ptest4) REFERENCES pktable(ptest1, ptest2));
832
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
835
CREATE TABLE PKTABLE (ptest1 int, ptest2 inet, ptest3 int, ptest4 inet, PRIMARY KEY(ptest1, ptest2), FOREIGN KEY(ptest3,
836
ptest4) REFERENCES pktable);
837
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
839
-- This shouldn't (mixed up columns)
840
CREATE TABLE PKTABLE (ptest1 int, ptest2 inet, ptest3 int, ptest4 inet, PRIMARY KEY(ptest1, ptest2), FOREIGN KEY(ptest3,
841
ptest4) REFERENCES pktable(ptest2, ptest1));
842
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
843
ERROR: foreign key constraint "pktable_ptest3_fkey" cannot be implemented
844
DETAIL: Key columns "ptest3" and "ptest2" are of incompatible types: integer and inet.
845
-- Nor should this... (same reason, we have 4,3 referencing 1,2 which mismatches types
846
CREATE TABLE PKTABLE (ptest1 int, ptest2 inet, ptest3 int, ptest4 inet, PRIMARY KEY(ptest1, ptest2), FOREIGN KEY(ptest4,
847
ptest3) REFERENCES pktable(ptest1, ptest2));
848
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
849
ERROR: foreign key constraint "pktable_ptest4_fkey" cannot be implemented
850
DETAIL: Key columns "ptest4" and "ptest1" are of incompatible types: inet and integer.
851
-- Not this one either... Same as the last one except we didn't defined the columns being referenced.
852
CREATE TABLE PKTABLE (ptest1 int, ptest2 inet, ptest3 int, ptest4 inet, PRIMARY KEY(ptest1, ptest2), FOREIGN KEY(ptest4,
853
ptest3) REFERENCES pktable);
854
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
855
ERROR: foreign key constraint "pktable_ptest4_fkey" cannot be implemented
856
DETAIL: Key columns "ptest4" and "ptest1" are of incompatible types: inet and integer.
858
-- Now some cases with inheritance
859
-- Basic 2 table case: 1 column of matching types.
860
create table pktable_base (base1 int not null);
861
create table pktable (ptest1 int, primary key(base1), unique(base1, ptest1)) inherits (pktable_base);
862
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
863
NOTICE: CREATE TABLE / UNIQUE will create implicit index "pktable_base1_key" for table "pktable"
864
create table fktable (ftest1 int references pktable(base1));
865
-- now some ins, upd, del
866
insert into pktable(base1) values (1);
867
insert into pktable(base1) values (2);
868
-- let's insert a non-existant fktable value
869
insert into fktable(ftest1) values (3);
870
ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_fkey"
871
DETAIL: Key (ftest1)=(3) is not present in table "pktable".
872
-- let's make a valid row for that
873
insert into pktable(base1) values (3);
874
insert into fktable(ftest1) values (3);
875
-- let's try removing a row that should fail from pktable
876
delete from pktable where base1>2;
877
ERROR: update or delete on table "pktable" violates foreign key constraint "fktable_ftest1_fkey" on table "fktable"
878
DETAIL: Key (base1)=(3) is still referenced from table "fktable".
879
-- okay, let's try updating all of the base1 values to *4
880
-- which should fail.
881
update pktable set base1=base1*4;
882
ERROR: update or delete on table "pktable" violates foreign key constraint "fktable_ftest1_fkey" on table "fktable"
883
DETAIL: Key (base1)=(3) is still referenced from table "fktable".
884
-- okay, let's try an update that should work.
885
update pktable set base1=base1*4 where base1<3;
886
-- and a delete that should work
887
delete from pktable where base1>3;
891
-- Now 2 columns 2 tables, matching types
892
create table fktable (ftest1 int, ftest2 int, foreign key(ftest1, ftest2) references pktable(base1, ptest1));
893
-- now some ins, upd, del
894
insert into pktable(base1, ptest1) values (1, 1);
895
insert into pktable(base1, ptest1) values (2, 2);
896
-- let's insert a non-existant fktable value
897
insert into fktable(ftest1, ftest2) values (3, 1);
898
ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_fkey"
899
DETAIL: Key (ftest1,ftest2)=(3,1) is not present in table "pktable".
900
-- let's make a valid row for that
901
insert into pktable(base1,ptest1) values (3, 1);
902
insert into fktable(ftest1, ftest2) values (3, 1);
903
-- let's try removing a row that should fail from pktable
904
delete from pktable where base1>2;
905
ERROR: update or delete on table "pktable" violates foreign key constraint "fktable_ftest1_fkey" on table "fktable"
906
DETAIL: Key (base1,ptest1)=(3,1) is still referenced from table "fktable".
907
-- okay, let's try updating all of the base1 values to *4
908
-- which should fail.
909
update pktable set base1=base1*4;
910
ERROR: update or delete on table "pktable" violates foreign key constraint "fktable_ftest1_fkey" on table "fktable"
911
DETAIL: Key (base1,ptest1)=(3,1) is still referenced from table "fktable".
912
-- okay, let's try an update that should work.
913
update pktable set base1=base1*4 where base1<3;
914
-- and a delete that should work
915
delete from pktable where base1>3;
919
drop table pktable_base;
920
-- Now we'll do one all in 1 table with 2 columns of matching types
921
create table pktable_base(base1 int not null, base2 int);
922
create table pktable(ptest1 int, ptest2 int, primary key(base1, ptest1), foreign key(base2, ptest2) references
923
pktable(base1, ptest1)) inherits (pktable_base);
924
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
925
insert into pktable (base1, ptest1, base2, ptest2) values (1, 1, 1, 1);
926
insert into pktable (base1, ptest1, base2, ptest2) values (2, 1, 1, 1);
927
insert into pktable (base1, ptest1, base2, ptest2) values (2, 2, 2, 1);
928
insert into pktable (base1, ptest1, base2, ptest2) values (1, 3, 2, 2);
929
-- fails (3,2) isn't in base1, ptest1
930
insert into pktable (base1, ptest1, base2, ptest2) values (2, 3, 3, 2);
931
ERROR: insert or update on table "pktable" violates foreign key constraint "pktable_base2_fkey"
932
DETAIL: Key (base2,ptest2)=(3,2) is not present in table "pktable".
933
-- fails (2,2) is being referenced
934
delete from pktable where base1=2;
935
ERROR: update or delete on table "pktable" violates foreign key constraint "pktable_base2_fkey" on table "pktable"
936
DETAIL: Key (base1,ptest1)=(2,2) is still referenced from table "pktable".
937
-- fails (1,1) is being referenced (twice)
938
update pktable set base1=3 where base1=1;
939
ERROR: update or delete on table "pktable" violates foreign key constraint "pktable_base2_fkey" on table "pktable"
940
DETAIL: Key (base1,ptest1)=(1,1) is still referenced from table "pktable".
941
-- this sequence of two deletes will work, since after the first there will be no (2,*) references
942
delete from pktable where base2=2;
943
delete from pktable where base1=2;
945
drop table pktable_base;
946
-- 2 columns (2 tables), mismatched types
947
create table pktable_base(base1 int not null);
948
create table pktable(ptest1 inet, primary key(base1, ptest1)) inherits (pktable_base);
949
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
950
-- just generally bad types (with and without column references on the referenced table)
951
create table fktable(ftest1 cidr, ftest2 int[], foreign key (ftest1, ftest2) references pktable);
952
ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented
953
DETAIL: Key columns "ftest1" and "base1" are of incompatible types: cidr and integer.
954
create table fktable(ftest1 cidr, ftest2 int[], foreign key (ftest1, ftest2) references pktable(base1, ptest1));
955
ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented
956
DETAIL: Key columns "ftest1" and "base1" are of incompatible types: cidr and integer.
957
-- let's mix up which columns reference which
958
create table fktable(ftest1 int, ftest2 inet, foreign key(ftest2, ftest1) references pktable);
959
ERROR: foreign key constraint "fktable_ftest2_fkey" cannot be implemented
960
DETAIL: Key columns "ftest2" and "base1" are of incompatible types: inet and integer.
961
create table fktable(ftest1 int, ftest2 inet, foreign key(ftest2, ftest1) references pktable(base1, ptest1));
962
ERROR: foreign key constraint "fktable_ftest2_fkey" cannot be implemented
963
DETAIL: Key columns "ftest2" and "base1" are of incompatible types: inet and integer.
964
create table fktable(ftest1 int, ftest2 inet, foreign key(ftest1, ftest2) references pktable(ptest1, base1));
965
ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented
966
DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: integer and inet.
968
drop table pktable_base;
969
-- 2 columns (1 table), mismatched types
970
create table pktable_base(base1 int not null, base2 int);
971
create table pktable(ptest1 inet, ptest2 inet[], primary key(base1, ptest1), foreign key(base2, ptest2) references
972
pktable(base1, ptest1)) inherits (pktable_base);
973
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
974
ERROR: foreign key constraint "pktable_base2_fkey" cannot be implemented
975
DETAIL: Key columns "ptest2" and "ptest1" are of incompatible types: inet[] and inet.
976
create table pktable(ptest1 inet, ptest2 inet, primary key(base1, ptest1), foreign key(base2, ptest2) references
977
pktable(ptest1, base1)) inherits (pktable_base);
978
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
979
ERROR: foreign key constraint "pktable_base2_fkey" cannot be implemented
980
DETAIL: Key columns "base2" and "ptest1" are of incompatible types: integer and inet.
981
create table pktable(ptest1 inet, ptest2 inet, primary key(base1, ptest1), foreign key(ptest2, base2) references
982
pktable(base1, ptest1)) inherits (pktable_base);
983
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
984
ERROR: foreign key constraint "pktable_ptest2_fkey" cannot be implemented
985
DETAIL: Key columns "ptest2" and "base1" are of incompatible types: inet and integer.
986
create table pktable(ptest1 inet, ptest2 inet, primary key(base1, ptest1), foreign key(ptest2, base2) references
987
pktable(base1, ptest1)) inherits (pktable_base);
988
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
989
ERROR: foreign key constraint "pktable_ptest2_fkey" cannot be implemented
990
DETAIL: Key columns "ptest2" and "base1" are of incompatible types: inet and integer.
992
ERROR: table "pktable" does not exist
993
drop table pktable_base;
995
-- Deferrable constraints
996
-- (right now, only FOREIGN KEY constraints can be deferred)
998
-- deferrable, explicitly deferred
999
CREATE TABLE pktable (
1000
id INT4 PRIMARY KEY,
1003
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
1004
CREATE TABLE fktable (
1005
id INT4 PRIMARY KEY,
1006
fk INT4 REFERENCES pktable DEFERRABLE
1008
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "fktable_pkey" for table "fktable"
1009
-- default to immediate: should fail
1010
INSERT INTO fktable VALUES (5, 10);
1011
ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey"
1012
DETAIL: Key (fk)=(10) is not present in table "pktable".
1013
-- explicitly defer the constraint
1015
SET CONSTRAINTS ALL DEFERRED;
1016
INSERT INTO fktable VALUES (10, 15);
1017
INSERT INTO pktable VALUES (15, 0); -- make the FK insert valid
1019
DROP TABLE fktable, pktable;
1020
-- deferrable, initially deferred
1021
CREATE TABLE pktable (
1022
id INT4 PRIMARY KEY,
1025
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
1026
CREATE TABLE fktable (
1027
id INT4 PRIMARY KEY,
1028
fk INT4 REFERENCES pktable DEFERRABLE INITIALLY DEFERRED
1030
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "fktable_pkey" for table "fktable"
1031
-- default to deferred, should succeed
1033
INSERT INTO fktable VALUES (100, 200);
1034
INSERT INTO pktable VALUES (200, 500); -- make the FK insert valid
1036
-- default to deferred, explicitly make immediate
1038
SET CONSTRAINTS ALL IMMEDIATE;
1040
INSERT INTO fktable VALUES (500, 1000);
1041
ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey"
1042
DETAIL: Key (fk)=(1000) is not present in table "pktable".
1044
DROP TABLE fktable, pktable;
1045
-- tricky behavior: according to SQL99, if a deferred constraint is set
1046
-- to 'immediate' mode, it should be checked for validity *immediately*,
1047
-- not when the current transaction commits (i.e. the mode change applies
1049
CREATE TABLE pktable (
1050
id INT4 PRIMARY KEY,
1053
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
1054
CREATE TABLE fktable (
1055
id INT4 PRIMARY KEY,
1056
fk INT4 REFERENCES pktable DEFERRABLE
1058
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "fktable_pkey" for table "fktable"
1060
SET CONSTRAINTS ALL DEFERRED;
1061
-- should succeed, for now
1062
INSERT INTO fktable VALUES (1000, 2000);
1063
-- should cause transaction abort, due to preceding error
1064
SET CONSTRAINTS ALL IMMEDIATE;
1065
ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey"
1066
DETAIL: Key (fk)=(2000) is not present in table "pktable".
1067
INSERT INTO pktable VALUES (2000, 3); -- too late
1068
ERROR: current transaction is aborted, commands ignored until end of transaction block
1070
DROP TABLE fktable, pktable;
1071
-- deferrable, initially deferred
1072
CREATE TABLE pktable (
1073
id INT4 PRIMARY KEY,
1076
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
1077
CREATE TABLE fktable (
1078
id INT4 PRIMARY KEY,
1079
fk INT4 REFERENCES pktable DEFERRABLE INITIALLY DEFERRED
1081
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "fktable_pkey" for table "fktable"
1084
INSERT INTO fktable VALUES (100, 200);
1085
-- error here on commit
1087
ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey"
1088
DETAIL: Key (fk)=(200) is not present in table "pktable".
1089
DROP TABLE pktable, fktable;
1090
-- test notice about expensive referential integrity checks,
1091
-- where the index cannot be used because of type incompatibilities.
1092
CREATE TEMP TABLE pktable (
1093
id1 INT4 PRIMARY KEY,
1094
id2 VARCHAR(4) UNIQUE,
1096
UNIQUE(id1, id2, id3)
1098
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
1099
NOTICE: CREATE TABLE / UNIQUE will create implicit index "pktable_id2_key" for table "pktable"
1100
NOTICE: CREATE TABLE / UNIQUE will create implicit index "pktable_id3_key" for table "pktable"
1101
NOTICE: CREATE TABLE / UNIQUE will create implicit index "pktable_id1_key" for table "pktable"
1102
CREATE TEMP TABLE fktable (
1103
x1 INT4 REFERENCES pktable(id1),
1104
x2 VARCHAR(4) REFERENCES pktable(id2),
1105
x3 REAL REFERENCES pktable(id3),
1109
-- check individual constraints with alter table.
1111
-- varchar does not promote to real
1112
ALTER TABLE fktable ADD CONSTRAINT fk_2_3
1113
FOREIGN KEY (x2) REFERENCES pktable(id3);
1114
ERROR: foreign key constraint "fk_2_3" cannot be implemented
1115
DETAIL: Key columns "x2" and "id3" are of incompatible types: character varying and real.
1117
ALTER TABLE fktable ADD CONSTRAINT fk_2_1
1118
FOREIGN KEY (x2) REFERENCES pktable(id1);
1119
ERROR: foreign key constraint "fk_2_1" cannot be implemented
1120
DETAIL: Key columns "x2" and "id1" are of incompatible types: character varying and integer.
1121
-- real does not promote to int4
1122
ALTER TABLE fktable ADD CONSTRAINT fk_3_1
1123
FOREIGN KEY (x3) REFERENCES pktable(id1);
1124
ERROR: foreign key constraint "fk_3_1" cannot be implemented
1125
DETAIL: Key columns "x3" and "id1" are of incompatible types: real and integer.
1126
-- int4 does not promote to text
1127
ALTER TABLE fktable ADD CONSTRAINT fk_1_2
1128
FOREIGN KEY (x1) REFERENCES pktable(id2);
1129
ERROR: foreign key constraint "fk_1_2" cannot be implemented
1130
DETAIL: Key columns "x1" and "id2" are of incompatible types: integer and character varying.
1132
-- int4 promotes to real
1133
ALTER TABLE fktable ADD CONSTRAINT fk_1_3
1134
FOREIGN KEY (x1) REFERENCES pktable(id3);
1135
-- text is compatible with varchar
1136
ALTER TABLE fktable ADD CONSTRAINT fk_4_2
1137
FOREIGN KEY (x4) REFERENCES pktable(id2);
1138
-- int2 is part of integer opfamily as of 8.0
1139
ALTER TABLE fktable ADD CONSTRAINT fk_5_1
1140
FOREIGN KEY (x5) REFERENCES pktable(id1);
1141
-- check multikey cases, especially out-of-order column lists
1142
-- these should work
1143
ALTER TABLE fktable ADD CONSTRAINT fk_123_123
1144
FOREIGN KEY (x1,x2,x3) REFERENCES pktable(id1,id2,id3);
1145
ALTER TABLE fktable ADD CONSTRAINT fk_213_213
1146
FOREIGN KEY (x2,x1,x3) REFERENCES pktable(id2,id1,id3);
1147
ALTER TABLE fktable ADD CONSTRAINT fk_253_213
1148
FOREIGN KEY (x2,x5,x3) REFERENCES pktable(id2,id1,id3);
1149
-- these should fail
1150
ALTER TABLE fktable ADD CONSTRAINT fk_123_231
1151
FOREIGN KEY (x1,x2,x3) REFERENCES pktable(id2,id3,id1);
1152
ERROR: foreign key constraint "fk_123_231" cannot be implemented
1153
DETAIL: Key columns "x1" and "id2" are of incompatible types: integer and character varying.
1154
ALTER TABLE fktable ADD CONSTRAINT fk_241_132
1155
FOREIGN KEY (x2,x4,x1) REFERENCES pktable(id1,id3,id2);
1156
ERROR: foreign key constraint "fk_241_132" cannot be implemented
1157
DETAIL: Key columns "x2" and "id1" are of incompatible types: character varying and integer.
1158
DROP TABLE pktable, fktable;
1159
-- test a tricky case: we can elide firing the FK check trigger during
1160
-- an UPDATE if the UPDATE did not change the foreign key
1161
-- field. However, we can't do this if our transaction was the one that
1162
-- created the updated row and the trigger is deferred, since our UPDATE
1163
-- will have invalidated the original newly-inserted tuple, and therefore
1164
-- cause the on-INSERT RI trigger not to be fired.
1165
CREATE TEMP TABLE pktable (
1169
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
1170
CREATE TEMP TABLE fktable (
1172
fk int references pktable deferrable initially deferred
1174
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "fktable_pkey" for table "fktable"
1175
INSERT INTO pktable VALUES (5, 10);
1177
-- doesn't match PK, but no error yet
1178
INSERT INTO fktable VALUES (0, 20);
1180
UPDATE fktable SET id = id + 1;
1181
-- should catch error from initial INSERT
1183
ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey"
1184
DETAIL: Key (fk)=(20) is not present in table "pktable".
1185
-- check same case when insert is in a different subtransaction than update
1187
-- doesn't match PK, but no error yet
1188
INSERT INTO fktable VALUES (0, 20);
1189
-- UPDATE will be in a subxact
1192
UPDATE fktable SET id = id + 1;
1193
-- should catch error from initial INSERT
1195
ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey"
1196
DETAIL: Key (fk)=(20) is not present in table "pktable".
1198
-- INSERT will be in a subxact
1200
-- doesn't match PK, but no error yet
1201
INSERT INTO fktable VALUES (0, 20);
1202
RELEASE SAVEPOINT savept1;
1204
UPDATE fktable SET id = id + 1;
1205
-- should catch error from initial INSERT
1207
ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey"
1208
DETAIL: Key (fk)=(20) is not present in table "pktable".
1210
-- doesn't match PK, but no error yet
1211
INSERT INTO fktable VALUES (0, 20);
1212
-- UPDATE will be in a subxact
1215
UPDATE fktable SET id = id + 1;
1216
-- Roll back the UPDATE
1217
ROLLBACK TO savept1;
1218
-- should catch error from initial INSERT
1220
ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey"
1221
DETAIL: Key (fk)=(20) is not present in table "pktable".
1222
-- test order of firing of FK triggers when several RI-induced changes need to
1223
-- be made to the same row. This was broken by subtransaction-related
1225
CREATE TEMP TABLE users (
1227
name VARCHAR NOT NULL
1229
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "users_pkey" for table "users"
1230
INSERT INTO users VALUES (1, 'Jozko');
1231
INSERT INTO users VALUES (2, 'Ferko');
1232
INSERT INTO users VALUES (3, 'Samko');
1233
CREATE TEMP TABLE tasks (
1235
owner INT REFERENCES users ON UPDATE CASCADE ON DELETE SET NULL,
1236
worker INT REFERENCES users ON UPDATE CASCADE ON DELETE SET NULL,
1237
checked_by INT REFERENCES users ON UPDATE CASCADE ON DELETE SET NULL
1239
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "tasks_pkey" for table "tasks"
1240
INSERT INTO tasks VALUES (1,1,NULL,NULL);
1241
INSERT INTO tasks VALUES (2,2,2,NULL);
1242
INSERT INTO tasks VALUES (3,3,3,3);
1243
SELECT * FROM tasks;
1244
id | owner | worker | checked_by
1245
----+-------+--------+------------
1251
UPDATE users SET id = 4 WHERE id = 3;
1252
SELECT * FROM tasks;
1253
id | owner | worker | checked_by
1254
----+-------+--------+------------
1260
DELETE FROM users WHERE id = 4;
1261
SELECT * FROM tasks;
1262
id | owner | worker | checked_by
1263
----+-------+--------+------------
1269
-- could fail with only 2 changes to make, if row was already updated
1271
UPDATE tasks set id=id WHERE id=2;
1272
SELECT * FROM tasks;
1273
id | owner | worker | checked_by
1274
----+-------+--------+------------
1280
DELETE FROM users WHERE id = 2;
1281
SELECT * FROM tasks;
1282
id | owner | worker | checked_by
1283
----+-------+--------+------------
added
removed
src/test/regress/expected/foreign_key.out
