| blob | 3f459f70ac1e63a2a0d4552a7801fc193908589a |
1 --
2 -- FOREIGN KEY
3 --
4 -- MATCH FULL
5 --
6 -- First test, check and cascade
7 --
8 CREATE TABLE PKTABLE ( ptest1 int PRIMARY KEY, ptest2 text );
9 CREATE TABLE FKTABLE ( ftest1 int REFERENCES PKTABLE MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE, ftest2 int );
10 -- Insert test data into PKTABLE
11 INSERT INTO PKTABLE VALUES (1, 'Test1');
12 INSERT INTO PKTABLE VALUES (2, 'Test2');
13 INSERT INTO PKTABLE VALUES (3, 'Test3');
14 INSERT INTO PKTABLE VALUES (4, 'Test4');
15 INSERT INTO PKTABLE VALUES (5, 'Test5');
16 -- Insert successful rows into FK TABLE
17 INSERT INTO FKTABLE VALUES (1, 2);
18 INSERT INTO FKTABLE VALUES (2, 3);
19 INSERT INTO FKTABLE VALUES (3, 4);
20 INSERT INTO FKTABLE VALUES (NULL, 1);
21 -- Insert a failed row into FK TABLE
22 INSERT INTO FKTABLE VALUES (100, 2);
23 ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_fkey"
24 DETAIL: Key (ftest1)=(100) is not present in table "pktable".
25 -- Check FKTABLE
26 SELECT * FROM FKTABLE;
27 ftest1 | ftest2
28 --------+--------
29 1 | 2
30 2 | 3
31 3 | 4
32 | 1
33 (4 rows)
35 -- Delete a row from PK TABLE
36 DELETE FROM PKTABLE WHERE ptest1=1;
37 -- Check FKTABLE for removal of matched row
38 SELECT * FROM FKTABLE;
39 ftest1 | ftest2
40 --------+--------
41 2 | 3
42 3 | 4
43 | 1
44 (3 rows)
46 -- Update a row from PK TABLE
47 UPDATE PKTABLE SET ptest1=1 WHERE ptest1=2;
48 -- Check FKTABLE for update of matched row
49 SELECT * FROM FKTABLE;
50 ftest1 | ftest2
51 --------+--------
52 3 | 4
53 | 1
54 1 | 3
55 (3 rows)
57 DROP TABLE FKTABLE;
58 DROP TABLE PKTABLE;
59 --
60 -- check set NULL and table constraint on multiple columns
61 --
62 CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 text, PRIMARY KEY(ptest1, ptest2) );
63 CREATE TABLE FKTABLE ( ftest1 int, ftest2 int, ftest3 int, CONSTRAINT constrname FOREIGN KEY(ftest1, ftest2)
64 REFERENCES PKTABLE MATCH FULL ON DELETE SET NULL ON UPDATE SET NULL);
65 -- Test comments
66 COMMENT ON CONSTRAINT constrname_wrong ON FKTABLE IS 'fk constraint comment';
67 ERROR: constraint "constrname_wrong" for table "fktable" does not exist
68 COMMENT ON CONSTRAINT constrname ON FKTABLE IS 'fk constraint comment';
69 COMMENT ON CONSTRAINT constrname ON FKTABLE IS NULL;
70 -- Insert test data into PKTABLE
71 INSERT INTO PKTABLE VALUES (1, 2, 'Test1');
72 INSERT INTO PKTABLE VALUES (1, 3, 'Test1-2');
73 INSERT INTO PKTABLE VALUES (2, 4, 'Test2');
74 INSERT INTO PKTABLE VALUES (3, 6, 'Test3');
75 INSERT INTO PKTABLE VALUES (4, 8, 'Test4');
76 INSERT INTO PKTABLE VALUES (5, 10, 'Test5');
77 -- Insert successful rows into FK TABLE
78 INSERT INTO FKTABLE VALUES (1, 2, 4);
79 INSERT INTO FKTABLE VALUES (1, 3, 5);
80 INSERT INTO FKTABLE VALUES (2, 4, 8);
81 INSERT INTO FKTABLE VALUES (3, 6, 12);
82 INSERT INTO FKTABLE VALUES (NULL, NULL, 0);
83 -- Insert failed rows into FK TABLE
84 INSERT INTO FKTABLE VALUES (100, 2, 4);
85 ERROR: insert or update on table "fktable" violates foreign key constraint "constrname"
86 DETAIL: Key (ftest1, ftest2)=(100, 2) is not present in table "pktable".
87 INSERT INTO FKTABLE VALUES (2, 2, 4);
88 ERROR: insert or update on table "fktable" violates foreign key constraint "constrname"
89 DETAIL: Key (ftest1, ftest2)=(2, 2) is not present in table "pktable".
90 INSERT INTO FKTABLE VALUES (NULL, 2, 4);
91 ERROR: insert or update on table "fktable" violates foreign key constraint "constrname"
92 DETAIL: MATCH FULL does not allow mixing of null and nonnull key values.
93 INSERT INTO FKTABLE VALUES (1, NULL, 4);
94 ERROR: insert or update on table "fktable" violates foreign key constraint "constrname"
95 DETAIL: MATCH FULL does not allow mixing of null and nonnull key values.
96 -- Check FKTABLE
97 SELECT * FROM FKTABLE;
98 ftest1 | ftest2 | ftest3
99 --------+--------+--------
100 1 | 2 | 4
101 1 | 3 | 5
102 2 | 4 | 8
103 3 | 6 | 12
104 | | 0
105 (5 rows)
107 -- Delete a row from PK TABLE
108 DELETE FROM PKTABLE WHERE ptest1=1 and ptest2=2;
109 -- Check FKTABLE for removal of matched row
110 SELECT * FROM FKTABLE;
111 ftest1 | ftest2 | ftest3
112 --------+--------+--------
113 1 | 3 | 5
114 2 | 4 | 8
115 3 | 6 | 12
116 | | 0
117 | | 4
118 (5 rows)
120 -- Delete another row from PK TABLE
121 DELETE FROM PKTABLE WHERE ptest1=5 and ptest2=10;
122 -- Check FKTABLE (should be no change)
123 SELECT * FROM FKTABLE;
124 ftest1 | ftest2 | ftest3
125 --------+--------+--------
126 1 | 3 | 5
127 2 | 4 | 8
128 3 | 6 | 12
129 | | 0
130 | | 4
131 (5 rows)
133 -- Update a row from PK TABLE
134 UPDATE PKTABLE SET ptest1=1 WHERE ptest1=2;
135 -- Check FKTABLE for update of matched row
136 SELECT * FROM FKTABLE;
137 ftest1 | ftest2 | ftest3
138 --------+--------+--------
139 1 | 3 | 5
140 3 | 6 | 12
141 | | 0
142 | | 4
143 | | 8
144 (5 rows)
146 -- Check update with part of key null
147 UPDATE FKTABLE SET ftest1 = NULL WHERE ftest1 = 1;
148 ERROR: insert or update on table "fktable" violates foreign key constraint "constrname"
149 DETAIL: MATCH FULL does not allow mixing of null and nonnull key values.
150 -- Check update with old and new key values equal
151 UPDATE FKTABLE SET ftest1 = 1 WHERE ftest1 = 1;
152 -- Try altering the column type where foreign keys are involved
153 ALTER TABLE PKTABLE ALTER COLUMN ptest1 TYPE bigint;
154 ALTER TABLE FKTABLE ALTER COLUMN ftest1 TYPE bigint;
155 SELECT * FROM PKTABLE;
156 ptest1 | ptest2 | ptest3
157 --------+--------+---------
158 1 | 3 | Test1-2
159 3 | 6 | Test3
160 4 | 8 | Test4
161 1 | 4 | Test2
162 (4 rows)
164 SELECT * FROM FKTABLE;
165 ftest1 | ftest2 | ftest3
166 --------+--------+--------
167 3 | 6 | 12
168 | | 0
169 | | 4
170 | | 8
171 1 | 3 | 5
172 (5 rows)
174 DROP TABLE PKTABLE CASCADE;
175 NOTICE: drop cascades to constraint constrname on table fktable
176 DROP TABLE FKTABLE;
177 --
178 -- check set default and table constraint on multiple columns
179 --
180 CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 text, PRIMARY KEY(ptest1, ptest2) );
181 CREATE TABLE FKTABLE ( ftest1 int DEFAULT -1, ftest2 int DEFAULT -2, ftest3 int, CONSTRAINT constrname2 FOREIGN KEY(ftest1, ftest2)
182 REFERENCES PKTABLE MATCH FULL ON DELETE SET DEFAULT ON UPDATE SET DEFAULT);
183 -- Insert a value in PKTABLE for default
184 INSERT INTO PKTABLE VALUES (-1, -2, 'The Default!');
185 -- Insert test data into PKTABLE
186 INSERT INTO PKTABLE VALUES (1, 2, 'Test1');
187 INSERT INTO PKTABLE VALUES (1, 3, 'Test1-2');
188 INSERT INTO PKTABLE VALUES (2, 4, 'Test2');
189 INSERT INTO PKTABLE VALUES (3, 6, 'Test3');
190 INSERT INTO PKTABLE VALUES (4, 8, 'Test4');
191 INSERT INTO PKTABLE VALUES (5, 10, 'Test5');
192 -- Insert successful rows into FK TABLE
193 INSERT INTO FKTABLE VALUES (1, 2, 4);
194 INSERT INTO FKTABLE VALUES (1, 3, 5);
195 INSERT INTO FKTABLE VALUES (2, 4, 8);
196 INSERT INTO FKTABLE VALUES (3, 6, 12);
197 INSERT INTO FKTABLE VALUES (NULL, NULL, 0);
198 -- Insert failed rows into FK TABLE
199 INSERT INTO FKTABLE VALUES (100, 2, 4);
200 ERROR: insert or update on table "fktable" violates foreign key constraint "constrname2"
201 DETAIL: Key (ftest1, ftest2)=(100, 2) is not present in table "pktable".
202 INSERT INTO FKTABLE VALUES (2, 2, 4);
203 ERROR: insert or update on table "fktable" violates foreign key constraint "constrname2"
204 DETAIL: Key (ftest1, ftest2)=(2, 2) is not present in table "pktable".
205 INSERT INTO FKTABLE VALUES (NULL, 2, 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.
208 INSERT INTO FKTABLE VALUES (1, NULL, 4);
209 ERROR: insert or update on table "fktable" violates foreign key constraint "constrname2"
210 DETAIL: MATCH FULL does not allow mixing of null and nonnull key values.
211 -- Check FKTABLE
212 SELECT * FROM FKTABLE;
213 ftest1 | ftest2 | ftest3
214 --------+--------+--------
215 1 | 2 | 4
216 1 | 3 | 5
217 2 | 4 | 8
218 3 | 6 | 12
219 | | 0
220 (5 rows)
222 -- Delete a row from PK TABLE
223 DELETE FROM PKTABLE WHERE ptest1=1 and ptest2=2;
224 -- Check FKTABLE to check for removal
225 SELECT * FROM FKTABLE;
226 ftest1 | ftest2 | ftest3
227 --------+--------+--------
228 1 | 3 | 5
229 2 | 4 | 8
230 3 | 6 | 12
231 | | 0
232 -1 | -2 | 4
233 (5 rows)
235 -- Delete another row from PK TABLE
236 DELETE FROM PKTABLE WHERE ptest1=5 and ptest2=10;
237 -- Check FKTABLE (should be no change)
238 SELECT * FROM FKTABLE;
239 ftest1 | ftest2 | ftest3
240 --------+--------+--------
241 1 | 3 | 5
242 2 | 4 | 8
243 3 | 6 | 12
244 | | 0
245 -1 | -2 | 4
246 (5 rows)
248 -- Update a row from PK TABLE
249 UPDATE PKTABLE SET ptest1=1 WHERE ptest1=2;
250 -- Check FKTABLE for update of matched row
251 SELECT * FROM FKTABLE;
252 ftest1 | ftest2 | ftest3
253 --------+--------+--------
254 1 | 3 | 5
255 3 | 6 | 12
256 | | 0
257 -1 | -2 | 4
258 -1 | -2 | 8
259 (5 rows)
261 -- this should fail for lack of CASCADE
262 DROP TABLE PKTABLE;
263 ERROR: cannot drop table pktable because other objects depend on it
264 DETAIL: constraint constrname2 on table fktable depends on table pktable
265 HINT: Use DROP ... CASCADE to drop the dependent objects too.
266 DROP TABLE PKTABLE CASCADE;
267 NOTICE: drop cascades to constraint constrname2 on table fktable
268 DROP TABLE FKTABLE;
269 --
270 -- First test, check with no on delete or on update
271 --
272 CREATE TABLE PKTABLE ( ptest1 int PRIMARY KEY, ptest2 text );
273 CREATE TABLE FKTABLE ( ftest1 int REFERENCES PKTABLE MATCH FULL, ftest2 int );
274 -- Insert test data into PKTABLE
275 INSERT INTO PKTABLE VALUES (1, 'Test1');
276 INSERT INTO PKTABLE VALUES (2, 'Test2');
277 INSERT INTO PKTABLE VALUES (3, 'Test3');
278 INSERT INTO PKTABLE VALUES (4, 'Test4');
279 INSERT INTO PKTABLE VALUES (5, 'Test5');
280 -- Insert successful rows into FK TABLE
281 INSERT INTO FKTABLE VALUES (1, 2);
282 INSERT INTO FKTABLE VALUES (2, 3);
283 INSERT INTO FKTABLE VALUES (3, 4);
284 INSERT INTO FKTABLE VALUES (NULL, 1);
285 -- Insert a failed row into FK TABLE
286 INSERT INTO FKTABLE VALUES (100, 2);
287 ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_fkey"
288 DETAIL: Key (ftest1)=(100) is not present in table "pktable".
289 -- Check FKTABLE
290 SELECT * FROM FKTABLE;
291 ftest1 | ftest2
292 --------+--------
293 1 | 2
294 2 | 3
295 3 | 4
296 | 1
297 (4 rows)
299 -- Check PKTABLE
300 SELECT * FROM PKTABLE;
301 ptest1 | ptest2
302 --------+--------
303 1 | Test1
304 2 | Test2
305 3 | Test3
306 4 | Test4
307 5 | Test5
308 (5 rows)
310 -- Delete a row from PK TABLE (should fail)
311 DELETE FROM PKTABLE WHERE ptest1=1;
312 ERROR: update or delete on table "pktable" violates foreign key constraint "fktable_ftest1_fkey" on table "fktable"
313 DETAIL: Key (ptest1)=(1) is still referenced from table "fktable".
314 -- Delete a row from PK TABLE (should succeed)
315 DELETE FROM PKTABLE WHERE ptest1=5;
316 -- Check PKTABLE for deletes
317 SELECT * FROM PKTABLE;
318 ptest1 | ptest2
319 --------+--------
320 1 | Test1
321 2 | Test2
322 3 | Test3
323 4 | Test4
324 (4 rows)
326 -- Update a row from PK TABLE (should fail)
327 UPDATE PKTABLE SET ptest1=0 WHERE ptest1=2;
328 ERROR: update or delete on table "pktable" violates foreign key constraint "fktable_ftest1_fkey" on table "fktable"
329 DETAIL: Key (ptest1)=(2) is still referenced from table "fktable".
330 -- Update a row from PK TABLE (should succeed)
331 UPDATE PKTABLE SET ptest1=0 WHERE ptest1=4;
332 -- Check PKTABLE for updates
333 SELECT * FROM PKTABLE;
334 ptest1 | ptest2
335 --------+--------
336 1 | Test1
337 2 | Test2
338 3 | Test3
339 0 | Test4
340 (4 rows)
342 DROP TABLE FKTABLE;
343 DROP TABLE PKTABLE;
344 --
345 -- Check initial check upon ALTER TABLE
346 --
347 CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, PRIMARY KEY(ptest1, ptest2) );
348 CREATE TABLE FKTABLE ( ftest1 int, ftest2 int );
349 INSERT INTO PKTABLE VALUES (1, 2);
350 INSERT INTO FKTABLE VALUES (1, NULL);
351 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) REFERENCES PKTABLE MATCH FULL;
352 ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_ftest2_fkey"
353 DETAIL: MATCH FULL does not allow mixing of null and nonnull key values.
354 DROP TABLE FKTABLE;
355 DROP TABLE PKTABLE;
356 -- MATCH SIMPLE
357 -- Base test restricting update/delete
358 CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2, ptest3) );
359 CREATE TABLE FKTABLE ( ftest1 int, ftest2 int, ftest3 int, ftest4 int, CONSTRAINT constrname3
360 FOREIGN KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE);
361 -- Insert Primary Key values
362 INSERT INTO PKTABLE VALUES (1, 2, 3, 'test1');
363 INSERT INTO PKTABLE VALUES (1, 3, 3, 'test2');
364 INSERT INTO PKTABLE VALUES (2, 3, 4, 'test3');
365 INSERT INTO PKTABLE VALUES (2, 4, 5, 'test4');
366 -- Insert Foreign Key values
367 INSERT INTO FKTABLE VALUES (1, 2, 3, 1);
368 INSERT INTO FKTABLE VALUES (NULL, 2, 3, 2);
369 INSERT INTO FKTABLE VALUES (2, NULL, 3, 3);
370 INSERT INTO FKTABLE VALUES (NULL, 2, 7, 4);
371 INSERT INTO FKTABLE VALUES (NULL, 3, 4, 5);
372 -- Insert a failed values
373 INSERT INTO FKTABLE VALUES (1, 2, 7, 6);
374 ERROR: insert or update on table "fktable" violates foreign key constraint "constrname3"
375 DETAIL: Key (ftest1, ftest2, ftest3)=(1, 2, 7) is not present in table "pktable".
376 -- Show FKTABLE
377 SELECT * from FKTABLE;
378 ftest1 | ftest2 | ftest3 | ftest4
379 --------+--------+--------+--------
380 1 | 2 | 3 | 1
381 | 2 | 3 | 2
382 2 | | 3 | 3
383 | 2 | 7 | 4
384 | 3 | 4 | 5
385 (5 rows)
387 -- Try to update something that should fail
388 UPDATE PKTABLE set ptest2=5 where ptest2=2;
389 ERROR: update or delete on table "pktable" violates foreign key constraint "constrname3" on table "fktable"
390 DETAIL: Key (ptest1, ptest2, ptest3)=(1, 2, 3) is still referenced from table "fktable".
391 -- Try to update something that should succeed
392 UPDATE PKTABLE set ptest1=1 WHERE ptest2=3;
393 -- Try to delete something that should fail
394 DELETE FROM PKTABLE where ptest1=1 and ptest2=2 and ptest3=3;
395 ERROR: update or delete on table "pktable" violates foreign key constraint "constrname3" on table "fktable"
396 DETAIL: Key (ptest1, ptest2, ptest3)=(1, 2, 3) is still referenced from table "fktable".
397 -- Try to delete something that should work
398 DELETE FROM PKTABLE where ptest1=2;
399 -- Show PKTABLE and FKTABLE
400 SELECT * from PKTABLE;
401 ptest1 | ptest2 | ptest3 | ptest4
402 --------+--------+--------+--------
403 1 | 2 | 3 | test1
404 1 | 3 | 3 | test2
405 1 | 3 | 4 | test3
406 (3 rows)
408 SELECT * from FKTABLE;
409 ftest1 | ftest2 | ftest3 | ftest4
410 --------+--------+--------+--------
411 1 | 2 | 3 | 1
412 | 2 | 3 | 2
413 2 | | 3 | 3
414 | 2 | 7 | 4
415 | 3 | 4 | 5
416 (5 rows)
418 DROP TABLE FKTABLE;
419 DROP TABLE PKTABLE;
420 -- restrict with null values
421 CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, UNIQUE(ptest1, ptest2, ptest3) );
422 CREATE TABLE FKTABLE ( ftest1 int, ftest2 int, ftest3 int, ftest4 int, CONSTRAINT constrname3
423 FOREIGN KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE (ptest1, ptest2, ptest3));
424 INSERT INTO PKTABLE VALUES (1, 2, 3, 'test1');
425 INSERT INTO PKTABLE VALUES (1, 3, NULL, 'test2');
426 INSERT INTO PKTABLE VALUES (2, NULL, 4, 'test3');
427 INSERT INTO FKTABLE VALUES (1, 2, 3, 1);
428 DELETE FROM PKTABLE WHERE ptest1 = 2;
429 SELECT * FROM PKTABLE;
430 ptest1 | ptest2 | ptest3 | ptest4
431 --------+--------+--------+--------
432 1 | 2 | 3 | test1
433 1 | 3 | | test2
434 (2 rows)
436 SELECT * FROM FKTABLE;
437 ftest1 | ftest2 | ftest3 | ftest4
438 --------+--------+--------+--------
439 1 | 2 | 3 | 1
440 (1 row)
442 DROP TABLE FKTABLE;
443 DROP TABLE PKTABLE;
444 -- cascade update/delete
445 CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2, ptest3) );
446 CREATE TABLE FKTABLE ( ftest1 int, ftest2 int, ftest3 int, ftest4 int, CONSTRAINT constrname3
447 FOREIGN KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE
448 ON DELETE CASCADE ON UPDATE CASCADE);
449 -- Insert Primary Key values
450 INSERT INTO PKTABLE VALUES (1, 2, 3, 'test1');
451 INSERT INTO PKTABLE VALUES (1, 3, 3, 'test2');
452 INSERT INTO PKTABLE VALUES (2, 3, 4, 'test3');
453 INSERT INTO PKTABLE VALUES (2, 4, 5, 'test4');
454 -- Insert Foreign Key values
455 INSERT INTO FKTABLE VALUES (1, 2, 3, 1);
456 INSERT INTO FKTABLE VALUES (NULL, 2, 3, 2);
457 INSERT INTO FKTABLE VALUES (2, NULL, 3, 3);
458 INSERT INTO FKTABLE VALUES (NULL, 2, 7, 4);
459 INSERT INTO FKTABLE VALUES (NULL, 3, 4, 5);
460 -- Insert a failed values
461 INSERT INTO FKTABLE VALUES (1, 2, 7, 6);
462 ERROR: insert or update on table "fktable" violates foreign key constraint "constrname3"
463 DETAIL: Key (ftest1, ftest2, ftest3)=(1, 2, 7) is not present in table "pktable".
464 -- Show FKTABLE
465 SELECT * from FKTABLE;
466 ftest1 | ftest2 | ftest3 | ftest4
467 --------+--------+--------+--------
468 1 | 2 | 3 | 1
469 | 2 | 3 | 2
470 2 | | 3 | 3
471 | 2 | 7 | 4
472 | 3 | 4 | 5
473 (5 rows)
475 -- Try to update something that will cascade
476 UPDATE PKTABLE set ptest2=5 where ptest2=2;
477 -- Try to update something that should not cascade
478 UPDATE PKTABLE set ptest1=1 WHERE ptest2=3;
479 -- Show PKTABLE and FKTABLE
480 SELECT * from PKTABLE;
481 ptest1 | ptest2 | ptest3 | ptest4
482 --------+--------+--------+--------
483 2 | 4 | 5 | test4
484 1 | 5 | 3 | test1
485 1 | 3 | 3 | test2
486 1 | 3 | 4 | test3
487 (4 rows)
489 SELECT * from FKTABLE;
490 ftest1 | ftest2 | ftest3 | ftest4
491 --------+--------+--------+--------
492 | 2 | 3 | 2
493 2 | | 3 | 3
494 | 2 | 7 | 4
495 | 3 | 4 | 5
496 1 | 5 | 3 | 1
497 (5 rows)
499 -- Try to delete something that should cascade
500 DELETE FROM PKTABLE where ptest1=1 and ptest2=5 and ptest3=3;
501 -- Show PKTABLE and FKTABLE
502 SELECT * from PKTABLE;
503 ptest1 | ptest2 | ptest3 | ptest4
504 --------+--------+--------+--------
505 2 | 4 | 5 | test4
506 1 | 3 | 3 | test2
507 1 | 3 | 4 | test3
508 (3 rows)
510 SELECT * from FKTABLE;
511 ftest1 | ftest2 | ftest3 | ftest4
512 --------+--------+--------+--------
513 | 2 | 3 | 2
514 2 | | 3 | 3
515 | 2 | 7 | 4
516 | 3 | 4 | 5
517 (4 rows)
519 -- Try to delete something that should not have a cascade
520 DELETE FROM PKTABLE where ptest1=2;
521 -- Show PKTABLE and FKTABLE
522 SELECT * from PKTABLE;
523 ptest1 | ptest2 | ptest3 | ptest4
524 --------+--------+--------+--------
525 1 | 3 | 3 | test2
526 1 | 3 | 4 | test3
527 (2 rows)
529 SELECT * from FKTABLE;
530 ftest1 | ftest2 | ftest3 | ftest4
531 --------+--------+--------+--------
532 | 2 | 3 | 2
533 2 | | 3 | 3
534 | 2 | 7 | 4
535 | 3 | 4 | 5
536 (4 rows)
538 DROP TABLE FKTABLE;
539 DROP TABLE PKTABLE;
540 -- set null update / set default delete
541 CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2, ptest3) );
542 CREATE TABLE FKTABLE ( ftest1 int DEFAULT 0, ftest2 int, ftest3 int, ftest4 int, CONSTRAINT constrname3
543 FOREIGN KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE
544 ON DELETE SET DEFAULT ON UPDATE SET NULL);
545 -- Insert Primary Key values
546 INSERT INTO PKTABLE VALUES (1, 2, 3, 'test1');
547 INSERT INTO PKTABLE VALUES (1, 3, 3, 'test2');
548 INSERT INTO PKTABLE VALUES (2, 3, 4, 'test3');
549 INSERT INTO PKTABLE VALUES (2, 4, 5, 'test4');
550 -- Insert Foreign Key values
551 INSERT INTO FKTABLE VALUES (1, 2, 3, 1);
552 INSERT INTO FKTABLE VALUES (2, 3, 4, 1);
553 INSERT INTO FKTABLE VALUES (NULL, 2, 3, 2);
554 INSERT INTO FKTABLE VALUES (2, NULL, 3, 3);
555 INSERT INTO FKTABLE VALUES (NULL, 2, 7, 4);
556 INSERT INTO FKTABLE VALUES (NULL, 3, 4, 5);
557 -- Insert a failed values
558 INSERT INTO FKTABLE VALUES (1, 2, 7, 6);
559 ERROR: insert or update on table "fktable" violates foreign key constraint "constrname3"
560 DETAIL: Key (ftest1, ftest2, ftest3)=(1, 2, 7) is not present in table "pktable".
561 -- Show FKTABLE
562 SELECT * from FKTABLE;
563 ftest1 | ftest2 | ftest3 | ftest4
564 --------+--------+--------+--------
565 1 | 2 | 3 | 1
566 2 | 3 | 4 | 1
567 | 2 | 3 | 2
568 2 | | 3 | 3
569 | 2 | 7 | 4
570 | 3 | 4 | 5
571 (6 rows)
573 -- Try to update something that will set null
574 UPDATE PKTABLE set ptest2=5 where ptest2=2;
575 -- Try to update something that should not set null
576 UPDATE PKTABLE set ptest2=2 WHERE ptest2=3 and ptest1=1;
577 -- Show PKTABLE and FKTABLE
578 SELECT * from PKTABLE;
579 ptest1 | ptest2 | ptest3 | ptest4
580 --------+--------+--------+--------
581 2 | 3 | 4 | test3
582 2 | 4 | 5 | test4
583 1 | 5 | 3 | test1
584 1 | 2 | 3 | test2
585 (4 rows)
587 SELECT * from FKTABLE;
588 ftest1 | ftest2 | ftest3 | ftest4
589 --------+--------+--------+--------
590 2 | 3 | 4 | 1
591 | 2 | 3 | 2
592 2 | | 3 | 3
593 | 2 | 7 | 4
594 | 3 | 4 | 5
595 | | | 1
596 (6 rows)
598 -- Try to delete something that should set default
599 DELETE FROM PKTABLE where ptest1=2 and ptest2=3 and ptest3=4;
600 -- Show PKTABLE and FKTABLE
601 SELECT * from PKTABLE;
602 ptest1 | ptest2 | ptest3 | ptest4
603 --------+--------+--------+--------
604 2 | 4 | 5 | test4
605 1 | 5 | 3 | test1
606 1 | 2 | 3 | test2
607 (3 rows)
609 SELECT * from FKTABLE;
610 ftest1 | ftest2 | ftest3 | ftest4
611 --------+--------+--------+--------
612 | 2 | 3 | 2
613 2 | | 3 | 3
614 | 2 | 7 | 4
615 | 3 | 4 | 5
616 | | | 1
617 0 | | | 1
618 (6 rows)
620 -- Try to delete something that should not set default
621 DELETE FROM PKTABLE where ptest2=5;
622 -- Show PKTABLE and FKTABLE
623 SELECT * from PKTABLE;
624 ptest1 | ptest2 | ptest3 | ptest4
625 --------+--------+--------+--------
626 2 | 4 | 5 | test4
627 1 | 2 | 3 | test2
628 (2 rows)
630 SELECT * from FKTABLE;
631 ftest1 | ftest2 | ftest3 | ftest4
632 --------+--------+--------+--------
633 | 2 | 3 | 2
634 2 | | 3 | 3
635 | 2 | 7 | 4
636 | 3 | 4 | 5
637 | | | 1
638 0 | | | 1
639 (6 rows)
641 DROP TABLE FKTABLE;
642 DROP TABLE PKTABLE;
643 -- set default update / set null delete
644 CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2, ptest3) );
645 CREATE TABLE FKTABLE ( ftest1 int DEFAULT 0, ftest2 int DEFAULT -1, ftest3 int DEFAULT -2, ftest4 int, CONSTRAINT constrname3
646 FOREIGN KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE
647 ON DELETE SET NULL ON UPDATE SET DEFAULT);
648 -- Insert Primary Key values
649 INSERT INTO PKTABLE VALUES (1, 2, 3, 'test1');
650 INSERT INTO PKTABLE VALUES (1, 3, 3, 'test2');
651 INSERT INTO PKTABLE VALUES (2, 3, 4, 'test3');
652 INSERT INTO PKTABLE VALUES (2, 4, 5, 'test4');
653 INSERT INTO PKTABLE VALUES (2, -1, 5, 'test5');
654 -- Insert Foreign Key values
655 INSERT INTO FKTABLE VALUES (1, 2, 3, 1);
656 INSERT INTO FKTABLE VALUES (2, 3, 4, 1);
657 INSERT INTO FKTABLE VALUES (2, 4, 5, 1);
658 INSERT INTO FKTABLE VALUES (NULL, 2, 3, 2);
659 INSERT INTO FKTABLE VALUES (2, NULL, 3, 3);
660 INSERT INTO FKTABLE VALUES (NULL, 2, 7, 4);
661 INSERT INTO FKTABLE VALUES (NULL, 3, 4, 5);
662 -- Insert a failed values
663 INSERT INTO FKTABLE VALUES (1, 2, 7, 6);
664 ERROR: insert or update on table "fktable" violates foreign key constraint "constrname3"
665 DETAIL: Key (ftest1, ftest2, ftest3)=(1, 2, 7) is not present in table "pktable".
666 -- Show FKTABLE
667 SELECT * from FKTABLE;
668 ftest1 | ftest2 | ftest3 | ftest4
669 --------+--------+--------+--------
670 1 | 2 | 3 | 1
671 2 | 3 | 4 | 1
672 2 | 4 | 5 | 1
673 | 2 | 3 | 2
674 2 | | 3 | 3
675 | 2 | 7 | 4
676 | 3 | 4 | 5
677 (7 rows)
679 -- Try to update something that will fail
680 UPDATE PKTABLE set ptest2=5 where ptest2=2;
681 ERROR: insert or update on table "fktable" violates foreign key constraint "constrname3"
682 DETAIL: Key (ftest1, ftest2, ftest3)=(0, -1, -2) is not present in table "pktable".
683 -- Try to update something that will set default
684 UPDATE PKTABLE set ptest1=0, ptest2=-1, ptest3=-2 where ptest2=2;
685 UPDATE PKTABLE set ptest2=10 where ptest2=4;
686 -- Try to update something that should not set default
687 UPDATE PKTABLE set ptest2=2 WHERE ptest2=3 and ptest1=1;
688 -- Show PKTABLE and FKTABLE
689 SELECT * from PKTABLE;
690 ptest1 | ptest2 | ptest3 | ptest4
691 --------+--------+--------+--------
692 2 | 3 | 4 | test3
693 2 | -1 | 5 | test5
694 0 | -1 | -2 | test1
695 2 | 10 | 5 | test4
696 1 | 2 | 3 | test2
697 (5 rows)
699 SELECT * from FKTABLE;
700 ftest1 | ftest2 | ftest3 | ftest4
701 --------+--------+--------+--------
702 2 | 3 | 4 | 1
703 | 2 | 3 | 2
704 2 | | 3 | 3
705 | 2 | 7 | 4
706 | 3 | 4 | 5
707 0 | -1 | -2 | 1
708 0 | -1 | -2 | 1
709 (7 rows)
711 -- Try to delete something that should set null
712 DELETE FROM PKTABLE where ptest1=2 and ptest2=3 and ptest3=4;
713 -- Show PKTABLE and FKTABLE
714 SELECT * from PKTABLE;
715 ptest1 | ptest2 | ptest3 | ptest4
716 --------+--------+--------+--------
717 2 | -1 | 5 | test5
718 0 | -1 | -2 | test1
719 2 | 10 | 5 | test4
720 1 | 2 | 3 | test2
721 (4 rows)
723 SELECT * from FKTABLE;
724 ftest1 | ftest2 | ftest3 | ftest4
725 --------+--------+--------+--------
726 | 2 | 3 | 2
727 2 | | 3 | 3
728 | 2 | 7 | 4
729 | 3 | 4 | 5
730 0 | -1 | -2 | 1
731 0 | -1 | -2 | 1
732 | | | 1
733 (7 rows)
735 -- Try to delete something that should not set null
736 DELETE FROM PKTABLE where ptest2=-1 and ptest3=5;
737 -- Show PKTABLE and FKTABLE
738 SELECT * from PKTABLE;
739 ptest1 | ptest2 | ptest3 | ptest4
740 --------+--------+--------+--------
741 0 | -1 | -2 | test1
742 2 | 10 | 5 | test4
743 1 | 2 | 3 | test2
744 (3 rows)
746 SELECT * from FKTABLE;
747 ftest1 | ftest2 | ftest3 | ftest4
748 --------+--------+--------+--------
749 | 2 | 3 | 2
750 2 | | 3 | 3
751 | 2 | 7 | 4
752 | 3 | 4 | 5
753 0 | -1 | -2 | 1
754 0 | -1 | -2 | 1
755 | | | 1
756 (7 rows)
758 DROP TABLE FKTABLE;
759 DROP TABLE PKTABLE;
760 -- Test for ON DELETE SET NULL/DEFAULT (column_list);
761 CREATE TABLE PKTABLE (tid int, id int, PRIMARY KEY (tid, id));
762 CREATE TABLE FKTABLE (tid int, id int, foo int, FOREIGN KEY (tid, id) REFERENCES PKTABLE ON DELETE SET NULL (bar));
763 ERROR: column "bar" referenced in foreign key constraint does not exist
764 CREATE TABLE FKTABLE (tid int, id int, foo int, FOREIGN KEY (tid, id) REFERENCES PKTABLE ON DELETE SET NULL (foo));
765 ERROR: column "foo" referenced in ON DELETE SET action must be part of foreign key
766 CREATE TABLE FKTABLE (tid int, id int, foo int, FOREIGN KEY (tid, foo) REFERENCES PKTABLE ON UPDATE SET NULL (foo));
767 ERROR: a column list with SET NULL is only supported for ON DELETE actions
768 LINE 1: ...oo int, FOREIGN KEY (tid, foo) REFERENCES PKTABLE ON UPDATE ...
769 ^
770 CREATE TABLE FKTABLE (
771 tid int, id int,
772 fk_id_del_set_null int,
773 fk_id_del_set_default int DEFAULT 0,
774 FOREIGN KEY (tid, fk_id_del_set_null) REFERENCES PKTABLE ON DELETE SET NULL (fk_id_del_set_null),
775 FOREIGN KEY (tid, fk_id_del_set_default) REFERENCES PKTABLE ON DELETE SET DEFAULT (fk_id_del_set_default)
776 );
777 SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'fktable'::regclass::oid ORDER BY oid;
778 pg_get_constraintdef
779 --------------------------------------------------------------------------------------------------------------------
780 FOREIGN KEY (tid, fk_id_del_set_null) REFERENCES pktable(tid, id) ON DELETE SET NULL (fk_id_del_set_null)
781 FOREIGN KEY (tid, fk_id_del_set_default) REFERENCES pktable(tid, id) ON DELETE SET DEFAULT (fk_id_del_set_default)
782 (2 rows)
784 INSERT INTO PKTABLE VALUES (1, 0), (1, 1), (1, 2);
785 INSERT INTO FKTABLE VALUES
786 (1, 1, 1, NULL),
787 (1, 2, NULL, 2);
788 DELETE FROM PKTABLE WHERE id = 1 OR id = 2;
789 SELECT * FROM FKTABLE ORDER BY id;
790 tid | id | fk_id_del_set_null | fk_id_del_set_default
791 -----+----+--------------------+-----------------------
792 1 | 1 | |
793 1 | 2 | | 0
794 (2 rows)
796 DROP TABLE FKTABLE;
797 DROP TABLE PKTABLE;
798 -- Test some invalid FK definitions
799 CREATE TABLE PKTABLE (ptest1 int PRIMARY KEY, someoid oid);
800 CREATE TABLE FKTABLE_FAIL1 ( ftest1 int, CONSTRAINT fkfail1 FOREIGN KEY (ftest2) REFERENCES PKTABLE);
801 ERROR: column "ftest2" referenced in foreign key constraint does not exist
802 CREATE TABLE FKTABLE_FAIL2 ( ftest1 int, CONSTRAINT fkfail1 FOREIGN KEY (ftest1) REFERENCES PKTABLE(ptest2));
803 ERROR: column "ptest2" referenced in foreign key constraint does not exist
804 CREATE TABLE FKTABLE_FAIL3 ( ftest1 int, CONSTRAINT fkfail1 FOREIGN KEY (tableoid) REFERENCES PKTABLE(someoid));
805 ERROR: system columns cannot be used in foreign keys
806 CREATE TABLE FKTABLE_FAIL4 ( ftest1 oid, CONSTRAINT fkfail1 FOREIGN KEY (ftest1) REFERENCES PKTABLE(tableoid));
807 ERROR: system columns cannot be used in foreign keys
808 DROP TABLE PKTABLE;
809 -- Test for referencing column number smaller than referenced constraint
810 CREATE TABLE PKTABLE (ptest1 int, ptest2 int, UNIQUE(ptest1, ptest2));
811 CREATE TABLE FKTABLE_FAIL1 (ftest1 int REFERENCES pktable(ptest1));
812 ERROR: there is no unique constraint matching given keys for referenced table "pktable"
813 DROP TABLE FKTABLE_FAIL1;
814 ERROR: table "fktable_fail1" does not exist
815 DROP TABLE PKTABLE;
816 --
817 -- Tests for mismatched types
818 --
819 -- Basic one column, two table setup
820 CREATE TABLE PKTABLE (ptest1 int PRIMARY KEY);
821 INSERT INTO PKTABLE VALUES(42);
822 -- This next should fail, because int=inet does not exist
823 CREATE TABLE FKTABLE (ftest1 inet REFERENCES pktable);
824 ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented
825 DETAIL: Key columns "ftest1" of the referencing table and "ptest1" of the referenced table are of incompatible types: inet and integer.
826 -- This should also fail for the same reason, but here we
827 -- give the column name
828 CREATE TABLE FKTABLE (ftest1 inet REFERENCES pktable(ptest1));
829 ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented
830 DETAIL: Key columns "ftest1" of the referencing table and "ptest1" of the referenced table are of incompatible types: inet and integer.
831 -- This should succeed, even though they are different types,
832 -- because int=int8 exists and is a member of the integer opfamily
833 CREATE TABLE FKTABLE (ftest1 int8 REFERENCES pktable);
834 -- Check it actually works
835 INSERT INTO FKTABLE VALUES(42); -- should succeed
836 INSERT INTO FKTABLE VALUES(43); -- should fail
837 ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_fkey"
838 DETAIL: Key (ftest1)=(43) is not present in table "pktable".
839 UPDATE FKTABLE SET ftest1 = ftest1; -- should succeed
840 UPDATE FKTABLE SET ftest1 = ftest1 + 1; -- should fail
841 ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_fkey"
842 DETAIL: Key (ftest1)=(43) is not present in table "pktable".
843 DROP TABLE FKTABLE;
844 -- This should fail, because we'd have to cast numeric to int which is
845 -- not an implicit coercion (or use numeric=numeric, but that's not part
846 -- of the integer opfamily)
847 CREATE TABLE FKTABLE (ftest1 numeric REFERENCES pktable);
848 ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented
849 DETAIL: Key columns "ftest1" of the referencing table and "ptest1" of the referenced table are of incompatible types: numeric and integer.
850 DROP TABLE PKTABLE;
851 -- On the other hand, this should work because int implicitly promotes to
852 -- numeric, and we allow promotion on the FK side
853 CREATE TABLE PKTABLE (ptest1 numeric PRIMARY KEY);
854 INSERT INTO PKTABLE VALUES(42);
855 CREATE TABLE FKTABLE (ftest1 int REFERENCES pktable);
856 -- Check it actually works
857 INSERT INTO FKTABLE VALUES(42); -- should succeed
858 INSERT INTO FKTABLE VALUES(43); -- should fail
859 ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_fkey"
860 DETAIL: Key (ftest1)=(43) is not present in table "pktable".
861 UPDATE FKTABLE SET ftest1 = ftest1; -- should succeed
862 UPDATE FKTABLE SET ftest1 = ftest1 + 1; -- should fail
863 ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_fkey"
864 DETAIL: Key (ftest1)=(43) is not present in table "pktable".
865 DROP TABLE FKTABLE;
866 DROP TABLE PKTABLE;
867 -- Two columns, two tables
868 CREATE TABLE PKTABLE (ptest1 int, ptest2 inet, PRIMARY KEY(ptest1, ptest2));
869 -- This should fail, because we just chose really odd types
870 CREATE TABLE FKTABLE (ftest1 cidr, ftest2 timestamp, FOREIGN KEY(ftest1, ftest2) REFERENCES pktable);
871 ERROR: foreign key constraint "fktable_ftest1_ftest2_fkey" cannot be implemented
872 DETAIL: Key columns "ftest1" of the referencing table and "ptest1" of the referenced table are of incompatible types: cidr and integer.
873 -- Again, so should this...
874 CREATE TABLE FKTABLE (ftest1 cidr, ftest2 timestamp, FOREIGN KEY(ftest1, ftest2) REFERENCES pktable(ptest1, ptest2));
875 ERROR: foreign key constraint "fktable_ftest1_ftest2_fkey" cannot be implemented
876 DETAIL: Key columns "ftest1" of the referencing table and "ptest1" of the referenced table are of incompatible types: cidr and integer.
877 -- This fails because we mixed up the column ordering
878 CREATE TABLE FKTABLE (ftest1 int, ftest2 inet, FOREIGN KEY(ftest2, ftest1) REFERENCES pktable);
879 ERROR: foreign key constraint "fktable_ftest2_ftest1_fkey" cannot be implemented
880 DETAIL: Key columns "ftest2" of the referencing table and "ptest1" of the referenced table are of incompatible types: inet and integer.
881 -- As does this...
882 CREATE TABLE FKTABLE (ftest1 int, ftest2 inet, FOREIGN KEY(ftest2, ftest1) REFERENCES pktable(ptest1, ptest2));
883 ERROR: foreign key constraint "fktable_ftest2_ftest1_fkey" cannot be implemented
884 DETAIL: Key columns "ftest2" of the referencing table and "ptest1" of the referenced table are of incompatible types: inet and integer.
885 -- And again..
886 CREATE TABLE FKTABLE (ftest1 int, ftest2 inet, FOREIGN KEY(ftest1, ftest2) REFERENCES pktable(ptest2, ptest1));
887 ERROR: foreign key constraint "fktable_ftest1_ftest2_fkey" cannot be implemented
888 DETAIL: Key columns "ftest1" of the referencing table and "ptest2" of the referenced table are of incompatible types: integer and inet.
889 -- This works...
890 CREATE TABLE FKTABLE (ftest1 int, ftest2 inet, FOREIGN KEY(ftest2, ftest1) REFERENCES pktable(ptest2, ptest1));
891 DROP TABLE FKTABLE;
892 -- As does this
893 CREATE TABLE FKTABLE (ftest1 int, ftest2 inet, FOREIGN KEY(ftest1, ftest2) REFERENCES pktable(ptest1, ptest2));
894 DROP TABLE FKTABLE;
895 DROP TABLE PKTABLE;
896 -- Two columns, same table
897 -- Make sure this still works...
898 CREATE TABLE PKTABLE (ptest1 int, ptest2 inet, ptest3 int, ptest4 inet, PRIMARY KEY(ptest1, ptest2), FOREIGN KEY(ptest3,
899 ptest4) REFERENCES pktable(ptest1, ptest2));
900 DROP TABLE PKTABLE;
901 -- And this,
902 CREATE TABLE PKTABLE (ptest1 int, ptest2 inet, ptest3 int, ptest4 inet, PRIMARY KEY(ptest1, ptest2), FOREIGN KEY(ptest3,
903 ptest4) REFERENCES pktable);
904 DROP TABLE PKTABLE;
905 -- This shouldn't (mixed up columns)
906 CREATE TABLE PKTABLE (ptest1 int, ptest2 inet, ptest3 int, ptest4 inet, PRIMARY KEY(ptest1, ptest2), FOREIGN KEY(ptest3,
907 ptest4) REFERENCES pktable(ptest2, ptest1));
908 ERROR: foreign key constraint "pktable_ptest3_ptest4_fkey" cannot be implemented
909 DETAIL: Key columns "ptest3" of the referencing table and "ptest2" of the referenced table are of incompatible types: integer and inet.
910 -- Nor should this... (same reason, we have 4,3 referencing 1,2 which mismatches types
911 CREATE TABLE PKTABLE (ptest1 int, ptest2 inet, ptest3 int, ptest4 inet, PRIMARY KEY(ptest1, ptest2), FOREIGN KEY(ptest4,
912 ptest3) REFERENCES pktable(ptest1, ptest2));
913 ERROR: foreign key constraint "pktable_ptest4_ptest3_fkey" cannot be implemented
914 DETAIL: Key columns "ptest4" of the referencing table and "ptest1" of the referenced table are of incompatible types: inet and integer.
915 -- Not this one either... Same as the last one except we didn't defined the columns being referenced.
916 CREATE TABLE PKTABLE (ptest1 int, ptest2 inet, ptest3 int, ptest4 inet, PRIMARY KEY(ptest1, ptest2), FOREIGN KEY(ptest4,
917 ptest3) REFERENCES pktable);
918 ERROR: foreign key constraint "pktable_ptest4_ptest3_fkey" cannot be implemented
919 DETAIL: Key columns "ptest4" of the referencing table and "ptest1" of the referenced table are of incompatible types: inet and integer.
920 --
921 -- Now some cases with inheritance
922 -- Basic 2 table case: 1 column of matching types.
923 create table pktable_base (base1 int not null);
924 create table pktable (ptest1 int, primary key(base1), unique(base1, ptest1)) inherits (pktable_base);
925 create table fktable (ftest1 int references pktable(base1));
926 -- now some ins, upd, del
927 insert into pktable(base1) values (1);
928 insert into pktable(base1) values (2);
929 -- let's insert a non-existent fktable value
930 insert into fktable(ftest1) values (3);
931 ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_fkey"
932 DETAIL: Key (ftest1)=(3) is not present in table "pktable".
933 -- let's make a valid row for that
934 insert into pktable(base1) values (3);
935 insert into fktable(ftest1) values (3);
936 -- let's try removing a row that should fail from pktable
937 delete from pktable where base1>2;
938 ERROR: update or delete on table "pktable" violates foreign key constraint "fktable_ftest1_fkey" on table "fktable"
939 DETAIL: Key (base1)=(3) is still referenced from table "fktable".
940 -- okay, let's try updating all of the base1 values to *4
941 -- which should fail.
942 update pktable set base1=base1*4;
943 ERROR: update or delete on table "pktable" violates foreign key constraint "fktable_ftest1_fkey" on table "fktable"
944 DETAIL: Key (base1)=(3) is still referenced from table "fktable".
945 -- okay, let's try an update that should work.
946 update pktable set base1=base1*4 where base1<3;
947 -- and a delete that should work
948 delete from pktable where base1>3;
949 -- cleanup
950 drop table fktable;
951 delete from pktable;
952 -- Now 2 columns 2 tables, matching types
953 create table fktable (ftest1 int, ftest2 int, foreign key(ftest1, ftest2) references pktable(base1, ptest1));
954 -- now some ins, upd, del
955 insert into pktable(base1, ptest1) values (1, 1);
956 insert into pktable(base1, ptest1) values (2, 2);
957 -- let's insert a non-existent fktable value
958 insert into fktable(ftest1, ftest2) values (3, 1);
959 ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_ftest2_fkey"
960 DETAIL: Key (ftest1, ftest2)=(3, 1) is not present in table "pktable".
961 -- let's make a valid row for that
962 insert into pktable(base1,ptest1) values (3, 1);
963 insert into fktable(ftest1, ftest2) values (3, 1);
964 -- let's try removing a row that should fail from pktable
965 delete from pktable where base1>2;
966 ERROR: update or delete on table "pktable" violates foreign key constraint "fktable_ftest1_ftest2_fkey" on table "fktable"
967 DETAIL: Key (base1, ptest1)=(3, 1) is still referenced from table "fktable".
968 -- okay, let's try updating all of the base1 values to *4
969 -- which should fail.
970 update pktable set base1=base1*4;
971 ERROR: update or delete on table "pktable" violates foreign key constraint "fktable_ftest1_ftest2_fkey" on table "fktable"
972 DETAIL: Key (base1, ptest1)=(3, 1) is still referenced from table "fktable".
973 -- okay, let's try an update that should work.
974 update pktable set base1=base1*4 where base1<3;
975 -- and a delete that should work
976 delete from pktable where base1>3;
977 -- cleanup
978 drop table fktable;
979 drop table pktable;
980 drop table pktable_base;
981 -- Now we'll do one all in 1 table with 2 columns of matching types
982 create table pktable_base(base1 int not null, base2 int);
983 create table pktable(ptest1 int, ptest2 int, primary key(base1, ptest1), foreign key(base2, ptest2) references
984 pktable(base1, ptest1)) inherits (pktable_base);
985 insert into pktable (base1, ptest1, base2, ptest2) values (1, 1, 1, 1);
986 insert into pktable (base1, ptest1, base2, ptest2) values (2, 1, 1, 1);
987 insert into pktable (base1, ptest1, base2, ptest2) values (2, 2, 2, 1);
988 insert into pktable (base1, ptest1, base2, ptest2) values (1, 3, 2, 2);
989 -- fails (3,2) isn't in base1, ptest1
990 insert into pktable (base1, ptest1, base2, ptest2) values (2, 3, 3, 2);
991 ERROR: insert or update on table "pktable" violates foreign key constraint "pktable_base2_ptest2_fkey"
992 DETAIL: Key (base2, ptest2)=(3, 2) is not present in table "pktable".
993 -- fails (2,2) is being referenced
994 delete from pktable where base1=2;
995 ERROR: update or delete on table "pktable" violates foreign key constraint "pktable_base2_ptest2_fkey" on table "pktable"
996 DETAIL: Key (base1, ptest1)=(2, 2) is still referenced from table "pktable".
997 -- fails (1,1) is being referenced (twice)
998 update pktable set base1=3 where base1=1;
999 ERROR: update or delete on table "pktable" violates foreign key constraint "pktable_base2_ptest2_fkey" on table "pktable"
1000 DETAIL: Key (base1, ptest1)=(1, 1) is still referenced from table "pktable".
1001 -- this sequence of two deletes will work, since after the first there will be no (2,*) references
1002 delete from pktable where base2=2;
1003 delete from pktable where base1=2;
1004 drop table pktable;
1005 drop table pktable_base;
1006 -- 2 columns (2 tables), mismatched types
1007 create table pktable_base(base1 int not null);
1008 create table pktable(ptest1 inet, primary key(base1, ptest1)) inherits (pktable_base);
1009 -- just generally bad types (with and without column references on the referenced table)
1010 create table fktable(ftest1 cidr, ftest2 int[], foreign key (ftest1, ftest2) references pktable);
1011 ERROR: foreign key constraint "fktable_ftest1_ftest2_fkey" cannot be implemented
1012 DETAIL: Key columns "ftest1" of the referencing table and "base1" of the referenced table are of incompatible types: cidr and integer.
1013 create table fktable(ftest1 cidr, ftest2 int[], foreign key (ftest1, ftest2) references pktable(base1, ptest1));
1014 ERROR: foreign key constraint "fktable_ftest1_ftest2_fkey" cannot be implemented
1015 DETAIL: Key columns "ftest1" of the referencing table and "base1" of the referenced table are of incompatible types: cidr and integer.
1016 -- let's mix up which columns reference which
1017 create table fktable(ftest1 int, ftest2 inet, foreign key(ftest2, ftest1) references pktable);
1018 ERROR: foreign key constraint "fktable_ftest2_ftest1_fkey" cannot be implemented
1019 DETAIL: Key columns "ftest2" of the referencing table and "base1" of the referenced table are of incompatible types: inet and integer.
1020 create table fktable(ftest1 int, ftest2 inet, foreign key(ftest2, ftest1) references pktable(base1, ptest1));
1021 ERROR: foreign key constraint "fktable_ftest2_ftest1_fkey" cannot be implemented
1022 DETAIL: Key columns "ftest2" of the referencing table and "base1" of the referenced table are of incompatible types: inet and integer.
1023 create table fktable(ftest1 int, ftest2 inet, foreign key(ftest1, ftest2) references pktable(ptest1, base1));
1024 ERROR: foreign key constraint "fktable_ftest1_ftest2_fkey" cannot be implemented
1025 DETAIL: Key columns "ftest1" of the referencing table and "ptest1" of the referenced table are of incompatible types: integer and inet.
1026 drop table pktable;
1027 drop table pktable_base;
1028 -- 2 columns (1 table), mismatched types
1029 create table pktable_base(base1 int not null, base2 int);
1030 create table pktable(ptest1 inet, ptest2 inet[], primary key(base1, ptest1), foreign key(base2, ptest2) references
1031 pktable(base1, ptest1)) inherits (pktable_base);
1032 ERROR: foreign key constraint "pktable_base2_ptest2_fkey" cannot be implemented
1033 DETAIL: Key columns "ptest2" of the referencing table and "ptest1" of the referenced table are of incompatible types: inet[] and inet.
1034 create table pktable(ptest1 inet, ptest2 inet, primary key(base1, ptest1), foreign key(base2, ptest2) references
1035 pktable(ptest1, base1)) inherits (pktable_base);
1036 ERROR: foreign key constraint "pktable_base2_ptest2_fkey" cannot be implemented
1037 DETAIL: Key columns "base2" of the referencing table and "ptest1" of the referenced table are of incompatible types: integer and inet.
1038 create table pktable(ptest1 inet, ptest2 inet, primary key(base1, ptest1), foreign key(ptest2, base2) references
1039 pktable(base1, ptest1)) inherits (pktable_base);
1040 ERROR: foreign key constraint "pktable_ptest2_base2_fkey" cannot be implemented
1041 DETAIL: Key columns "ptest2" of the referencing table and "base1" of the referenced table are of incompatible types: inet and integer.
1042 create table pktable(ptest1 inet, ptest2 inet, primary key(base1, ptest1), foreign key(ptest2, base2) references
1043 pktable(base1, ptest1)) inherits (pktable_base);
1044 ERROR: foreign key constraint "pktable_ptest2_base2_fkey" cannot be implemented
1045 DETAIL: Key columns "ptest2" of the referencing table and "base1" of the referenced table are of incompatible types: inet and integer.
1046 drop table pktable;
1047 ERROR: table "pktable" does not exist
1048 drop table pktable_base;
1049 --
1050 -- Deferrable constraints
1051 --
1052 -- deferrable, explicitly deferred
1053 CREATE TABLE pktable (
1054 id INT4 PRIMARY KEY,
1055 other INT4
1056 );
1057 CREATE TABLE fktable (
1058 id INT4 PRIMARY KEY,
1059 fk INT4 REFERENCES pktable DEFERRABLE
1060 );
1061 -- default to immediate: should fail
1062 INSERT INTO fktable VALUES (5, 10);
1063 ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey"
1064 DETAIL: Key (fk)=(10) is not present in table "pktable".
1065 -- explicitly defer the constraint
1066 BEGIN;
1067 SET CONSTRAINTS ALL DEFERRED;
1068 INSERT INTO fktable VALUES (10, 15);
1069 INSERT INTO pktable VALUES (15, 0); -- make the FK insert valid
1070 COMMIT;
1071 DROP TABLE fktable, pktable;
1072 -- deferrable, initially deferred
1073 CREATE TABLE pktable (
1074 id INT4 PRIMARY KEY,
1075 other INT4
1076 );
1077 CREATE TABLE fktable (
1078 id INT4 PRIMARY KEY,
1079 fk INT4 REFERENCES pktable DEFERRABLE INITIALLY DEFERRED
1080 );
1081 -- default to deferred, should succeed
1082 BEGIN;
1083 INSERT INTO fktable VALUES (100, 200);
1084 INSERT INTO pktable VALUES (200, 500); -- make the FK insert valid
1085 COMMIT;
1086 -- default to deferred, explicitly make immediate
1087 BEGIN;
1088 SET CONSTRAINTS ALL IMMEDIATE;
1089 -- should fail
1090 INSERT INTO fktable VALUES (500, 1000);
1091 ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey"
1092 DETAIL: Key (fk)=(1000) is not present in table "pktable".
1093 COMMIT;
1094 DROP TABLE fktable, pktable;
1095 -- tricky behavior: according to SQL99, if a deferred constraint is set
1096 -- to 'immediate' mode, it should be checked for validity *immediately*,
1097 -- not when the current transaction commits (i.e. the mode change applies
1098 -- retroactively)
1099 CREATE TABLE pktable (
1100 id INT4 PRIMARY KEY,
1101 other INT4
1102 );
1103 CREATE TABLE fktable (
1104 id INT4 PRIMARY KEY,
1105 fk INT4 REFERENCES pktable DEFERRABLE
1106 );
1107 BEGIN;
1108 SET CONSTRAINTS ALL DEFERRED;
1109 -- should succeed, for now
1110 INSERT INTO fktable VALUES (1000, 2000);
1111 -- should cause transaction abort, due to preceding error
1112 SET CONSTRAINTS ALL IMMEDIATE;
1113 ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey"
1114 DETAIL: Key (fk)=(2000) is not present in table "pktable".
1115 INSERT INTO pktable VALUES (2000, 3); -- too late
1116 ERROR: current transaction is aborted, commands ignored until end of transaction block
1117 COMMIT;
1118 DROP TABLE fktable, pktable;
1119 -- deferrable, initially deferred
1120 CREATE TABLE pktable (
1121 id INT4 PRIMARY KEY,
1122 other INT4
1123 );
1124 CREATE TABLE fktable (
1125 id INT4 PRIMARY KEY,
1126 fk INT4 REFERENCES pktable DEFERRABLE INITIALLY DEFERRED
1127 );
1128 BEGIN;
1129 -- no error here
1130 INSERT INTO fktable VALUES (100, 200);
1131 -- error here on commit
1132 COMMIT;
1133 ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey"
1134 DETAIL: Key (fk)=(200) is not present in table "pktable".
1135 DROP TABLE pktable, fktable;
1136 -- test notice about expensive referential integrity checks,
1137 -- where the index cannot be used because of type incompatibilities.
1138 CREATE TEMP TABLE pktable (
1139 id1 INT4 PRIMARY KEY,
1140 id2 VARCHAR(4) UNIQUE,
1141 id3 REAL UNIQUE,
1142 UNIQUE(id1, id2, id3)
1143 );
1144 CREATE TEMP TABLE fktable (
1145 x1 INT4 REFERENCES pktable(id1),
1146 x2 VARCHAR(4) REFERENCES pktable(id2),
1147 x3 REAL REFERENCES pktable(id3),
1148 x4 TEXT,
1149 x5 INT2
1150 );
1151 -- check individual constraints with alter table.
1152 -- should fail
1153 -- varchar does not promote to real
1154 ALTER TABLE fktable ADD CONSTRAINT fk_2_3
1155 FOREIGN KEY (x2) REFERENCES pktable(id3);
1156 ERROR: foreign key constraint "fk_2_3" cannot be implemented
1157 DETAIL: Key columns "x2" of the referencing table and "id3" of the referenced table are of incompatible types: character varying and real.
1158 -- nor to int4
1159 ALTER TABLE fktable ADD CONSTRAINT fk_2_1
1160 FOREIGN KEY (x2) REFERENCES pktable(id1);
1161 ERROR: foreign key constraint "fk_2_1" cannot be implemented
1162 DETAIL: Key columns "x2" of the referencing table and "id1" of the referenced table are of incompatible types: character varying and integer.
1163 -- real does not promote to int4
1164 ALTER TABLE fktable ADD CONSTRAINT fk_3_1
1165 FOREIGN KEY (x3) REFERENCES pktable(id1);
1166 ERROR: foreign key constraint "fk_3_1" cannot be implemented
1167 DETAIL: Key columns "x3" of the referencing table and "id1" of the referenced table are of incompatible types: real and integer.
1168 -- int4 does not promote to text
1169 ALTER TABLE fktable ADD CONSTRAINT fk_1_2
1170 FOREIGN KEY (x1) REFERENCES pktable(id2);
1171 ERROR: foreign key constraint "fk_1_2" cannot be implemented
1172 DETAIL: Key columns "x1" of the referencing table and "id2" of the referenced table are of incompatible types: integer and character varying.
1173 -- should succeed
1174 -- int4 promotes to real
1175 ALTER TABLE fktable ADD CONSTRAINT fk_1_3
1176 FOREIGN KEY (x1) REFERENCES pktable(id3);
1177 -- text is compatible with varchar
1178 ALTER TABLE fktable ADD CONSTRAINT fk_4_2
1179 FOREIGN KEY (x4) REFERENCES pktable(id2);
1180 -- int2 is part of integer opfamily as of 8.0
1181 ALTER TABLE fktable ADD CONSTRAINT fk_5_1
1182 FOREIGN KEY (x5) REFERENCES pktable(id1);
1183 -- check multikey cases, especially out-of-order column lists
1184 -- these should work
1185 ALTER TABLE fktable ADD CONSTRAINT fk_123_123
1186 FOREIGN KEY (x1,x2,x3) REFERENCES pktable(id1,id2,id3);
1187 ALTER TABLE fktable ADD CONSTRAINT fk_213_213
1188 FOREIGN KEY (x2,x1,x3) REFERENCES pktable(id2,id1,id3);
1189 ALTER TABLE fktable ADD CONSTRAINT fk_253_213
1190 FOREIGN KEY (x2,x5,x3) REFERENCES pktable(id2,id1,id3);
1191 -- these should fail
1192 ALTER TABLE fktable ADD CONSTRAINT fk_123_231
1193 FOREIGN KEY (x1,x2,x3) REFERENCES pktable(id2,id3,id1);
1194 ERROR: foreign key constraint "fk_123_231" cannot be implemented
1195 DETAIL: Key columns "x1" of the referencing table and "id2" of the referenced table are of incompatible types: integer and character varying.
1196 ALTER TABLE fktable ADD CONSTRAINT fk_241_132
1197 FOREIGN KEY (x2,x4,x1) REFERENCES pktable(id1,id3,id2);
1198 ERROR: foreign key constraint "fk_241_132" cannot be implemented
1199 DETAIL: Key columns "x2" of the referencing table and "id1" of the referenced table are of incompatible types: character varying and integer.
1200 DROP TABLE pktable, fktable;
1201 -- test a tricky case: we can elide firing the FK check trigger during
1202 -- an UPDATE if the UPDATE did not change the foreign key
1203 -- field. However, we can't do this if our transaction was the one that
1204 -- created the updated row and the trigger is deferred, since our UPDATE
1205 -- will have invalidated the original newly-inserted tuple, and therefore
1206 -- cause the on-INSERT RI trigger not to be fired.
1207 CREATE TEMP TABLE pktable (
1208 id int primary key,
1209 other int
1210 );
1211 CREATE TEMP TABLE fktable (
1212 id int primary key,
1213 fk int references pktable deferrable initially deferred
1214 );
1215 INSERT INTO pktable VALUES (5, 10);
1216 BEGIN;
1217 -- doesn't match PK, but no error yet
1218 INSERT INTO fktable VALUES (0, 20);
1219 -- don't change FK
1220 UPDATE fktable SET id = id + 1;
1221 -- should catch error from initial INSERT
1222 COMMIT;
1223 ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey"
1224 DETAIL: Key (fk)=(20) is not present in table "pktable".
1225 -- check same case when insert is in a different subtransaction than update
1226 BEGIN;
1227 -- doesn't match PK, but no error yet
1228 INSERT INTO fktable VALUES (0, 20);
1229 -- UPDATE will be in a subxact
1230 SAVEPOINT savept1;
1231 -- don't change FK
1232 UPDATE fktable SET id = id + 1;
1233 -- should catch error from initial INSERT
1234 COMMIT;
1235 ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey"
1236 DETAIL: Key (fk)=(20) is not present in table "pktable".
1237 BEGIN;
1238 -- INSERT will be in a subxact
1239 SAVEPOINT savept1;
1240 -- doesn't match PK, but no error yet
1241 INSERT INTO fktable VALUES (0, 20);
1242 RELEASE SAVEPOINT savept1;
1243 -- don't change FK
1244 UPDATE fktable SET id = id + 1;
1245 -- should catch error from initial INSERT
1246 COMMIT;
1247 ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey"
1248 DETAIL: Key (fk)=(20) is not present in table "pktable".
1249 BEGIN;
1250 -- doesn't match PK, but no error yet
1251 INSERT INTO fktable VALUES (0, 20);
1252 -- UPDATE will be in a subxact
1253 SAVEPOINT savept1;
1254 -- don't change FK
1255 UPDATE fktable SET id = id + 1;
1256 -- Roll back the UPDATE
1257 ROLLBACK TO savept1;
1258 -- should catch error from initial INSERT
1259 COMMIT;
1260 ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey"
1261 DETAIL: Key (fk)=(20) is not present in table "pktable".
1262 --
1263 -- check ALTER CONSTRAINT
1264 --
1265 INSERT INTO fktable VALUES (1, 5);
1266 ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey DEFERRABLE INITIALLY IMMEDIATE;
1267 BEGIN;
1268 -- doesn't match FK, should throw error now
1269 UPDATE pktable SET id = 10 WHERE id = 5;
1270 ERROR: update or delete on table "pktable" violates foreign key constraint "fktable_fk_fkey" on table "fktable"
1271 DETAIL: Key (id)=(5) is still referenced from table "fktable".
1272 COMMIT;
1273 BEGIN;
1274 -- doesn't match PK, should throw error now
1275 INSERT INTO fktable VALUES (0, 20);
1276 ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey"
1277 DETAIL: Key (fk)=(20) is not present in table "pktable".
1278 COMMIT;
1279 -- try additional syntax
1280 ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NOT DEFERRABLE;
1281 -- illegal option
1282 ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NOT DEFERRABLE INITIALLY DEFERRED;
1283 ERROR: constraint declared INITIALLY DEFERRED must be DEFERRABLE
1284 LINE 1: ...e ALTER CONSTRAINT fktable_fk_fkey NOT DEFERRABLE INITIALLY ...
1285 ^
1286 -- test order of firing of FK triggers when several RI-induced changes need to
1287 -- be made to the same row. This was broken by subtransaction-related
1288 -- changes in 8.0.
1289 CREATE TEMP TABLE users (
1290 id INT PRIMARY KEY,
1291 name VARCHAR NOT NULL
1292 );
1293 INSERT INTO users VALUES (1, 'Jozko');
1294 INSERT INTO users VALUES (2, 'Ferko');
1295 INSERT INTO users VALUES (3, 'Samko');
1296 CREATE TEMP TABLE tasks (
1297 id INT PRIMARY KEY,
1298 owner INT REFERENCES users ON UPDATE CASCADE ON DELETE SET NULL,
1299 worker INT REFERENCES users ON UPDATE CASCADE ON DELETE SET NULL,
1300 checked_by INT REFERENCES users ON UPDATE CASCADE ON DELETE SET NULL
1301 );
1302 INSERT INTO tasks VALUES (1,1,NULL,NULL);
1303 INSERT INTO tasks VALUES (2,2,2,NULL);
1304 INSERT INTO tasks VALUES (3,3,3,3);
1305 SELECT * FROM tasks;
1306 id | owner | worker | checked_by
1307 ----+-------+--------+------------
1308 1 | 1 | |
1309 2 | 2 | 2 |
1310 3 | 3 | 3 | 3
1311 (3 rows)
1313 UPDATE users SET id = 4 WHERE id = 3;
1314 SELECT * FROM tasks;
1315 id | owner | worker | checked_by
1316 ----+-------+--------+------------
1317 1 | 1 | |
1318 2 | 2 | 2 |
1319 3 | 4 | 4 | 4
1320 (3 rows)
1322 DELETE FROM users WHERE id = 4;
1323 SELECT * FROM tasks;
1324 id | owner | worker | checked_by
1325 ----+-------+--------+------------
1326 1 | 1 | |
1327 2 | 2 | 2 |
1328 3 | | |
1329 (3 rows)
1331 -- could fail with only 2 changes to make, if row was already updated
1332 BEGIN;
1333 UPDATE tasks set id=id WHERE id=2;
1334 SELECT * FROM tasks;
1335 id | owner | worker | checked_by
1336 ----+-------+--------+------------
1337 1 | 1 | |
1338 3 | | |
1339 2 | 2 | 2 |
1340 (3 rows)
1342 DELETE FROM users WHERE id = 2;
1343 SELECT * FROM tasks;
1344 id | owner | worker | checked_by
1345 ----+-------+--------+------------
1346 1 | 1 | |
1347 3 | | |
1348 2 | | |
1349 (3 rows)
1351 COMMIT;
1352 --
1353 -- Test self-referential FK with CASCADE (bug #6268)
1354 --
1355 create temp table selfref (
1356 a int primary key,
1357 b int,
1358 foreign key (b) references selfref (a)
1359 on update cascade on delete cascade
1360 );
1361 insert into selfref (a, b)
1362 values
1363 (0, 0),
1364 (1, 1);
1365 begin;
1366 update selfref set a = 123 where a = 0;
1367 select a, b from selfref;
1368 a | b
1369 -----+-----
1370 1 | 1
1371 123 | 123
1372 (2 rows)
1374 update selfref set a = 456 where a = 123;
1375 select a, b from selfref;
1376 a | b
1377 -----+-----
1378 1 | 1
1379 456 | 456
1380 (2 rows)
1382 commit;
1383 --
1384 -- Test that SET DEFAULT actions recognize updates to default values
1385 --
1386 create temp table defp (f1 int primary key);
1387 create temp table defc (f1 int default 0
1388 references defp on delete set default);
1389 insert into defp values (0), (1), (2);
1390 insert into defc values (2);
1391 select * from defc;
1392 f1
1393 ----
1394 2
1395 (1 row)
1397 delete from defp where f1 = 2;
1398 select * from defc;
1399 f1
1400 ----
1401 0
1402 (1 row)
1404 delete from defp where f1 = 0; -- fail
1405 ERROR: update or delete on table "defp" violates foreign key constraint "defc_f1_fkey" on table "defc"
1406 DETAIL: Key (f1)=(0) is still referenced from table "defc".
1407 alter table defc alter column f1 set default 1;
1408 delete from defp where f1 = 0;
1409 select * from defc;
1410 f1
1411 ----
1412 1
1413 (1 row)
1415 delete from defp where f1 = 1; -- fail
1416 ERROR: update or delete on table "defp" violates foreign key constraint "defc_f1_fkey" on table "defc"
1417 DETAIL: Key (f1)=(1) is still referenced from table "defc".
1418 --
1419 -- Test the difference between NO ACTION and RESTRICT
1420 --
1421 create temp table pp (f1 int primary key);
1422 create temp table cc (f1 int references pp on update no action on delete no action);
1423 insert into pp values(12);
1424 insert into pp values(11);
1425 update pp set f1=f1+1;
1426 insert into cc values(13);
1427 update pp set f1=f1+1;
1428 update pp set f1=f1+1; -- fail
1429 ERROR: update or delete on table "pp" violates foreign key constraint "cc_f1_fkey" on table "cc"
1430 DETAIL: Key (f1)=(13) is still referenced from table "cc".
1431 delete from pp where f1 = 13; -- fail
1432 ERROR: update or delete on table "pp" violates foreign key constraint "cc_f1_fkey" on table "cc"
1433 DETAIL: Key (f1)=(13) is still referenced from table "cc".
1434 drop table pp, cc;
1435 create temp table pp (f1 int primary key);
1436 create temp table cc (f1 int references pp on update restrict on delete restrict);
1437 insert into pp values(12);
1438 insert into pp values(11);
1439 update pp set f1=f1+1;
1440 insert into cc values(13);
1441 update pp set f1=f1+1; -- fail
1442 ERROR: update or delete on table "pp" violates RESTRICT setting of foreign key constraint "cc_f1_fkey" on table "cc"
1443 DETAIL: Key (f1)=(13) is referenced from table "cc".
1444 delete from pp where f1 = 13; -- fail
1445 ERROR: update or delete on table "pp" violates RESTRICT setting of foreign key constraint "cc_f1_fkey" on table "cc"
1446 DETAIL: Key (f1)=(13) is referenced from table "cc".
1447 drop table pp, cc;
1448 --
1449 -- Test interaction of foreign-key optimization with rules (bug #14219)
1450 --
1451 create temp table t1 (a integer primary key, b text);
1452 create temp table t2 (a integer primary key, b integer references t1);
1453 create rule r1 as on delete to t1 do delete from t2 where t2.b = old.a;
1454 explain (costs off) delete from t1 where a = 1;
1455 QUERY PLAN
1456 --------------------------------------------
1457 Delete on t2
1458 -> Nested Loop
1459 -> Index Scan using t1_pkey on t1
1460 Index Cond: (a = 1)
1461 -> Seq Scan on t2
1462 Filter: (b = 1)
1464 Delete on t1
1465 -> Index Scan using t1_pkey on t1
1466 Index Cond: (a = 1)
1467 (10 rows)
1469 delete from t1 where a = 1;
1470 -- Test a primary key with attributes located in later attnum positions
1471 -- compared to the fk attributes.
1472 create table pktable2 (a int, b int, c int, d int, e int, primary key (d, e));
1473 create table fktable2 (d int, e int, foreign key (d, e) references pktable2);
1474 insert into pktable2 values (1, 2, 3, 4, 5);
1475 insert into fktable2 values (4, 5);
1476 delete from pktable2;
1477 ERROR: update or delete on table "pktable2" violates foreign key constraint "fktable2_d_e_fkey" on table "fktable2"
1478 DETAIL: Key (d, e)=(4, 5) is still referenced from table "fktable2".
1479 update pktable2 set d = 5;
1480 ERROR: update or delete on table "pktable2" violates foreign key constraint "fktable2_d_e_fkey" on table "fktable2"
1481 DETAIL: Key (d, e)=(4, 5) is still referenced from table "fktable2".
1482 drop table pktable2, fktable2;
1483 -- Test truncation of long foreign key names
1484 create table pktable1 (a int primary key);
1485 create table pktable2 (a int, b int, primary key (a, b));
1486 create table fktable2 (
1487 a int,
1488 b int,
1489 very_very_long_column_name_to_exceed_63_characters int,
1490 foreign key (very_very_long_column_name_to_exceed_63_characters) references pktable1,
1491 foreign key (a, very_very_long_column_name_to_exceed_63_characters) references pktable2,
1492 foreign key (a, very_very_long_column_name_to_exceed_63_characters) references pktable2
1493 );
1494 select conname from pg_constraint where conrelid = 'fktable2'::regclass order by conname;
1495 conname
1496 -----------------------------------------------------------------
1497 fktable2_a_very_very_long_column_name_to_exceed_63_charac_fkey1
1498 fktable2_a_very_very_long_column_name_to_exceed_63_charact_fkey
1499 fktable2_very_very_long_column_name_to_exceed_63_character_fkey
1500 (3 rows)
1502 drop table pktable1, pktable2, fktable2;
1503 --
1504 -- Test deferred FK check on a tuple deleted by a rolled-back subtransaction
1505 --
1506 create table pktable2(f1 int primary key);
1507 create table fktable2(f1 int references pktable2 deferrable initially deferred);
1508 insert into pktable2 values(1);
1509 begin;
1510 insert into fktable2 values(1);
1511 savepoint x;
1512 delete from fktable2;
1513 rollback to x;
1514 commit;
1515 begin;
1516 insert into fktable2 values(2);
1517 savepoint x;
1518 delete from fktable2;
1519 rollback to x;
1520 commit; -- fail
1521 ERROR: insert or update on table "fktable2" violates foreign key constraint "fktable2_f1_fkey"
1522 DETAIL: Key (f1)=(2) is not present in table "pktable2".
1523 --
1524 -- Test that we prevent dropping FK constraint with pending trigger events
1525 --
1526 begin;
1527 insert into fktable2 values(2);
1528 alter table fktable2 drop constraint fktable2_f1_fkey;
1529 ERROR: cannot ALTER TABLE "fktable2" because it has pending trigger events
1530 commit;
1531 begin;
1532 delete from pktable2 where f1 = 1;
1533 alter table fktable2 drop constraint fktable2_f1_fkey;
1534 ERROR: cannot ALTER TABLE "pktable2" because it has pending trigger events
1535 commit;
1536 drop table pktable2, fktable2;
1537 --
1538 -- Test keys that "look" different but compare as equal
1539 --
1540 create table pktable2 (a float8, b float8, primary key (a, b));
1541 create table fktable2 (x float8, y float8, foreign key (x, y) references pktable2 (a, b) on update cascade);
1542 insert into pktable2 values ('-0', '-0');
1543 insert into fktable2 values ('-0', '-0');
1544 select * from pktable2;
1545 a | b
1546 ----+----
1547 -0 | -0
1548 (1 row)
1550 select * from fktable2;
1551 x | y
1552 ----+----
1553 -0 | -0
1554 (1 row)
1556 update pktable2 set a = '0' where a = '-0';
1557 select * from pktable2;
1558 a | b
1559 ---+----
1560 0 | -0
1561 (1 row)
1563 -- should have updated fktable2.x
1564 select * from fktable2;
1565 x | y
1566 ---+----
1567 0 | -0
1568 (1 row)
1570 drop table pktable2, fktable2;
1571 --
1572 -- Foreign keys and partitioned tables
1573 --
1574 -- Creation of a partitioned hierarchy with irregular definitions
1575 CREATE TABLE fk_notpartitioned_pk (fdrop1 int, a int, fdrop2 int, b int,
1576 PRIMARY KEY (a, b));
1577 ALTER TABLE fk_notpartitioned_pk DROP COLUMN fdrop1, DROP COLUMN fdrop2;
1578 CREATE TABLE fk_partitioned_fk (b int, fdrop1 int, a int) PARTITION BY RANGE (a, b);
1579 ALTER TABLE fk_partitioned_fk DROP COLUMN fdrop1;
1580 CREATE TABLE fk_partitioned_fk_1 (fdrop1 int, fdrop2 int, a int, fdrop3 int, b int);
1581 ALTER TABLE fk_partitioned_fk_1 DROP COLUMN fdrop1, DROP COLUMN fdrop2, DROP COLUMN fdrop3;
1582 ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_1 FOR VALUES FROM (0,0) TO (1000,1000);
1583 ALTER TABLE fk_partitioned_fk ADD FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk;
1584 CREATE TABLE fk_partitioned_fk_2 (b int, fdrop1 int, fdrop2 int, a int);
1585 ALTER TABLE fk_partitioned_fk_2 DROP COLUMN fdrop1, DROP COLUMN fdrop2;
1586 ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES FROM (1000,1000) TO (2000,2000);
1587 CREATE TABLE fk_partitioned_fk_3 (fdrop1 int, fdrop2 int, fdrop3 int, fdrop4 int, b int, a int)
1588 PARTITION BY HASH (a);
1589 ALTER TABLE fk_partitioned_fk_3 DROP COLUMN fdrop1, DROP COLUMN fdrop2,
1590 DROP COLUMN fdrop3, DROP COLUMN fdrop4;
1591 CREATE TABLE fk_partitioned_fk_3_0 PARTITION OF fk_partitioned_fk_3 FOR VALUES WITH (MODULUS 5, REMAINDER 0);
1592 CREATE TABLE fk_partitioned_fk_3_1 PARTITION OF fk_partitioned_fk_3 FOR VALUES WITH (MODULUS 5, REMAINDER 1);
1593 ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_3
1594 FOR VALUES FROM (2000,2000) TO (3000,3000);
1595 -- Creating a foreign key with ONLY on a partitioned table referencing
1596 -- a non-partitioned table fails.
1597 ALTER TABLE ONLY fk_partitioned_fk ADD FOREIGN KEY (a, b)
1598 REFERENCES fk_notpartitioned_pk;
1599 ERROR: cannot use ONLY for foreign key on partitioned table "fk_partitioned_fk" referencing relation "fk_notpartitioned_pk"
1600 -- Adding a NOT VALID foreign key on a partitioned table referencing
1601 -- a non-partitioned table fails.
1602 ALTER TABLE fk_partitioned_fk ADD FOREIGN KEY (a, b)
1603 REFERENCES fk_notpartitioned_pk NOT VALID;
1604 ERROR: cannot add NOT VALID foreign key on partitioned table "fk_partitioned_fk" referencing relation "fk_notpartitioned_pk"
1605 DETAIL: This feature is not yet supported on partitioned tables.
1606 -- these inserts, targeting both the partition directly as well as the
1607 -- partitioned table, should all fail
1608 INSERT INTO fk_partitioned_fk (a,b) VALUES (500, 501);
1609 ERROR: insert or update on table "fk_partitioned_fk_1" violates foreign key constraint "fk_partitioned_fk_a_b_fkey"
1610 DETAIL: Key (a, b)=(500, 501) is not present in table "fk_notpartitioned_pk".
1611 INSERT INTO fk_partitioned_fk_1 (a,b) VALUES (500, 501);
1612 ERROR: insert or update on table "fk_partitioned_fk_1" violates foreign key constraint "fk_partitioned_fk_a_b_fkey"
1613 DETAIL: Key (a, b)=(500, 501) is not present in table "fk_notpartitioned_pk".
1614 INSERT INTO fk_partitioned_fk (a,b) VALUES (1500, 1501);
1615 ERROR: insert or update on table "fk_partitioned_fk_2" violates foreign key constraint "fk_partitioned_fk_a_b_fkey"
1616 DETAIL: Key (a, b)=(1500, 1501) is not present in table "fk_notpartitioned_pk".
1617 INSERT INTO fk_partitioned_fk_2 (a,b) VALUES (1500, 1501);
1618 ERROR: insert or update on table "fk_partitioned_fk_2" violates foreign key constraint "fk_partitioned_fk_a_b_fkey"
1619 DETAIL: Key (a, b)=(1500, 1501) is not present in table "fk_notpartitioned_pk".
1620 INSERT INTO fk_partitioned_fk (a,b) VALUES (2500, 2502);
1621 ERROR: insert or update on table "fk_partitioned_fk_3_1" violates foreign key constraint "fk_partitioned_fk_a_b_fkey"
1622 DETAIL: Key (a, b)=(2500, 2502) is not present in table "fk_notpartitioned_pk".
1623 INSERT INTO fk_partitioned_fk_3 (a,b) VALUES (2500, 2502);
1624 ERROR: insert or update on table "fk_partitioned_fk_3_1" violates foreign key constraint "fk_partitioned_fk_a_b_fkey"
1625 DETAIL: Key (a, b)=(2500, 2502) is not present in table "fk_notpartitioned_pk".
1626 INSERT INTO fk_partitioned_fk (a,b) VALUES (2501, 2503);
1627 ERROR: insert or update on table "fk_partitioned_fk_3_0" violates foreign key constraint "fk_partitioned_fk_a_b_fkey"
1628 DETAIL: Key (a, b)=(2501, 2503) is not present in table "fk_notpartitioned_pk".
1629 INSERT INTO fk_partitioned_fk_3 (a,b) VALUES (2501, 2503);
1630 ERROR: insert or update on table "fk_partitioned_fk_3_0" violates foreign key constraint "fk_partitioned_fk_a_b_fkey"
1631 DETAIL: Key (a, b)=(2501, 2503) is not present in table "fk_notpartitioned_pk".
1632 -- but if we insert the values that make them valid, then they work
1633 INSERT INTO fk_notpartitioned_pk VALUES (500, 501), (1500, 1501),
1634 (2500, 2502), (2501, 2503);
1635 INSERT INTO fk_partitioned_fk (a,b) VALUES (500, 501);
1636 INSERT INTO fk_partitioned_fk (a,b) VALUES (1500, 1501);
1637 INSERT INTO fk_partitioned_fk (a,b) VALUES (2500, 2502);
1638 INSERT INTO fk_partitioned_fk (a,b) VALUES (2501, 2503);
1639 -- this update fails because there is no referenced row
1640 UPDATE fk_partitioned_fk SET a = a + 1 WHERE a = 2501;
1641 ERROR: insert or update on table "fk_partitioned_fk_3_1" violates foreign key constraint "fk_partitioned_fk_a_b_fkey"
1642 DETAIL: Key (a, b)=(2502, 2503) is not present in table "fk_notpartitioned_pk".
1643 -- but we can fix it thusly:
1644 INSERT INTO fk_notpartitioned_pk (a,b) VALUES (2502, 2503);
1645 UPDATE fk_partitioned_fk SET a = a + 1 WHERE a = 2501;
1646 -- these updates would leave lingering rows in the referencing table; disallow
1647 UPDATE fk_notpartitioned_pk SET b = 502 WHERE a = 500;
1648 ERROR: update or delete on table "fk_notpartitioned_pk" violates foreign key constraint "fk_partitioned_fk_a_b_fkey" on table "fk_partitioned_fk"
1649 DETAIL: Key (a, b)=(500, 501) is still referenced from table "fk_partitioned_fk".
1650 UPDATE fk_notpartitioned_pk SET b = 1502 WHERE a = 1500;
1651 ERROR: update or delete on table "fk_notpartitioned_pk" violates foreign key constraint "fk_partitioned_fk_a_b_fkey" on table "fk_partitioned_fk"
1652 DETAIL: Key (a, b)=(1500, 1501) is still referenced from table "fk_partitioned_fk".
1653 UPDATE fk_notpartitioned_pk SET b = 2504 WHERE a = 2500;
1654 ERROR: update or delete on table "fk_notpartitioned_pk" violates foreign key constraint "fk_partitioned_fk_a_b_fkey" on table "fk_partitioned_fk"
1655 DETAIL: Key (a, b)=(2500, 2502) is still referenced from table "fk_partitioned_fk".
1656 -- check psql behavior
1657 \d fk_notpartitioned_pk
1658 Table "public.fk_notpartitioned_pk"
1659 Column | Type | Collation | Nullable | Default
1660 --------+---------+-----------+----------+---------
1661 a | integer | | not null |
1662 b | integer | | not null |
1663 Indexes:
1664 "fk_notpartitioned_pk_pkey" PRIMARY KEY, btree (a, b)
1665 Referenced by:
1666 TABLE "fk_partitioned_fk" CONSTRAINT "fk_partitioned_fk_a_b_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b)
1668 ALTER TABLE fk_partitioned_fk DROP CONSTRAINT fk_partitioned_fk_a_b_fkey;
1669 -- done.
1670 DROP TABLE fk_notpartitioned_pk, fk_partitioned_fk;
1671 -- Altering a type referenced by a foreign key needs to drop/recreate the FK.
1672 -- Ensure that works.
1673 CREATE TABLE fk_notpartitioned_pk (a INT, PRIMARY KEY(a), CHECK (a > 0));
1674 CREATE TABLE fk_partitioned_fk (a INT REFERENCES fk_notpartitioned_pk(a) PRIMARY KEY) PARTITION BY RANGE(a);
1675 CREATE TABLE fk_partitioned_fk_1 PARTITION OF fk_partitioned_fk FOR VALUES FROM (MINVALUE) TO (MAXVALUE);
1676 INSERT INTO fk_notpartitioned_pk VALUES (1);
1677 INSERT INTO fk_partitioned_fk VALUES (1);
1678 ALTER TABLE fk_notpartitioned_pk ALTER COLUMN a TYPE bigint;
1679 DELETE FROM fk_notpartitioned_pk WHERE a = 1;
1680 ERROR: update or delete on table "fk_notpartitioned_pk" violates foreign key constraint "fk_partitioned_fk_a_fkey" on table "fk_partitioned_fk"
1681 DETAIL: Key (a)=(1) is still referenced from table "fk_partitioned_fk".
1682 DROP TABLE fk_notpartitioned_pk, fk_partitioned_fk;
1683 -- Test some other exotic foreign key features: MATCH SIMPLE, ON UPDATE/DELETE
1684 -- actions
1685 CREATE TABLE fk_notpartitioned_pk (a int, b int, primary key (a, b));
1686 CREATE TABLE fk_partitioned_fk (a int default 2501, b int default 142857) PARTITION BY LIST (a);
1687 CREATE TABLE fk_partitioned_fk_1 PARTITION OF fk_partitioned_fk FOR VALUES IN (NULL,500,501,502);
1688 ALTER TABLE fk_partitioned_fk ADD FOREIGN KEY (a, b)
1689 REFERENCES fk_notpartitioned_pk MATCH SIMPLE
1690 ON DELETE SET NULL ON UPDATE SET NULL;
1691 CREATE TABLE fk_partitioned_fk_2 PARTITION OF fk_partitioned_fk FOR VALUES IN (1500,1502);
1692 CREATE TABLE fk_partitioned_fk_3 (a int, b int);
1693 ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_3 FOR VALUES IN (2500,2501,2502,2503);
1694 -- this insert fails
1695 INSERT INTO fk_partitioned_fk (a, b) VALUES (2502, 2503);
1696 ERROR: insert or update on table "fk_partitioned_fk_3" violates foreign key constraint "fk_partitioned_fk_a_b_fkey"
1697 DETAIL: Key (a, b)=(2502, 2503) is not present in table "fk_notpartitioned_pk".
1698 INSERT INTO fk_partitioned_fk_3 (a, b) VALUES (2502, 2503);
1699 ERROR: insert or update on table "fk_partitioned_fk_3" violates foreign key constraint "fk_partitioned_fk_a_b_fkey"
1700 DETAIL: Key (a, b)=(2502, 2503) is not present in table "fk_notpartitioned_pk".
1701 -- but since the FK is MATCH SIMPLE, this one doesn't
1702 INSERT INTO fk_partitioned_fk_3 (a, b) VALUES (2502, NULL);
1703 -- now create the referenced row ...
1704 INSERT INTO fk_notpartitioned_pk VALUES (2502, 2503);
1705 --- and now the same insert work
1706 INSERT INTO fk_partitioned_fk_3 (a, b) VALUES (2502, 2503);
1707 -- this always works
1708 INSERT INTO fk_partitioned_fk (a,b) VALUES (NULL, NULL);
1709 -- MATCH FULL
1710 INSERT INTO fk_notpartitioned_pk VALUES (1, 2);
1711 CREATE TABLE fk_partitioned_fk_full (x int, y int) PARTITION BY RANGE (x);
1712 CREATE TABLE fk_partitioned_fk_full_1 PARTITION OF fk_partitioned_fk_full DEFAULT;
1713 INSERT INTO fk_partitioned_fk_full VALUES (1, NULL);
1714 ALTER TABLE fk_partitioned_fk_full ADD FOREIGN KEY (x, y) REFERENCES fk_notpartitioned_pk MATCH FULL; -- fails
1715 ERROR: insert or update on table "fk_partitioned_fk_full_1" violates foreign key constraint "fk_partitioned_fk_full_x_y_fkey"
1716 DETAIL: MATCH FULL does not allow mixing of null and nonnull key values.
1717 TRUNCATE fk_partitioned_fk_full;
1718 ALTER TABLE fk_partitioned_fk_full ADD FOREIGN KEY (x, y) REFERENCES fk_notpartitioned_pk MATCH FULL;
1719 INSERT INTO fk_partitioned_fk_full VALUES (1, NULL); -- fails
1720 ERROR: insert or update on table "fk_partitioned_fk_full_1" violates foreign key constraint "fk_partitioned_fk_full_x_y_fkey"
1721 DETAIL: MATCH FULL does not allow mixing of null and nonnull key values.
1722 DROP TABLE fk_partitioned_fk_full;
1723 -- ON UPDATE SET NULL
1724 SELECT tableoid::regclass, a, b FROM fk_partitioned_fk WHERE b IS NULL ORDER BY a;
1725 tableoid | a | b
1726 ---------------------+------+---
1727 fk_partitioned_fk_3 | 2502 |
1728 fk_partitioned_fk_1 | |
1729 (2 rows)
1731 UPDATE fk_notpartitioned_pk SET a = a + 1 WHERE a = 2502;
1732 SELECT tableoid::regclass, a, b FROM fk_partitioned_fk WHERE b IS NULL ORDER BY a;
1733 tableoid | a | b
1734 ---------------------+------+---
1735 fk_partitioned_fk_3 | 2502 |
1736 fk_partitioned_fk_1 | |
1737 fk_partitioned_fk_1 | |
1738 (3 rows)
1740 -- ON DELETE SET NULL
1741 INSERT INTO fk_partitioned_fk VALUES (2503, 2503);
1742 SELECT count(*) FROM fk_partitioned_fk WHERE a IS NULL;
1743 count
1744 -------
1745 2
1746 (1 row)
1748 DELETE FROM fk_notpartitioned_pk;
1749 SELECT count(*) FROM fk_partitioned_fk WHERE a IS NULL;
1750 count
1751 -------
1752 3
1753 (1 row)
1755 -- ON UPDATE/DELETE SET DEFAULT
1756 ALTER TABLE fk_partitioned_fk DROP CONSTRAINT fk_partitioned_fk_a_b_fkey;
1757 ALTER TABLE fk_partitioned_fk ADD FOREIGN KEY (a, b)
1758 REFERENCES fk_notpartitioned_pk
1759 ON DELETE SET DEFAULT ON UPDATE SET DEFAULT;
1760 INSERT INTO fk_notpartitioned_pk VALUES (2502, 2503);
1761 INSERT INTO fk_partitioned_fk_3 (a, b) VALUES (2502, 2503);
1762 -- this fails, because the defaults for the referencing table are not present
1763 -- in the referenced table:
1764 UPDATE fk_notpartitioned_pk SET a = 1500 WHERE a = 2502;
1765 ERROR: insert or update on table "fk_partitioned_fk_3" violates foreign key constraint "fk_partitioned_fk_a_b_fkey"
1766 DETAIL: Key (a, b)=(2501, 142857) is not present in table "fk_notpartitioned_pk".
1767 -- but inserting the row we can make it work:
1768 INSERT INTO fk_notpartitioned_pk VALUES (2501, 142857);
1769 UPDATE fk_notpartitioned_pk SET a = 1500 WHERE a = 2502;
1770 SELECT * FROM fk_partitioned_fk WHERE b = 142857;
1771 a | b
1772 ------+--------
1773 2501 | 142857
1774 (1 row)
1776 -- ON DELETE SET NULL column_list
1777 ALTER TABLE fk_partitioned_fk DROP CONSTRAINT fk_partitioned_fk_a_b_fkey;
1778 ALTER TABLE fk_partitioned_fk ADD FOREIGN KEY (a, b)
1779 REFERENCES fk_notpartitioned_pk
1780 ON DELETE SET NULL (a);
1781 BEGIN;
1782 DELETE FROM fk_notpartitioned_pk WHERE b = 142857;
1783 SELECT * FROM fk_partitioned_fk WHERE a IS NOT NULL OR b IS NOT NULL ORDER BY a NULLS LAST;
1784 a | b
1785 ------+--------
1786 2502 |
1787 | 142857
1788 (2 rows)
1790 ROLLBACK;
1791 -- ON DELETE SET DEFAULT column_list
1792 ALTER TABLE fk_partitioned_fk DROP CONSTRAINT fk_partitioned_fk_a_b_fkey;
1793 ALTER TABLE fk_partitioned_fk ADD FOREIGN KEY (a, b)
1794 REFERENCES fk_notpartitioned_pk
1795 ON DELETE SET DEFAULT (a);
1796 BEGIN;
1797 DELETE FROM fk_partitioned_fk;
1798 DELETE FROM fk_notpartitioned_pk;
1799 INSERT INTO fk_notpartitioned_pk VALUES (500, 100000), (2501, 100000);
1800 INSERT INTO fk_partitioned_fk VALUES (500, 100000);
1801 DELETE FROM fk_notpartitioned_pk WHERE a = 500;
1802 SELECT * FROM fk_partitioned_fk ORDER BY a;
1803 a | b
1804 ------+--------
1805 2501 | 100000
1806 (1 row)
1808 ROLLBACK;
1809 -- ON UPDATE/DELETE CASCADE
1810 ALTER TABLE fk_partitioned_fk DROP CONSTRAINT fk_partitioned_fk_a_b_fkey;
1811 ALTER TABLE fk_partitioned_fk ADD FOREIGN KEY (a, b)
1812 REFERENCES fk_notpartitioned_pk
1813 ON DELETE CASCADE ON UPDATE CASCADE;
1814 UPDATE fk_notpartitioned_pk SET a = 2502 WHERE a = 2501;
1815 SELECT * FROM fk_partitioned_fk WHERE b = 142857;
1816 a | b
1817 ------+--------
1818 2502 | 142857
1819 (1 row)
1821 -- Now you see it ...
1822 SELECT * FROM fk_partitioned_fk WHERE b = 142857;
1823 a | b
1824 ------+--------
1825 2502 | 142857
1826 (1 row)
1828 DELETE FROM fk_notpartitioned_pk WHERE b = 142857;
1829 -- now you don't.
1830 SELECT * FROM fk_partitioned_fk WHERE a = 142857;
1831 a | b
1832 ---+---
1833 (0 rows)
1835 -- verify that DROP works
1836 DROP TABLE fk_partitioned_fk_2;
1837 -- Test behavior of the constraint together with attaching and detaching
1838 -- partitions.
1839 CREATE TABLE fk_partitioned_fk_2 PARTITION OF fk_partitioned_fk FOR VALUES IN (1500,1502);
1840 ALTER TABLE fk_partitioned_fk DETACH PARTITION fk_partitioned_fk_2;
1841 BEGIN;
1842 DROP TABLE fk_partitioned_fk;
1843 -- constraint should still be there
1844 \d fk_partitioned_fk_2;
1845 Table "public.fk_partitioned_fk_2"
1846 Column | Type | Collation | Nullable | Default
1847 --------+---------+-----------+----------+---------
1848 a | integer | | | 2501
1849 b | integer | | | 142857
1850 Foreign-key constraints:
1851 "fk_partitioned_fk_a_b_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE
1853 ROLLBACK;
1854 ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES IN (1500,1502);
1855 DROP TABLE fk_partitioned_fk_2;
1856 CREATE TABLE fk_partitioned_fk_2 (b int, c text, a int,
1857 FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk ON UPDATE CASCADE ON DELETE CASCADE);
1858 ALTER TABLE fk_partitioned_fk_2 DROP COLUMN c;
1859 ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES IN (1500,1502);
1860 -- should have only one constraint
1861 \d fk_partitioned_fk_2
1862 Table "public.fk_partitioned_fk_2"
1863 Column | Type | Collation | Nullable | Default
1864 --------+---------+-----------+----------+---------
1865 b | integer | | |
1866 a | integer | | |
1867 Partition of: fk_partitioned_fk FOR VALUES IN (1500, 1502)
1868 Foreign-key constraints:
1869 TABLE "fk_partitioned_fk" CONSTRAINT "fk_partitioned_fk_a_b_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE
1871 DROP TABLE fk_partitioned_fk_2;
1872 CREATE TABLE fk_partitioned_fk_4 (a int, b int, FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE) PARTITION BY RANGE (b, a);
1873 CREATE TABLE fk_partitioned_fk_4_1 PARTITION OF fk_partitioned_fk_4 FOR VALUES FROM (1,1) TO (100,100);
1874 CREATE TABLE fk_partitioned_fk_4_2 (a int, b int, FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE SET NULL);
1875 ALTER TABLE fk_partitioned_fk_4 ATTACH PARTITION fk_partitioned_fk_4_2 FOR VALUES FROM (100,100) TO (1000,1000);
1876 ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_4 FOR VALUES IN (3500,3502);
1877 ALTER TABLE fk_partitioned_fk DETACH PARTITION fk_partitioned_fk_4;
1878 ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_4 FOR VALUES IN (3500,3502);
1879 -- should only have one constraint
1880 \d fk_partitioned_fk_4
1881 Partitioned table "public.fk_partitioned_fk_4"
1882 Column | Type | Collation | Nullable | Default
1883 --------+---------+-----------+----------+---------
1884 a | integer | | |
1885 b | integer | | |
1886 Partition of: fk_partitioned_fk FOR VALUES IN (3500, 3502)
1887 Partition key: RANGE (b, a)
1888 Foreign-key constraints:
1889 TABLE "fk_partitioned_fk" CONSTRAINT "fk_partitioned_fk_a_b_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE
1890 Number of partitions: 2 (Use \d+ to list them.)
1892 \d fk_partitioned_fk_4_1
1893 Table "public.fk_partitioned_fk_4_1"
1894 Column | Type | Collation | Nullable | Default
1895 --------+---------+-----------+----------+---------
1896 a | integer | | |
1897 b | integer | | |
1898 Partition of: fk_partitioned_fk_4 FOR VALUES FROM (1, 1) TO (100, 100)
1899 Foreign-key constraints:
1900 TABLE "fk_partitioned_fk" CONSTRAINT "fk_partitioned_fk_a_b_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE
1902 -- this one has an FK with mismatched properties
1903 \d fk_partitioned_fk_4_2
1904 Table "public.fk_partitioned_fk_4_2"
1905 Column | Type | Collation | Nullable | Default
1906 --------+---------+-----------+----------+---------
1907 a | integer | | |
1908 b | integer | | |
1909 Partition of: fk_partitioned_fk_4 FOR VALUES FROM (100, 100) TO (1000, 1000)
1910 Foreign-key constraints:
1911 "fk_partitioned_fk_4_2_a_b_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE SET NULL
1912 TABLE "fk_partitioned_fk" CONSTRAINT "fk_partitioned_fk_a_b_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE
1914 CREATE TABLE fk_partitioned_fk_5 (a int, b int,
1915 FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE,
1916 FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE)
1917 PARTITION BY RANGE (a);
1918 CREATE TABLE fk_partitioned_fk_5_1 (a int, b int, FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk);
1919 ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_5 FOR VALUES IN (4500);
1920 ALTER TABLE fk_partitioned_fk_5 ATTACH PARTITION fk_partitioned_fk_5_1 FOR VALUES FROM (0) TO (10);
1921 ALTER TABLE fk_partitioned_fk DETACH PARTITION fk_partitioned_fk_5;
1922 ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_5 FOR VALUES IN (4500);
1923 -- this one has two constraints, similar but not quite the one in the parent,
1924 -- so it gets a new one
1925 \d fk_partitioned_fk_5
1926 Partitioned table "public.fk_partitioned_fk_5"
1927 Column | Type | Collation | Nullable | Default
1928 --------+---------+-----------+----------+---------
1929 a | integer | | |
1930 b | integer | | |
1931 Partition of: fk_partitioned_fk FOR VALUES IN (4500)
1932 Partition key: RANGE (a)
1933 Foreign-key constraints:
1934 "fk_partitioned_fk_5_a_b_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE
1935 "fk_partitioned_fk_5_a_b_fkey1" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE
1936 TABLE "fk_partitioned_fk" CONSTRAINT "fk_partitioned_fk_a_b_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE
1937 Number of partitions: 1 (Use \d+ to list them.)
1939 -- verify that it works to reattaching a child with multiple candidate
1940 -- constraints
1941 ALTER TABLE fk_partitioned_fk_5 DETACH PARTITION fk_partitioned_fk_5_1;
1942 ALTER TABLE fk_partitioned_fk_5 ATTACH PARTITION fk_partitioned_fk_5_1 FOR VALUES FROM (0) TO (10);
1943 \d fk_partitioned_fk_5_1
1944 Table "public.fk_partitioned_fk_5_1"
1945 Column | Type | Collation | Nullable | Default
1946 --------+---------+-----------+----------+---------
1947 a | integer | | |
1948 b | integer | | |
1949 Partition of: fk_partitioned_fk_5 FOR VALUES FROM (0) TO (10)
1950 Foreign-key constraints:
1951 "fk_partitioned_fk_5_1_a_b_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b)
1952 TABLE "fk_partitioned_fk_5" CONSTRAINT "fk_partitioned_fk_5_a_b_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE
1953 TABLE "fk_partitioned_fk_5" CONSTRAINT "fk_partitioned_fk_5_a_b_fkey1" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE
1954 TABLE "fk_partitioned_fk" CONSTRAINT "fk_partitioned_fk_a_b_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE
1956 -- verify that attaching a table checks that the existing data satisfies the
1957 -- constraint
1958 CREATE TABLE fk_partitioned_fk_2 (a int, b int) PARTITION BY RANGE (b);
1959 CREATE TABLE fk_partitioned_fk_2_1 PARTITION OF fk_partitioned_fk_2 FOR VALUES FROM (0) TO (1000);
1960 CREATE TABLE fk_partitioned_fk_2_2 PARTITION OF fk_partitioned_fk_2 FOR VALUES FROM (1000) TO (2000);
1961 INSERT INTO fk_partitioned_fk_2 VALUES (1600, 601), (1600, 1601);
1962 ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2
1963 FOR VALUES IN (1600);
1964 ERROR: insert or update on table "fk_partitioned_fk_2_1" violates foreign key constraint "fk_partitioned_fk_a_b_fkey"
1965 DETAIL: Key (a, b)=(1600, 601) is not present in table "fk_notpartitioned_pk".
1966 INSERT INTO fk_notpartitioned_pk VALUES (1600, 601), (1600, 1601);
1967 ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2
1968 FOR VALUES IN (1600);
1969 -- leave these tables around intentionally
1970 -- Verify that attaching a table that's referenced by an existing FK
1971 -- in the parent throws an error
1972 CREATE TABLE fk_partitioned_pk_6 (a int PRIMARY KEY);
1973 CREATE TABLE fk_partitioned_fk_6 (a int REFERENCES fk_partitioned_pk_6) PARTITION BY LIST (a);
1974 ALTER TABLE fk_partitioned_fk_6 ATTACH PARTITION fk_partitioned_pk_6 FOR VALUES IN (1);
1975 ERROR: cannot attach table "fk_partitioned_pk_6" as a partition because it is referenced by foreign key "fk_partitioned_fk_6_a_fkey"
1976 DROP TABLE fk_partitioned_pk_6, fk_partitioned_fk_6;
1977 -- This case is similar to above, but the referenced relation is one level
1978 -- lower in the hierarchy. This one fails in a different way as the above,
1979 -- because we don't bother to protect against this case explicitly. If the
1980 -- current error stops happening, we'll need to add a better protection.
1981 CREATE TABLE fk_partitioned_pk_6 (a int PRIMARY KEY) PARTITION BY list (a);
1982 CREATE TABLE fk_partitioned_pk_61 PARTITION OF fk_partitioned_pk_6 FOR VALUES IN (1);
1983 CREATE TABLE fk_partitioned_fk_6 (a int REFERENCES fk_partitioned_pk_61) PARTITION BY LIST (a);
1984 ALTER TABLE fk_partitioned_fk_6 ATTACH PARTITION fk_partitioned_pk_6 FOR VALUES IN (1);
1985 ERROR: cannot ALTER TABLE "fk_partitioned_pk_61" because it is being used by active queries in this session
1986 DROP TABLE fk_partitioned_pk_6, fk_partitioned_fk_6;
1987 -- test the case when the referenced table is owned by a different user
1988 create role regress_other_partitioned_fk_owner;
1989 grant references on fk_notpartitioned_pk to regress_other_partitioned_fk_owner;
1990 set role regress_other_partitioned_fk_owner;
1991 create table other_partitioned_fk(a int, b int) partition by list (a);
1992 create table other_partitioned_fk_1 partition of other_partitioned_fk
1993 for values in (2048);
1994 insert into other_partitioned_fk
1995 select 2048, x from generate_series(1,10) x;
1996 -- this should fail
1997 alter table other_partitioned_fk add foreign key (a, b)
1998 references fk_notpartitioned_pk(a, b);
1999 ERROR: insert or update on table "other_partitioned_fk_1" violates foreign key constraint "other_partitioned_fk_a_b_fkey"
2000 DETAIL: Key (a, b)=(2048, 1) is not present in table "fk_notpartitioned_pk".
2001 -- add the missing keys and retry
2002 reset role;
2003 insert into fk_notpartitioned_pk (a, b)
2004 select 2048, x from generate_series(1,10) x;
2005 set role regress_other_partitioned_fk_owner;
2006 alter table other_partitioned_fk add foreign key (a, b)
2007 references fk_notpartitioned_pk(a, b);
2008 -- clean up
2009 drop table other_partitioned_fk;
2010 reset role;
2011 revoke all on fk_notpartitioned_pk from regress_other_partitioned_fk_owner;
2012 drop role regress_other_partitioned_fk_owner;
2013 --
2014 -- Test self-referencing foreign key with partition.
2015 -- This should create only one fk constraint per partition
2016 --
2017 CREATE TABLE parted_self_fk (
2018 id bigint NOT NULL PRIMARY KEY,
2019 id_abc bigint,
2020 FOREIGN KEY (id_abc) REFERENCES parted_self_fk(id)
2021 )
2022 PARTITION BY RANGE (id);
2023 CREATE TABLE part1_self_fk (
2024 id bigint NOT NULL PRIMARY KEY,
2025 id_abc bigint
2026 );
2027 ALTER TABLE parted_self_fk ATTACH PARTITION part1_self_fk FOR VALUES FROM (0) TO (10);
2028 CREATE TABLE part2_self_fk PARTITION OF parted_self_fk FOR VALUES FROM (10) TO (20);
2029 CREATE TABLE part3_self_fk ( -- a partitioned partition
2030 id bigint NOT NULL PRIMARY KEY,
2031 id_abc bigint
2032 ) PARTITION BY RANGE (id);
2033 CREATE TABLE part32_self_fk PARTITION OF part3_self_fk FOR VALUES FROM (20) TO (30);
2034 ALTER TABLE parted_self_fk ATTACH PARTITION part3_self_fk FOR VALUES FROM (20) TO (40);
2035 CREATE TABLE part33_self_fk (
2036 id bigint NOT NULL PRIMARY KEY,
2037 id_abc bigint
2038 );
2039 ALTER TABLE part3_self_fk ATTACH PARTITION part33_self_fk FOR VALUES FROM (30) TO (40);
2040 SELECT cr.relname, co.conname, co.contype, co.convalidated,
2041 p.conname AS conparent, p.convalidated, cf.relname AS foreignrel
2042 FROM pg_constraint co
2043 JOIN pg_class cr ON cr.oid = co.conrelid
2044 LEFT JOIN pg_class cf ON cf.oid = co.confrelid
2045 LEFT JOIN pg_constraint p ON p.oid = co.conparentid
2046 WHERE cr.oid IN (SELECT relid FROM pg_partition_tree('parted_self_fk'))
2047 ORDER BY co.contype, cr.relname, co.conname, p.conname;
2048 relname | conname | contype | convalidated | conparent | convalidated | foreignrel
2049 ----------------+----------------------------+---------+--------------+----------------------------+--------------+----------------
2050 part1_self_fk | parted_self_fk_id_abc_fkey | f | t | parted_self_fk_id_abc_fkey | t | parted_self_fk
2051 part2_self_fk | parted_self_fk_id_abc_fkey | f | t | parted_self_fk_id_abc_fkey | t | parted_self_fk
2052 part32_self_fk | parted_self_fk_id_abc_fkey | f | t | parted_self_fk_id_abc_fkey | t | parted_self_fk
2053 part33_self_fk | parted_self_fk_id_abc_fkey | f | t | parted_self_fk_id_abc_fkey | t | parted_self_fk
2054 part3_self_fk | parted_self_fk_id_abc_fkey | f | t | parted_self_fk_id_abc_fkey | t | parted_self_fk
2055 parted_self_fk | parted_self_fk_id_abc_fkey | f | t | | | parted_self_fk
2056 part1_self_fk | part1_self_fk_id_not_null | n | t | | |
2057 part2_self_fk | parted_self_fk_id_not_null | n | t | | |
2058 part32_self_fk | part3_self_fk_id_not_null | n | t | | |
2059 part33_self_fk | part33_self_fk_id_not_null | n | t | | |
2060 part3_self_fk | part3_self_fk_id_not_null | n | t | | |
2061 parted_self_fk | parted_self_fk_id_not_null | n | t | | |
2062 part1_self_fk | part1_self_fk_pkey | p | t | parted_self_fk_pkey | t |
2063 part2_self_fk | part2_self_fk_pkey | p | t | parted_self_fk_pkey | t |
2064 part32_self_fk | part32_self_fk_pkey | p | t | part3_self_fk_pkey | t |
2065 part33_self_fk | part33_self_fk_pkey | p | t | part3_self_fk_pkey | t |
2066 part3_self_fk | part3_self_fk_pkey | p | t | parted_self_fk_pkey | t |
2067 parted_self_fk | parted_self_fk_pkey | p | t | | |
2068 (18 rows)
2070 -- detach and re-attach multiple times just to ensure everything is kosher
2071 ALTER TABLE parted_self_fk DETACH PARTITION part2_self_fk;
2072 ALTER TABLE parted_self_fk ATTACH PARTITION part2_self_fk FOR VALUES FROM (10) TO (20);
2073 ALTER TABLE parted_self_fk DETACH PARTITION part2_self_fk;
2074 ALTER TABLE parted_self_fk ATTACH PARTITION part2_self_fk FOR VALUES FROM (10) TO (20);
2075 SELECT cr.relname, co.conname, co.contype, co.convalidated,
2076 p.conname AS conparent, p.convalidated, cf.relname AS foreignrel
2077 FROM pg_constraint co
2078 JOIN pg_class cr ON cr.oid = co.conrelid
2079 LEFT JOIN pg_class cf ON cf.oid = co.confrelid
2080 LEFT JOIN pg_constraint p ON p.oid = co.conparentid
2081 WHERE cr.oid IN (SELECT relid FROM pg_partition_tree('parted_self_fk'))
2082 ORDER BY co.contype, cr.relname, co.conname, p.conname;
2083 relname | conname | contype | convalidated | conparent | convalidated | foreignrel
2084 ----------------+----------------------------+---------+--------------+----------------------------+--------------+----------------
2085 part1_self_fk | parted_self_fk_id_abc_fkey | f | t | parted_self_fk_id_abc_fkey | t | parted_self_fk
2086 part2_self_fk | parted_self_fk_id_abc_fkey | f | t | parted_self_fk_id_abc_fkey | t | parted_self_fk
2087 part32_self_fk | parted_self_fk_id_abc_fkey | f | t | parted_self_fk_id_abc_fkey | t | parted_self_fk
2088 part33_self_fk | parted_self_fk_id_abc_fkey | f | t | parted_self_fk_id_abc_fkey | t | parted_self_fk
2089 part3_self_fk | parted_self_fk_id_abc_fkey | f | t | parted_self_fk_id_abc_fkey | t | parted_self_fk
2090 parted_self_fk | parted_self_fk_id_abc_fkey | f | t | | | parted_self_fk
2091 part1_self_fk | part1_self_fk_id_not_null | n | t | | |
2092 part2_self_fk | parted_self_fk_id_not_null | n | t | | |
2093 part32_self_fk | part3_self_fk_id_not_null | n | t | | |
2094 part33_self_fk | part33_self_fk_id_not_null | n | t | | |
2095 part3_self_fk | part3_self_fk_id_not_null | n | t | | |
2096 parted_self_fk | parted_self_fk_id_not_null | n | t | | |
2097 part1_self_fk | part1_self_fk_pkey | p | t | parted_self_fk_pkey | t |
2098 part2_self_fk | part2_self_fk_pkey | p | t | parted_self_fk_pkey | t |
2099 part32_self_fk | part32_self_fk_pkey | p | t | part3_self_fk_pkey | t |
2100 part33_self_fk | part33_self_fk_pkey | p | t | part3_self_fk_pkey | t |
2101 part3_self_fk | part3_self_fk_pkey | p | t | parted_self_fk_pkey | t |
2102 parted_self_fk | parted_self_fk_pkey | p | t | | |
2103 (18 rows)
2105 -- Leave this table around, for pg_upgrade/pg_dump tests
2106 -- Test creating a constraint at the parent that already exists in partitions.
2107 -- There should be no duplicated constraints, and attempts to drop the
2108 -- constraint in partitions should raise appropriate errors.
2109 create schema fkpart0
2110 create table pkey (a int primary key)
2111 create table fk_part (a int) partition by list (a)
2112 create table fk_part_1 partition of fk_part
2113 (foreign key (a) references fkpart0.pkey) for values in (1)
2114 create table fk_part_23 partition of fk_part
2115 (foreign key (a) references fkpart0.pkey) for values in (2, 3)
2116 partition by list (a)
2117 create table fk_part_23_2 partition of fk_part_23 for values in (2);
2118 alter table fkpart0.fk_part add foreign key (a) references fkpart0.pkey;
2119 \d fkpart0.fk_part_1 \\ -- should have only one FK
2120 Table "fkpart0.fk_part_1"
2121 Column | Type | Collation | Nullable | Default
2122 --------+---------+-----------+----------+---------
2123 a | integer | | |
2124 Partition of: fkpart0.fk_part FOR VALUES IN (1)
2125 Foreign-key constraints:
2126 TABLE "fkpart0.fk_part" CONSTRAINT "fk_part_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a)
2128 alter table fkpart0.fk_part_1 drop constraint fk_part_1_a_fkey;
2129 ERROR: cannot drop inherited constraint "fk_part_1_a_fkey" of relation "fk_part_1"
2130 \d fkpart0.fk_part_23 \\ -- should have only one FK
2131 Partitioned table "fkpart0.fk_part_23"
2132 Column | Type | Collation | Nullable | Default
2133 --------+---------+-----------+----------+---------
2134 a | integer | | |
2135 Partition of: fkpart0.fk_part FOR VALUES IN (2, 3)
2136 Partition key: LIST (a)
2137 Foreign-key constraints:
2138 TABLE "fkpart0.fk_part" CONSTRAINT "fk_part_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a)
2139 Number of partitions: 1 (Use \d+ to list them.)
2141 \d fkpart0.fk_part_23_2 \\ -- should have only one FK
2142 Table "fkpart0.fk_part_23_2"
2143 Column | Type | Collation | Nullable | Default
2144 --------+---------+-----------+----------+---------
2145 a | integer | | |
2146 Partition of: fkpart0.fk_part_23 FOR VALUES IN (2)
2147 Foreign-key constraints:
2148 TABLE "fkpart0.fk_part" CONSTRAINT "fk_part_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a)
2150 alter table fkpart0.fk_part_23 drop constraint fk_part_23_a_fkey;
2151 ERROR: cannot drop inherited constraint "fk_part_23_a_fkey" of relation "fk_part_23"
2152 alter table fkpart0.fk_part_23_2 drop constraint fk_part_23_a_fkey;
2153 ERROR: cannot drop inherited constraint "fk_part_23_a_fkey" of relation "fk_part_23_2"
2154 create table fkpart0.fk_part_4 partition of fkpart0.fk_part for values in (4);
2155 \d fkpart0.fk_part_4
2156 Table "fkpart0.fk_part_4"
2157 Column | Type | Collation | Nullable | Default
2158 --------+---------+-----------+----------+---------
2159 a | integer | | |
2160 Partition of: fkpart0.fk_part FOR VALUES IN (4)
2161 Foreign-key constraints:
2162 TABLE "fkpart0.fk_part" CONSTRAINT "fk_part_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a)
2164 alter table fkpart0.fk_part_4 drop constraint fk_part_a_fkey;
2165 ERROR: cannot drop inherited constraint "fk_part_a_fkey" of relation "fk_part_4"
2166 create table fkpart0.fk_part_56 partition of fkpart0.fk_part
2167 for values in (5,6) partition by list (a);
2168 create table fkpart0.fk_part_56_5 partition of fkpart0.fk_part_56
2169 for values in (5);
2170 \d fkpart0.fk_part_56
2171 Partitioned table "fkpart0.fk_part_56"
2172 Column | Type | Collation | Nullable | Default
2173 --------+---------+-----------+----------+---------
2174 a | integer | | |
2175 Partition of: fkpart0.fk_part FOR VALUES IN (5, 6)
2176 Partition key: LIST (a)
2177 Foreign-key constraints:
2178 TABLE "fkpart0.fk_part" CONSTRAINT "fk_part_a_fkey" FOREIGN KEY (a) REFERENCES fkpart0.pkey(a)
2179 Number of partitions: 1 (Use \d+ to list them.)
2181 alter table fkpart0.fk_part_56 drop constraint fk_part_a_fkey;
2182 ERROR: cannot drop inherited constraint "fk_part_a_fkey" of relation "fk_part_56"
2183 alter table fkpart0.fk_part_56_5 drop constraint fk_part_a_fkey;
2184 ERROR: cannot drop inherited constraint "fk_part_a_fkey" of relation "fk_part_56_5"
2185 -- verify that attaching and detaching partitions maintains the right set of
2186 -- triggers
2187 create schema fkpart1
2188 create table pkey (a int primary key)
2189 create table fk_part (a int) partition by list (a)
2190 create table fk_part_1 partition of fk_part for values in (1) partition by list (a)
2191 create table fk_part_1_1 partition of fk_part_1 for values in (1);
2192 alter table fkpart1.fk_part add foreign key (a) references fkpart1.pkey;
2193 insert into fkpart1.fk_part values (1); -- should fail
2194 ERROR: insert or update on table "fk_part_1_1" violates foreign key constraint "fk_part_a_fkey"
2195 DETAIL: Key (a)=(1) is not present in table "pkey".
2196 insert into fkpart1.pkey values (1);
2197 insert into fkpart1.fk_part values (1);
2198 delete from fkpart1.pkey where a = 1; -- should fail
2199 ERROR: update or delete on table "pkey" violates foreign key constraint "fk_part_a_fkey" on table "fk_part"
2200 DETAIL: Key (a)=(1) is still referenced from table "fk_part".
2201 alter table fkpart1.fk_part detach partition fkpart1.fk_part_1;
2202 create table fkpart1.fk_part_1_2 partition of fkpart1.fk_part_1 for values in (2);
2203 insert into fkpart1.fk_part_1 values (2); -- should fail
2204 ERROR: insert or update on table "fk_part_1_2" violates foreign key constraint "fk_part_a_fkey"
2205 DETAIL: Key (a)=(2) is not present in table "pkey".
2206 delete from fkpart1.pkey where a = 1;
2207 ERROR: update or delete on table "pkey" violates foreign key constraint "fk_part_a_fkey" on table "fk_part_1"
2208 DETAIL: Key (a)=(1) is still referenced from table "fk_part_1".
2209 -- verify that attaching and detaching partitions manipulates the inheritance
2210 -- properties of their FK constraints correctly
2211 create schema fkpart2
2212 create table pkey (a int primary key)
2213 create table fk_part (a int, constraint fkey foreign key (a) references fkpart2.pkey) partition by list (a)
2214 create table fk_part_1 partition of fkpart2.fk_part for values in (1) partition by list (a)
2215 create table fk_part_1_1 (a int, constraint my_fkey foreign key (a) references fkpart2.pkey);
2216 alter table fkpart2.fk_part_1 attach partition fkpart2.fk_part_1_1 for values in (1);
2217 alter table fkpart2.fk_part_1 drop constraint fkey; -- should fail
2218 ERROR: cannot drop inherited constraint "fkey" of relation "fk_part_1"
2219 alter table fkpart2.fk_part_1_1 drop constraint my_fkey; -- should fail
2220 ERROR: cannot drop inherited constraint "my_fkey" of relation "fk_part_1_1"
2221 alter table fkpart2.fk_part detach partition fkpart2.fk_part_1;
2222 alter table fkpart2.fk_part_1 drop constraint fkey; -- ok
2223 alter table fkpart2.fk_part_1_1 drop constraint my_fkey; -- doesn't exist
2224 ERROR: constraint "my_fkey" of relation "fk_part_1_1" does not exist
2225 -- verify constraint deferrability
2226 create schema fkpart3
2227 create table pkey (a int primary key)
2228 create table fk_part (a int, constraint fkey foreign key (a) references fkpart3.pkey deferrable initially immediate) partition by list (a)
2229 create table fk_part_1 partition of fkpart3.fk_part for values in (1) partition by list (a)
2230 create table fk_part_1_1 partition of fkpart3.fk_part_1 for values in (1)
2231 create table fk_part_2 partition of fkpart3.fk_part for values in (2);
2232 begin;
2233 set constraints fkpart3.fkey deferred;
2234 insert into fkpart3.fk_part values (1);
2235 insert into fkpart3.pkey values (1);
2236 commit;
2237 begin;
2238 set constraints fkpart3.fkey deferred;
2239 delete from fkpart3.pkey;
2240 delete from fkpart3.fk_part;
2241 commit;
2242 drop schema fkpart0, fkpart1, fkpart2, fkpart3 cascade;
2243 NOTICE: drop cascades to 10 other objects
2244 DETAIL: drop cascades to table fkpart3.pkey
2245 drop cascades to table fkpart3.fk_part
2246 drop cascades to table fkpart2.pkey
2247 drop cascades to table fkpart2.fk_part
2248 drop cascades to table fkpart2.fk_part_1
2249 drop cascades to table fkpart1.pkey
2250 drop cascades to table fkpart1.fk_part
2251 drop cascades to table fkpart1.fk_part_1
2252 drop cascades to table fkpart0.pkey
2253 drop cascades to table fkpart0.fk_part
2254 -- Test a partitioned table as referenced table.
2255 -- Verify basic functionality with a regular partition creation and a partition
2256 -- with a different column layout, as well as partitions added (created and
2257 -- attached) after creating the foreign key.
2258 CREATE SCHEMA fkpart3;
2259 SET search_path TO fkpart3;
2260 CREATE TABLE pk (a int PRIMARY KEY) PARTITION BY RANGE (a);
2261 CREATE TABLE pk1 PARTITION OF pk FOR VALUES FROM (0) TO (1000);
2262 CREATE TABLE pk2 (b int, a int);
2263 ALTER TABLE pk2 DROP COLUMN b;
2264 ALTER TABLE pk2 ALTER a SET NOT NULL;
2265 ALTER TABLE pk ATTACH PARTITION pk2 FOR VALUES FROM (1000) TO (2000);
2266 CREATE TABLE fk (a int) PARTITION BY RANGE (a);
2267 CREATE TABLE fk1 PARTITION OF fk FOR VALUES FROM (0) TO (750);
2268 ALTER TABLE fk ADD FOREIGN KEY (a) REFERENCES pk;
2269 CREATE TABLE fk2 (b int, a int) ;
2270 ALTER TABLE fk2 DROP COLUMN b;
2271 ALTER TABLE fk ATTACH PARTITION fk2 FOR VALUES FROM (750) TO (3500);
2272 CREATE TABLE pk3 PARTITION OF pk FOR VALUES FROM (2000) TO (3000);
2273 CREATE TABLE pk4 (LIKE pk);
2274 ALTER TABLE pk ATTACH PARTITION pk4 FOR VALUES FROM (3000) TO (4000);
2275 CREATE TABLE pk5 (c int, b int, a int NOT NULL) PARTITION BY RANGE (a);
2276 ALTER TABLE pk5 DROP COLUMN b, DROP COLUMN c;
2277 CREATE TABLE pk51 PARTITION OF pk5 FOR VALUES FROM (4000) TO (4500);
2278 CREATE TABLE pk52 PARTITION OF pk5 FOR VALUES FROM (4500) TO (5000);
2279 ALTER TABLE pk ATTACH PARTITION pk5 FOR VALUES FROM (4000) TO (5000);
2280 CREATE TABLE fk3 PARTITION OF fk FOR VALUES FROM (3500) TO (5000);
2281 -- these should fail: referenced value not present
2282 INSERT into fk VALUES (1);
2283 ERROR: insert or update on table "fk1" violates foreign key constraint "fk_a_fkey"
2284 DETAIL: Key (a)=(1) is not present in table "pk".
2285 INSERT into fk VALUES (1000);
2286 ERROR: insert or update on table "fk2" violates foreign key constraint "fk_a_fkey"
2287 DETAIL: Key (a)=(1000) is not present in table "pk".
2288 INSERT into fk VALUES (2000);
2289 ERROR: insert or update on table "fk2" violates foreign key constraint "fk_a_fkey"
2290 DETAIL: Key (a)=(2000) is not present in table "pk".
2291 INSERT into fk VALUES (3000);
2292 ERROR: insert or update on table "fk2" violates foreign key constraint "fk_a_fkey"
2293 DETAIL: Key (a)=(3000) is not present in table "pk".
2294 INSERT into fk VALUES (4000);
2295 ERROR: insert or update on table "fk3" violates foreign key constraint "fk_a_fkey"
2296 DETAIL: Key (a)=(4000) is not present in table "pk".
2297 INSERT into fk VALUES (4500);
2298 ERROR: insert or update on table "fk3" violates foreign key constraint "fk_a_fkey"
2299 DETAIL: Key (a)=(4500) is not present in table "pk".
2300 -- insert into the referenced table, now they should work
2301 INSERT into pk VALUES (1), (1000), (2000), (3000), (4000), (4500);
2302 INSERT into fk VALUES (1), (1000), (2000), (3000), (4000), (4500);
2303 -- should fail: referencing value present
2304 DELETE FROM pk WHERE a = 1;
2305 ERROR: update or delete on table "pk1" violates foreign key constraint "fk_a_fkey1" on table "fk"
2306 DETAIL: Key (a)=(1) is still referenced from table "fk".
2307 DELETE FROM pk WHERE a = 1000;
2308 ERROR: update or delete on table "pk2" violates foreign key constraint "fk_a_fkey2" on table "fk"
2309 DETAIL: Key (a)=(1000) is still referenced from table "fk".
2310 DELETE FROM pk WHERE a = 2000;
2311 ERROR: update or delete on table "pk3" violates foreign key constraint "fk_a_fkey3" on table "fk"
2312 DETAIL: Key (a)=(2000) is still referenced from table "fk".
2313 DELETE FROM pk WHERE a = 3000;
2314 ERROR: update or delete on table "pk4" violates foreign key constraint "fk_a_fkey4" on table "fk"
2315 DETAIL: Key (a)=(3000) is still referenced from table "fk".
2316 DELETE FROM pk WHERE a = 4000;
2317 ERROR: update or delete on table "pk51" violates foreign key constraint "fk_a_fkey6" on table "fk"
2318 DETAIL: Key (a)=(4000) is still referenced from table "fk".
2319 DELETE FROM pk WHERE a = 4500;
2320 ERROR: update or delete on table "pk52" violates foreign key constraint "fk_a_fkey7" on table "fk"
2321 DETAIL: Key (a)=(4500) is still referenced from table "fk".
2322 UPDATE pk SET a = 2 WHERE a = 1;
2323 ERROR: update or delete on table "pk1" violates foreign key constraint "fk_a_fkey1" on table "fk"
2324 DETAIL: Key (a)=(1) is still referenced from table "fk".
2325 UPDATE pk SET a = 1002 WHERE a = 1000;
2326 ERROR: update or delete on table "pk2" violates foreign key constraint "fk_a_fkey2" on table "fk"
2327 DETAIL: Key (a)=(1000) is still referenced from table "fk".
2328 UPDATE pk SET a = 2002 WHERE a = 2000;
2329 ERROR: update or delete on table "pk3" violates foreign key constraint "fk_a_fkey3" on table "fk"
2330 DETAIL: Key (a)=(2000) is still referenced from table "fk".
2331 UPDATE pk SET a = 3002 WHERE a = 3000;
2332 ERROR: update or delete on table "pk4" violates foreign key constraint "fk_a_fkey4" on table "fk"
2333 DETAIL: Key (a)=(3000) is still referenced from table "fk".
2334 UPDATE pk SET a = 4002 WHERE a = 4000;
2335 ERROR: update or delete on table "pk51" violates foreign key constraint "fk_a_fkey6" on table "fk"
2336 DETAIL: Key (a)=(4000) is still referenced from table "fk".
2337 UPDATE pk SET a = 4502 WHERE a = 4500;
2338 ERROR: update or delete on table "pk52" violates foreign key constraint "fk_a_fkey7" on table "fk"
2339 DETAIL: Key (a)=(4500) is still referenced from table "fk".
2340 -- now they should work
2341 DELETE FROM fk;
2342 UPDATE pk SET a = 2 WHERE a = 1;
2343 DELETE FROM pk WHERE a = 2;
2344 UPDATE pk SET a = 1002 WHERE a = 1000;
2345 DELETE FROM pk WHERE a = 1002;
2346 UPDATE pk SET a = 2002 WHERE a = 2000;
2347 DELETE FROM pk WHERE a = 2002;
2348 UPDATE pk SET a = 3002 WHERE a = 3000;
2349 DELETE FROM pk WHERE a = 3002;
2350 UPDATE pk SET a = 4002 WHERE a = 4000;
2351 DELETE FROM pk WHERE a = 4002;
2352 UPDATE pk SET a = 4502 WHERE a = 4500;
2353 DELETE FROM pk WHERE a = 4502;
2354 CREATE SCHEMA fkpart4;
2355 SET search_path TO fkpart4;
2356 -- dropping/detaching PARTITIONs is prevented if that would break
2357 -- a foreign key's existing data
2358 CREATE TABLE droppk (a int PRIMARY KEY) PARTITION BY RANGE (a);
2359 CREATE TABLE droppk1 PARTITION OF droppk FOR VALUES FROM (0) TO (1000);
2360 CREATE TABLE droppk_d PARTITION OF droppk DEFAULT;
2361 CREATE TABLE droppk2 PARTITION OF droppk FOR VALUES FROM (1000) TO (2000)
2362 PARTITION BY RANGE (a);
2363 CREATE TABLE droppk21 PARTITION OF droppk2 FOR VALUES FROM (1000) TO (1400);
2364 CREATE TABLE droppk2_d PARTITION OF droppk2 DEFAULT;
2365 INSERT into droppk VALUES (1), (1000), (1500), (2000);
2366 CREATE TABLE dropfk (a int REFERENCES droppk);
2367 INSERT into dropfk VALUES (1), (1000), (1500), (2000);
2368 -- these should all fail
2369 ALTER TABLE droppk DETACH PARTITION droppk_d;
2370 ERROR: removing partition "droppk_d" violates foreign key constraint "dropfk_a_fkey5"
2371 DETAIL: Key (a)=(2000) is still referenced from table "dropfk".
2372 ALTER TABLE droppk2 DETACH PARTITION droppk2_d;
2373 ERROR: removing partition "droppk2_d" violates foreign key constraint "dropfk_a_fkey4"
2374 DETAIL: Key (a)=(1500) is still referenced from table "dropfk".
2375 ALTER TABLE droppk DETACH PARTITION droppk1;
2376 ERROR: removing partition "droppk1" violates foreign key constraint "dropfk_a_fkey1"
2377 DETAIL: Key (a)=(1) is still referenced from table "dropfk".
2378 ALTER TABLE droppk DETACH PARTITION droppk2;
2379 ERROR: removing partition "droppk2" violates foreign key constraint "dropfk_a_fkey2"
2380 DETAIL: Key (a)=(1000) is still referenced from table "dropfk".
2381 ALTER TABLE droppk2 DETACH PARTITION droppk21;
2382 ERROR: removing partition "droppk21" violates foreign key constraint "dropfk_a_fkey3"
2383 DETAIL: Key (a)=(1000) is still referenced from table "dropfk".
2384 -- dropping partitions is disallowed
2385 DROP TABLE droppk_d;
2386 ERROR: cannot drop table droppk_d because other objects depend on it
2387 DETAIL: constraint dropfk_a_fkey on table dropfk depends on table droppk_d
2388 HINT: Use DROP ... CASCADE to drop the dependent objects too.
2389 DROP TABLE droppk2_d;
2390 ERROR: cannot drop table droppk2_d because other objects depend on it
2391 DETAIL: constraint dropfk_a_fkey on table dropfk depends on table droppk2_d
2392 HINT: Use DROP ... CASCADE to drop the dependent objects too.
2393 DROP TABLE droppk1;
2394 ERROR: cannot drop table droppk1 because other objects depend on it
2395 DETAIL: constraint dropfk_a_fkey on table dropfk depends on table droppk1
2396 HINT: Use DROP ... CASCADE to drop the dependent objects too.
2397 DROP TABLE droppk2;
2398 ERROR: cannot drop table droppk2 because other objects depend on it
2399 DETAIL: constraint dropfk_a_fkey on table dropfk depends on table droppk2
2400 HINT: Use DROP ... CASCADE to drop the dependent objects too.
2401 DROP TABLE droppk21;
2402 ERROR: cannot drop table droppk21 because other objects depend on it
2403 DETAIL: constraint dropfk_a_fkey on table dropfk depends on table droppk21
2404 HINT: Use DROP ... CASCADE to drop the dependent objects too.
2405 DELETE FROM dropfk;
2406 -- dropping partitions is disallowed, even when no referencing values
2407 DROP TABLE droppk_d;
2408 ERROR: cannot drop table droppk_d because other objects depend on it
2409 DETAIL: constraint dropfk_a_fkey on table dropfk depends on table droppk_d
2410 HINT: Use DROP ... CASCADE to drop the dependent objects too.
2411 DROP TABLE droppk2_d;
2412 ERROR: cannot drop table droppk2_d because other objects depend on it
2413 DETAIL: constraint dropfk_a_fkey on table dropfk depends on table droppk2_d
2414 HINT: Use DROP ... CASCADE to drop the dependent objects too.
2415 DROP TABLE droppk1;
2416 ERROR: cannot drop table droppk1 because other objects depend on it
2417 DETAIL: constraint dropfk_a_fkey on table dropfk depends on table droppk1
2418 HINT: Use DROP ... CASCADE to drop the dependent objects too.
2419 -- but DETACH is allowed, and DROP afterwards works
2420 ALTER TABLE droppk2 DETACH PARTITION droppk21;
2421 DROP TABLE droppk2;
2422 ERROR: cannot drop table droppk2 because other objects depend on it
2423 DETAIL: constraint dropfk_a_fkey on table dropfk depends on table droppk2
2424 HINT: Use DROP ... CASCADE to drop the dependent objects too.
2425 -- Verify that initial constraint creation and cloning behave correctly
2426 CREATE SCHEMA fkpart5;
2427 SET search_path TO fkpart5;
2428 CREATE TABLE pk (a int PRIMARY KEY) PARTITION BY LIST (a);
2429 CREATE TABLE pk1 PARTITION OF pk FOR VALUES IN (1) PARTITION BY LIST (a);
2430 CREATE TABLE pk11 PARTITION OF pk1 FOR VALUES IN (1);
2431 CREATE TABLE fk (a int) PARTITION BY LIST (a);
2432 CREATE TABLE fk1 PARTITION OF fk FOR VALUES IN (1) PARTITION BY LIST (a);
2433 CREATE TABLE fk11 PARTITION OF fk1 FOR VALUES IN (1);
2434 ALTER TABLE fk ADD FOREIGN KEY (a) REFERENCES pk;
2435 CREATE TABLE pk2 PARTITION OF pk FOR VALUES IN (2);
2436 CREATE TABLE pk3 (a int NOT NULL) PARTITION BY LIST (a);
2437 CREATE TABLE pk31 PARTITION OF pk3 FOR VALUES IN (31);
2438 CREATE TABLE pk32 (b int, a int NOT NULL);
2439 ALTER TABLE pk32 DROP COLUMN b;
2440 ALTER TABLE pk3 ATTACH PARTITION pk32 FOR VALUES IN (32);
2441 ALTER TABLE pk ATTACH PARTITION pk3 FOR VALUES IN (31, 32);
2442 CREATE TABLE fk2 PARTITION OF fk FOR VALUES IN (2);
2443 CREATE TABLE fk3 (b int, a int);
2444 ALTER TABLE fk3 DROP COLUMN b;
2445 ALTER TABLE fk ATTACH PARTITION fk3 FOR VALUES IN (3);
2446 SELECT pg_describe_object('pg_constraint'::regclass, oid, 0), confrelid::regclass,
2447 CASE WHEN conparentid <> 0 THEN pg_describe_object('pg_constraint'::regclass, conparentid, 0) ELSE 'TOP' END
2448 FROM pg_catalog.pg_constraint
2449 WHERE conrelid IN (SELECT relid FROM pg_partition_tree('fk'))
2450 ORDER BY conrelid::regclass::text, conname;
2451 pg_describe_object | confrelid | case
2452 ------------------------------------+-----------+-----------------------------------
2453 constraint fk_a_fkey on table fk | pk | TOP
2454 constraint fk_a_fkey1 on table fk | pk1 | constraint fk_a_fkey on table fk
2455 constraint fk_a_fkey2 on table fk | pk11 | constraint fk_a_fkey1 on table fk
2456 constraint fk_a_fkey3 on table fk | pk2 | constraint fk_a_fkey on table fk
2457 constraint fk_a_fkey4 on table fk | pk3 | constraint fk_a_fkey on table fk
2458 constraint fk_a_fkey5 on table fk | pk31 | constraint fk_a_fkey4 on table fk
2459 constraint fk_a_fkey6 on table fk | pk32 | constraint fk_a_fkey4 on table fk
2460 constraint fk_a_fkey on table fk1 | pk | constraint fk_a_fkey on table fk
2461 constraint fk_a_fkey on table fk11 | pk | constraint fk_a_fkey on table fk1
2462 constraint fk_a_fkey on table fk2 | pk | constraint fk_a_fkey on table fk
2463 constraint fk_a_fkey on table fk3 | pk | constraint fk_a_fkey on table fk
2464 (11 rows)
2466 CREATE TABLE fk4 (LIKE fk);
2467 INSERT INTO fk4 VALUES (50);
2468 ALTER TABLE fk ATTACH PARTITION fk4 FOR VALUES IN (50);
2469 ERROR: insert or update on table "fk4" violates foreign key constraint "fk_a_fkey"
2470 DETAIL: Key (a)=(50) is not present in table "pk".
2471 -- Verify constraint deferrability
2472 CREATE SCHEMA fkpart9;
2473 SET search_path TO fkpart9;
2474 CREATE TABLE pk (a int PRIMARY KEY) PARTITION BY LIST (a);
2475 CREATE TABLE pk1 PARTITION OF pk FOR VALUES IN (1, 2) PARTITION BY LIST (a);
2476 CREATE TABLE pk11 PARTITION OF pk1 FOR VALUES IN (1);
2477 CREATE TABLE pk3 PARTITION OF pk FOR VALUES IN (3);
2478 CREATE TABLE fk (a int REFERENCES pk DEFERRABLE INITIALLY IMMEDIATE);
2479 INSERT INTO fk VALUES (1); -- should fail
2480 ERROR: insert or update on table "fk" violates foreign key constraint "fk_a_fkey"
2481 DETAIL: Key (a)=(1) is not present in table "pk".
2482 BEGIN;
2483 SET CONSTRAINTS fk_a_fkey DEFERRED;
2484 INSERT INTO fk VALUES (1);
2485 COMMIT; -- should fail
2486 ERROR: insert or update on table "fk" violates foreign key constraint "fk_a_fkey"
2487 DETAIL: Key (a)=(1) is not present in table "pk".
2488 BEGIN;
2489 SET CONSTRAINTS fk_a_fkey DEFERRED;
2490 INSERT INTO fk VALUES (1);
2491 INSERT INTO pk VALUES (1);
2492 COMMIT; -- OK
2493 BEGIN;
2494 SET CONSTRAINTS fk_a_fkey DEFERRED;
2495 DELETE FROM pk WHERE a = 1;
2496 DELETE FROM fk WHERE a = 1;
2497 COMMIT; -- OK
2498 -- Verify constraint deferrability when changed by ALTER
2499 -- Partitioned table at referencing end
2500 CREATE TABLE pt(f1 int, f2 int, f3 int, PRIMARY KEY(f1,f2));
2501 CREATE TABLE ref(f1 int, f2 int, f3 int)
2502 PARTITION BY list(f1);
2503 CREATE TABLE ref1 PARTITION OF ref FOR VALUES IN (1);
2504 CREATE TABLE ref2 PARTITION OF ref FOR VALUES in (2);
2505 ALTER TABLE ref ADD FOREIGN KEY(f1,f2) REFERENCES pt;
2506 ALTER TABLE ref ALTER CONSTRAINT ref_f1_f2_fkey
2507 DEFERRABLE INITIALLY DEFERRED;
2508 INSERT INTO pt VALUES(1,2,3);
2509 INSERT INTO ref VALUES(1,2,3);
2510 BEGIN;
2511 DELETE FROM pt;
2512 DELETE FROM ref;
2513 ABORT;
2514 DROP TABLE pt, ref;
2515 -- Multi-level partitioning at referencing end
2516 CREATE TABLE pt(f1 int, f2 int, f3 int, PRIMARY KEY(f1,f2));
2517 CREATE TABLE ref(f1 int, f2 int, f3 int)
2518 PARTITION BY list(f1);
2519 CREATE TABLE ref1_2 PARTITION OF ref FOR VALUES IN (1, 2) PARTITION BY list (f2);
2520 CREATE TABLE ref1 PARTITION OF ref1_2 FOR VALUES IN (1);
2521 CREATE TABLE ref2 PARTITION OF ref1_2 FOR VALUES IN (2) PARTITION BY list (f2);
2522 CREATE TABLE ref22 PARTITION OF ref2 FOR VALUES IN (2);
2523 ALTER TABLE ref ADD FOREIGN KEY(f1,f2) REFERENCES pt;
2524 INSERT INTO pt VALUES(1,2,3);
2525 INSERT INTO ref VALUES(1,2,3);
2526 ALTER TABLE ref22 ALTER CONSTRAINT ref_f1_f2_fkey
2527 DEFERRABLE INITIALLY IMMEDIATE; -- fails
2528 ERROR: cannot alter constraint "ref_f1_f2_fkey" on relation "ref22"
2529 DETAIL: Constraint "ref_f1_f2_fkey" is derived from constraint "ref_f1_f2_fkey" of relation "ref".
2530 HINT: You may alter the constraint it derives from instead.
2531 ALTER TABLE ref ALTER CONSTRAINT ref_f1_f2_fkey
2532 DEFERRABLE INITIALLY DEFERRED;
2533 BEGIN;
2534 DELETE FROM pt;
2535 DELETE FROM ref;
2536 ABORT;
2537 DROP TABLE pt, ref;
2538 -- Partitioned table at referenced end
2539 CREATE TABLE pt(f1 int, f2 int, f3 int, PRIMARY KEY(f1,f2))
2540 PARTITION BY LIST(f1);
2541 CREATE TABLE pt1 PARTITION OF pt FOR VALUES IN (1);
2542 CREATE TABLE pt2 PARTITION OF pt FOR VALUES IN (2);
2543 CREATE TABLE ref(f1 int, f2 int, f3 int);
2544 ALTER TABLE ref ADD FOREIGN KEY(f1,f2) REFERENCES pt;
2545 ALTER TABLE ref ALTER CONSTRAINT ref_f1_f2_fkey
2546 DEFERRABLE INITIALLY DEFERRED;
2547 INSERT INTO pt VALUES(1,2,3);
2548 INSERT INTO ref VALUES(1,2,3);
2549 BEGIN;
2550 DELETE FROM pt;
2551 DELETE FROM ref;
2552 ABORT;
2553 DROP TABLE pt, ref;
2554 -- Multi-level partitioning at referenced end
2555 CREATE TABLE pt(f1 int, f2 int, f3 int, PRIMARY KEY(f1,f2))
2556 PARTITION BY LIST(f1);
2557 CREATE TABLE pt1_2 PARTITION OF pt FOR VALUES IN (1, 2) PARTITION BY LIST (f1);
2558 CREATE TABLE pt1 PARTITION OF pt1_2 FOR VALUES IN (1);
2559 CREATE TABLE pt2 PARTITION OF pt1_2 FOR VALUES IN (2);
2560 CREATE TABLE ref(f1 int, f2 int, f3 int);
2561 ALTER TABLE ref ADD FOREIGN KEY(f1,f2) REFERENCES pt;
2562 ALTER TABLE ref ALTER CONSTRAINT ref_f1_f2_fkey1
2563 DEFERRABLE INITIALLY DEFERRED; -- fails
2564 ERROR: cannot alter constraint "ref_f1_f2_fkey1" on relation "ref"
2565 DETAIL: Constraint "ref_f1_f2_fkey1" is derived from constraint "ref_f1_f2_fkey" of relation "ref".
2566 HINT: You may alter the constraint it derives from instead.
2567 ALTER TABLE ref ALTER CONSTRAINT ref_f1_f2_fkey
2568 DEFERRABLE INITIALLY DEFERRED;
2569 INSERT INTO pt VALUES(1,2,3);
2570 INSERT INTO ref VALUES(1,2,3);
2571 BEGIN;
2572 DELETE FROM pt;
2573 DELETE FROM ref;
2574 ABORT;
2575 DROP TABLE pt, ref;
2576 DROP SCHEMA fkpart9 CASCADE;
2577 NOTICE: drop cascades to 2 other objects
2578 DETAIL: drop cascades to table pk
2579 drop cascades to table fk
2580 -- Verify ON UPDATE/DELETE behavior
2581 CREATE SCHEMA fkpart6;
2582 SET search_path TO fkpart6;
2583 CREATE TABLE pk (a int PRIMARY KEY) PARTITION BY RANGE (a);
2584 CREATE TABLE pk1 PARTITION OF pk FOR VALUES FROM (1) TO (100) PARTITION BY RANGE (a);
2585 CREATE TABLE pk11 PARTITION OF pk1 FOR VALUES FROM (1) TO (50);
2586 CREATE TABLE pk12 PARTITION OF pk1 FOR VALUES FROM (50) TO (100);
2587 CREATE TABLE fk (a int) PARTITION BY RANGE (a);
2588 CREATE TABLE fk1 PARTITION OF fk FOR VALUES FROM (1) TO (100) PARTITION BY RANGE (a);
2589 CREATE TABLE fk11 PARTITION OF fk1 FOR VALUES FROM (1) TO (10);
2590 CREATE TABLE fk12 PARTITION OF fk1 FOR VALUES FROM (10) TO (100);
2591 ALTER TABLE fk ADD FOREIGN KEY (a) REFERENCES pk ON UPDATE CASCADE ON DELETE CASCADE;
2592 CREATE TABLE fk_d PARTITION OF fk DEFAULT;
2593 INSERT INTO pk VALUES (1);
2594 INSERT INTO fk VALUES (1);
2595 UPDATE pk SET a = 20;
2596 SELECT tableoid::regclass, * FROM fk;
2597 tableoid | a
2598 ----------+----
2599 fk12 | 20
2600 (1 row)
2602 DELETE FROM pk WHERE a = 20;
2603 SELECT tableoid::regclass, * FROM fk;
2604 tableoid | a
2605 ----------+---
2606 (0 rows)
2608 DROP TABLE fk;
2609 TRUNCATE TABLE pk;
2610 INSERT INTO pk VALUES (20), (50);
2611 CREATE TABLE fk (a int) PARTITION BY RANGE (a);
2612 CREATE TABLE fk1 PARTITION OF fk FOR VALUES FROM (1) TO (100) PARTITION BY RANGE (a);
2613 CREATE TABLE fk11 PARTITION OF fk1 FOR VALUES FROM (1) TO (10);
2614 CREATE TABLE fk12 PARTITION OF fk1 FOR VALUES FROM (10) TO (100);
2615 ALTER TABLE fk ADD FOREIGN KEY (a) REFERENCES pk ON UPDATE SET NULL ON DELETE SET NULL;
2616 CREATE TABLE fk_d PARTITION OF fk DEFAULT;
2617 INSERT INTO fk VALUES (20), (50);
2618 UPDATE pk SET a = 21 WHERE a = 20;
2619 DELETE FROM pk WHERE a = 50;
2620 SELECT tableoid::regclass, * FROM fk;
2621 tableoid | a
2622 ----------+---
2623 fk_d |
2624 fk_d |
2625 (2 rows)
2627 DROP TABLE fk;
2628 TRUNCATE TABLE pk;
2629 INSERT INTO pk VALUES (20), (30), (50);
2630 CREATE TABLE fk (id int, a int DEFAULT 50) PARTITION BY RANGE (a);
2631 CREATE TABLE fk1 PARTITION OF fk FOR VALUES FROM (1) TO (100) PARTITION BY RANGE (a);
2632 CREATE TABLE fk11 PARTITION OF fk1 FOR VALUES FROM (1) TO (10);
2633 CREATE TABLE fk12 PARTITION OF fk1 FOR VALUES FROM (10) TO (100);
2634 ALTER TABLE fk ADD FOREIGN KEY (a) REFERENCES pk ON UPDATE SET DEFAULT ON DELETE SET DEFAULT;
2635 CREATE TABLE fk_d PARTITION OF fk DEFAULT;
2636 INSERT INTO fk VALUES (1, 20), (2, 30);
2637 DELETE FROM pk WHERE a = 20 RETURNING *;
2638 a
2639 ----
2640 20
2641 (1 row)
2643 UPDATE pk SET a = 90 WHERE a = 30 RETURNING *;
2644 a
2645 ----
2646 90
2647 (1 row)
2649 SELECT tableoid::regclass, * FROM fk;
2650 tableoid | id | a
2651 ----------+----+----
2652 fk12 | 1 | 50
2653 fk12 | 2 | 50
2654 (2 rows)
2656 DROP TABLE fk;
2657 TRUNCATE TABLE pk;
2658 INSERT INTO pk VALUES (20), (30);
2659 CREATE TABLE fk (a int DEFAULT 50) PARTITION BY RANGE (a);
2660 CREATE TABLE fk1 PARTITION OF fk FOR VALUES FROM (1) TO (100) PARTITION BY RANGE (a);
2661 CREATE TABLE fk11 PARTITION OF fk1 FOR VALUES FROM (1) TO (10);
2662 CREATE TABLE fk12 PARTITION OF fk1 FOR VALUES FROM (10) TO (100);
2663 ALTER TABLE fk ADD FOREIGN KEY (a) REFERENCES pk ON UPDATE RESTRICT ON DELETE RESTRICT;
2664 CREATE TABLE fk_d PARTITION OF fk DEFAULT;
2665 INSERT INTO fk VALUES (20), (30);
2666 DELETE FROM pk WHERE a = 20;
2667 ERROR: update or delete on table "pk11" violates RESTRICT setting of foreign key constraint "fk_a_fkey2" on table "fk"
2668 DETAIL: Key (a)=(20) is referenced from table "fk".
2669 UPDATE pk SET a = 90 WHERE a = 30;
2670 ERROR: update or delete on table "pk" violates RESTRICT setting of foreign key constraint "fk_a_fkey" on table "fk"
2671 DETAIL: Key (a)=(30) is referenced from table "fk".
2672 SELECT tableoid::regclass, * FROM fk;
2673 tableoid | a
2674 ----------+----
2675 fk12 | 20
2676 fk12 | 30
2677 (2 rows)
2679 DROP TABLE fk;
2680 -- test for reported bug: relispartition not set
2681 -- https://postgr.es/m/CA+HiwqHMsRtRYRWYTWavKJ8x14AFsv7bmAV46mYwnfD3vy8goQ@mail.gmail.com
2682 CREATE SCHEMA fkpart7
2683 CREATE TABLE pkpart (a int) PARTITION BY LIST (a)
2684 CREATE TABLE pkpart1 PARTITION OF pkpart FOR VALUES IN (1);
2685 ALTER TABLE fkpart7.pkpart1 ADD PRIMARY KEY (a);
2686 ALTER TABLE fkpart7.pkpart ADD PRIMARY KEY (a);
2687 CREATE TABLE fkpart7.fk (a int REFERENCES fkpart7.pkpart);
2688 DROP SCHEMA fkpart7 CASCADE;
2689 NOTICE: drop cascades to 2 other objects
2690 DETAIL: drop cascades to table fkpart7.pkpart
2691 drop cascades to table fkpart7.fk
2692 -- ensure we check partitions are "not used" when dropping constraints
2693 CREATE SCHEMA fkpart8
2694 CREATE TABLE tbl1(f1 int PRIMARY KEY)
2695 CREATE TABLE tbl2(f1 int REFERENCES tbl1 DEFERRABLE INITIALLY DEFERRED) PARTITION BY RANGE(f1)
2696 CREATE TABLE tbl2_p1 PARTITION OF tbl2 FOR VALUES FROM (minvalue) TO (maxvalue);
2697 INSERT INTO fkpart8.tbl1 VALUES(1);
2698 BEGIN;
2699 INSERT INTO fkpart8.tbl2 VALUES(1);
2700 ALTER TABLE fkpart8.tbl2 DROP CONSTRAINT tbl2_f1_fkey;
2701 ERROR: cannot ALTER TABLE "tbl2_p1" because it has pending trigger events
2702 COMMIT;
2703 DROP SCHEMA fkpart8 CASCADE;
2704 NOTICE: drop cascades to 2 other objects
2705 DETAIL: drop cascades to table fkpart8.tbl1
2706 drop cascades to table fkpart8.tbl2
2707 -- ensure FK referencing a multi-level partitioned table are
2708 -- enforce reference to sub-children.
2709 CREATE SCHEMA fkpart9
2710 CREATE TABLE pk (a INT PRIMARY KEY) PARTITION BY RANGE (a)
2711 CREATE TABLE fk (
2712 fk_a INT REFERENCES pk(a) ON DELETE CASCADE
2713 )
2714 CREATE TABLE pk1 PARTITION OF pk FOR VALUES FROM (30) TO (50) PARTITION BY RANGE (a)
2715 CREATE TABLE pk11 PARTITION OF pk1 FOR VALUES FROM (30) TO (40);
2716 INSERT INTO fkpart9.pk VALUES (35);
2717 INSERT INTO fkpart9.fk VALUES (35);
2718 DELETE FROM fkpart9.pk WHERE a=35;
2719 SELECT * FROM fkpart9.pk;
2720 a
2721 ---
2722 (0 rows)
2724 SELECT * FROM fkpart9.fk;
2725 fk_a
2726 ------
2727 (0 rows)
2729 DROP SCHEMA fkpart9 CASCADE;
2730 NOTICE: drop cascades to 2 other objects
2731 DETAIL: drop cascades to table fkpart9.pk
2732 drop cascades to table fkpart9.fk
2733 -- test that ri_Check_Pk_Match() scans the correct partition for a deferred
2734 -- ON DELETE/UPDATE NO ACTION constraint
2735 CREATE SCHEMA fkpart10
2736 CREATE TABLE tbl1(f1 int PRIMARY KEY) PARTITION BY RANGE(f1)
2737 CREATE TABLE tbl1_p1 PARTITION OF tbl1 FOR VALUES FROM (minvalue) TO (1)
2738 CREATE TABLE tbl1_p2 PARTITION OF tbl1 FOR VALUES FROM (1) TO (maxvalue)
2739 CREATE TABLE tbl2(f1 int REFERENCES tbl1 DEFERRABLE INITIALLY DEFERRED)
2740 CREATE TABLE tbl3(f1 int PRIMARY KEY) PARTITION BY RANGE(f1)
2741 CREATE TABLE tbl3_p1 PARTITION OF tbl3 FOR VALUES FROM (minvalue) TO (1)
2742 CREATE TABLE tbl3_p2 PARTITION OF tbl3 FOR VALUES FROM (1) TO (maxvalue)
2743 CREATE TABLE tbl4(f1 int REFERENCES tbl3 DEFERRABLE INITIALLY DEFERRED);
2744 INSERT INTO fkpart10.tbl1 VALUES (0), (1);
2745 INSERT INTO fkpart10.tbl2 VALUES (0), (1);
2746 INSERT INTO fkpart10.tbl3 VALUES (-2), (-1), (0);
2747 INSERT INTO fkpart10.tbl4 VALUES (-2), (-1);
2748 BEGIN;
2749 DELETE FROM fkpart10.tbl1 WHERE f1 = 0;
2750 UPDATE fkpart10.tbl1 SET f1 = 2 WHERE f1 = 1;
2751 INSERT INTO fkpart10.tbl1 VALUES (0), (1);
2752 COMMIT;
2753 -- test that cross-partition updates correctly enforces the foreign key
2754 -- restriction (specifically testing INITIALLY DEFERRED)
2755 BEGIN;
2756 UPDATE fkpart10.tbl1 SET f1 = 3 WHERE f1 = 0;
2757 UPDATE fkpart10.tbl3 SET f1 = f1 * -1;
2758 INSERT INTO fkpart10.tbl1 VALUES (4);
2759 COMMIT;
2760 ERROR: update or delete on table "tbl1" violates foreign key constraint "tbl2_f1_fkey" on table "tbl2"
2761 DETAIL: Key (f1)=(0) is still referenced from table "tbl2".
2762 BEGIN;
2763 UPDATE fkpart10.tbl3 SET f1 = f1 * -1;
2764 UPDATE fkpart10.tbl3 SET f1 = f1 + 3;
2765 UPDATE fkpart10.tbl1 SET f1 = 3 WHERE f1 = 0;
2766 INSERT INTO fkpart10.tbl1 VALUES (0);
2767 COMMIT;
2768 ERROR: update or delete on table "tbl3" violates foreign key constraint "tbl4_f1_fkey" on table "tbl4"
2769 DETAIL: Key (f1)=(-2) is still referenced from table "tbl4".
2770 BEGIN;
2771 UPDATE fkpart10.tbl3 SET f1 = f1 * -1;
2772 UPDATE fkpart10.tbl1 SET f1 = 3 WHERE f1 = 0;
2773 INSERT INTO fkpart10.tbl1 VALUES (0);
2774 INSERT INTO fkpart10.tbl3 VALUES (-2), (-1);
2775 COMMIT;
2776 -- test where the updated table now has both an IMMEDIATE and a DEFERRED
2777 -- constraint pointing into it
2778 CREATE TABLE fkpart10.tbl5(f1 int REFERENCES fkpart10.tbl3);
2779 INSERT INTO fkpart10.tbl5 VALUES (-2), (-1);
2780 BEGIN;
2781 UPDATE fkpart10.tbl3 SET f1 = f1 * -3;
2782 ERROR: update or delete on table "tbl3" violates foreign key constraint "tbl5_f1_fkey" on table "tbl5"
2783 DETAIL: Key (f1)=(-2) is still referenced from table "tbl5".
2784 COMMIT;
2785 -- Now test where the row referenced from the table with an IMMEDIATE
2786 -- constraint stays in place, while those referenced from the table with a
2787 -- DEFERRED constraint don't.
2788 DELETE FROM fkpart10.tbl5;
2789 INSERT INTO fkpart10.tbl5 VALUES (0);
2790 BEGIN;
2791 UPDATE fkpart10.tbl3 SET f1 = f1 * -3;
2792 COMMIT;
2793 ERROR: update or delete on table "tbl3" violates foreign key constraint "tbl4_f1_fkey" on table "tbl4"
2794 DETAIL: Key (f1)=(-2) is still referenced from table "tbl4".
2795 DROP SCHEMA fkpart10 CASCADE;
2796 NOTICE: drop cascades to 5 other objects
2797 DETAIL: drop cascades to table fkpart10.tbl1
2798 drop cascades to table fkpart10.tbl2
2799 drop cascades to table fkpart10.tbl3
2800 drop cascades to table fkpart10.tbl4
2801 drop cascades to table fkpart10.tbl5
2802 -- verify foreign keys are enforced during cross-partition updates,
2803 -- especially on the PK side
2804 CREATE SCHEMA fkpart11
2805 CREATE TABLE pk (a INT PRIMARY KEY, b text) PARTITION BY LIST (a)
2806 CREATE TABLE fk (
2807 a INT,
2808 CONSTRAINT fkey FOREIGN KEY (a) REFERENCES pk(a) ON UPDATE CASCADE ON DELETE CASCADE
2809 )
2810 CREATE TABLE fk_parted (
2811 a INT PRIMARY KEY,
2812 CONSTRAINT fkey FOREIGN KEY (a) REFERENCES pk(a) ON UPDATE CASCADE ON DELETE CASCADE
2813 ) PARTITION BY LIST (a)
2814 CREATE TABLE fk_another (
2815 a INT,
2816 CONSTRAINT fkey FOREIGN KEY (a) REFERENCES fk_parted (a) ON UPDATE CASCADE ON DELETE CASCADE
2817 )
2818 CREATE TABLE pk1 PARTITION OF pk FOR VALUES IN (1, 2) PARTITION BY LIST (a)
2819 CREATE TABLE pk2 PARTITION OF pk FOR VALUES IN (3)
2820 CREATE TABLE pk3 PARTITION OF pk FOR VALUES IN (4)
2821 CREATE TABLE fk1 PARTITION OF fk_parted FOR VALUES IN (1, 2)
2822 CREATE TABLE fk2 PARTITION OF fk_parted FOR VALUES IN (3)
2823 CREATE TABLE fk3 PARTITION OF fk_parted FOR VALUES IN (4);
2824 CREATE TABLE fkpart11.pk11 (b text, a int NOT NULL);
2825 ALTER TABLE fkpart11.pk1 ATTACH PARTITION fkpart11.pk11 FOR VALUES IN (1);
2826 CREATE TABLE fkpart11.pk12 (b text, c int, a int NOT NULL);
2827 ALTER TABLE fkpart11.pk12 DROP c;
2828 ALTER TABLE fkpart11.pk1 ATTACH PARTITION fkpart11.pk12 FOR VALUES IN (2);
2829 INSERT INTO fkpart11.pk VALUES (1, 'xxx'), (3, 'yyy');
2830 INSERT INTO fkpart11.fk VALUES (1), (3);
2831 INSERT INTO fkpart11.fk_parted VALUES (1), (3);
2832 INSERT INTO fkpart11.fk_another VALUES (1), (3);
2833 -- moves 2 rows from one leaf partition to another, with both updates being
2834 -- cascaded to fk and fk_parted. Updates of fk_parted, of which one is
2835 -- cross-partition (3 -> 4), are further cascaded to fk_another.
2836 UPDATE fkpart11.pk SET a = a + 1 RETURNING tableoid::pg_catalog.regclass, *;
2837 tableoid | a | b
2838 ---------------+---+-----
2839 fkpart11.pk12 | 2 | xxx
2840 fkpart11.pk3 | 4 | yyy
2841 (2 rows)
2843 SELECT tableoid::pg_catalog.regclass, * FROM fkpart11.fk;
2844 tableoid | a
2845 -------------+---
2846 fkpart11.fk | 2
2847 fkpart11.fk | 4
2848 (2 rows)
2850 SELECT tableoid::pg_catalog.regclass, * FROM fkpart11.fk_parted;
2851 tableoid | a
2852 --------------+---
2853 fkpart11.fk1 | 2
2854 fkpart11.fk3 | 4
2855 (2 rows)
2857 SELECT tableoid::pg_catalog.regclass, * FROM fkpart11.fk_another;
2858 tableoid | a
2859 ---------------------+---
2860 fkpart11.fk_another | 2
2861 fkpart11.fk_another | 4
2862 (2 rows)
2864 -- let's try with the foreign key pointing at tables in the partition tree
2865 -- that are not the same as the query's target table
2866 -- 1. foreign key pointing into a non-root ancestor
2867 --
2868 -- A cross-partition update on the root table will fail, because we currently
2869 -- can't enforce the foreign keys pointing into a non-leaf partition
2870 ALTER TABLE fkpart11.fk DROP CONSTRAINT fkey;
2871 DELETE FROM fkpart11.fk WHERE a = 4;
2872 ALTER TABLE fkpart11.fk ADD CONSTRAINT fkey FOREIGN KEY (a) REFERENCES fkpart11.pk1 (a) ON UPDATE CASCADE ON DELETE CASCADE;
2873 UPDATE fkpart11.pk SET a = a - 1;
2874 ERROR: cannot move tuple across partitions when a non-root ancestor of the source partition is directly referenced in a foreign key
2875 DETAIL: A foreign key points to ancestor "pk1" but not the root ancestor "pk".
2876 HINT: Consider defining the foreign key on table "pk".
2877 -- it's okay though if the non-leaf partition is updated directly
2878 UPDATE fkpart11.pk1 SET a = a - 1;
2879 SELECT tableoid::pg_catalog.regclass, * FROM fkpart11.pk;
2880 tableoid | a | b
2881 ---------------+---+-----
2882 fkpart11.pk11 | 1 | xxx
2883 fkpart11.pk3 | 4 | yyy
2884 (2 rows)
2886 SELECT tableoid::pg_catalog.regclass, * FROM fkpart11.fk;
2887 tableoid | a
2888 -------------+---
2889 fkpart11.fk | 1
2890 (1 row)
2892 SELECT tableoid::pg_catalog.regclass, * FROM fkpart11.fk_parted;
2893 tableoid | a
2894 --------------+---
2895 fkpart11.fk1 | 1
2896 fkpart11.fk3 | 4
2897 (2 rows)
2899 SELECT tableoid::pg_catalog.regclass, * FROM fkpart11.fk_another;
2900 tableoid | a
2901 ---------------------+---
2902 fkpart11.fk_another | 4
2903 fkpart11.fk_another | 1
2904 (2 rows)
2906 -- 2. foreign key pointing into a single leaf partition
2907 --
2908 -- A cross-partition update that deletes from the pointed-to leaf partition
2909 -- is allowed to succeed
2910 ALTER TABLE fkpart11.fk DROP CONSTRAINT fkey;
2911 ALTER TABLE fkpart11.fk ADD CONSTRAINT fkey FOREIGN KEY (a) REFERENCES fkpart11.pk11 (a) ON UPDATE CASCADE ON DELETE CASCADE;
2912 -- will delete (1) from p11 which is cascaded to fk
2913 UPDATE fkpart11.pk SET a = a + 1 WHERE a = 1;
2914 SELECT tableoid::pg_catalog.regclass, * FROM fkpart11.fk;
2915 tableoid | a
2916 ----------+---
2917 (0 rows)
2919 DROP TABLE fkpart11.fk;
2920 -- check that regular and deferrable AR triggers on the PK tables
2921 -- still work as expected
2922 CREATE FUNCTION fkpart11.print_row () RETURNS TRIGGER LANGUAGE plpgsql AS $$
2923 BEGIN
2924 RAISE NOTICE 'TABLE: %, OP: %, OLD: %, NEW: %', TG_RELNAME, TG_OP, OLD, NEW;
2925 RETURN NULL;
2926 END;
2927 $$;
2928 CREATE TRIGGER trig_upd_pk AFTER UPDATE ON fkpart11.pk FOR EACH ROW EXECUTE FUNCTION fkpart11.print_row();
2929 CREATE TRIGGER trig_del_pk AFTER DELETE ON fkpart11.pk FOR EACH ROW EXECUTE FUNCTION fkpart11.print_row();
2930 CREATE TRIGGER trig_ins_pk AFTER INSERT ON fkpart11.pk FOR EACH ROW EXECUTE FUNCTION fkpart11.print_row();
2931 CREATE CONSTRAINT TRIGGER trig_upd_fk_parted AFTER UPDATE ON fkpart11.fk_parted INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION fkpart11.print_row();
2932 CREATE CONSTRAINT TRIGGER trig_del_fk_parted AFTER DELETE ON fkpart11.fk_parted INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION fkpart11.print_row();
2933 CREATE CONSTRAINT TRIGGER trig_ins_fk_parted AFTER INSERT ON fkpart11.fk_parted INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION fkpart11.print_row();
2934 UPDATE fkpart11.pk SET a = 3 WHERE a = 4;
2935 NOTICE: TABLE: pk3, OP: DELETE, OLD: (4,yyy), NEW: <NULL>
2936 NOTICE: TABLE: pk2, OP: INSERT, OLD: <NULL>, NEW: (3,yyy)
2937 NOTICE: TABLE: fk3, OP: DELETE, OLD: (4), NEW: <NULL>
2938 NOTICE: TABLE: fk2, OP: INSERT, OLD: <NULL>, NEW: (3)
2939 UPDATE fkpart11.pk SET a = 1 WHERE a = 2;
2940 NOTICE: TABLE: pk12, OP: DELETE, OLD: (xxx,2), NEW: <NULL>
2941 NOTICE: TABLE: pk11, OP: INSERT, OLD: <NULL>, NEW: (xxx,1)
2942 NOTICE: TABLE: fk1, OP: UPDATE, OLD: (2), NEW: (1)
2943 DROP SCHEMA fkpart11 CASCADE;
2944 NOTICE: drop cascades to 4 other objects
2945 DETAIL: drop cascades to table fkpart11.pk
2946 drop cascades to table fkpart11.fk_parted
2947 drop cascades to table fkpart11.fk_another
2948 drop cascades to function fkpart11.print_row()
2949 -- When a table is attached as partition to a partitioned table that has
2950 -- a foreign key to another partitioned table, it acquires a clone of the
2951 -- FK. Upon detach, this clone is not removed, but instead becomes an
2952 -- independent FK. If it then attaches to the partitioned table again,
2953 -- the FK from the parent "takes over" ownership of the independent FK rather
2954 -- than creating a separate one.
2955 CREATE SCHEMA fkpart12
2956 CREATE TABLE fk_p ( id int, jd int, PRIMARY KEY(id, jd)) PARTITION BY list (id)
2957 CREATE TABLE fk_p_1 PARTITION OF fk_p FOR VALUES IN (1) PARTITION BY list (jd)
2958 CREATE TABLE fk_p_1_1 PARTITION OF fk_p_1 FOR VALUES IN (1)
2959 CREATE TABLE fk_p_1_2 (x int, y int, jd int NOT NULL, id int NOT NULL)
2960 CREATE TABLE fk_p_2 PARTITION OF fk_p FOR VALUES IN (2) PARTITION BY list (jd)
2961 CREATE TABLE fk_p_2_1 PARTITION OF fk_p_2 FOR VALUES IN (1)
2962 CREATE TABLE fk_p_2_2 PARTITION OF fk_p_2 FOR VALUES IN (2)
2963 CREATE TABLE fk_r_1 ( p_jd int NOT NULL, x int, id int PRIMARY KEY, p_id int NOT NULL)
2964 CREATE TABLE fk_r_2 ( id int PRIMARY KEY, p_id int NOT NULL, p_jd int NOT NULL) PARTITION BY list (id)
2965 CREATE TABLE fk_r_2_1 PARTITION OF fk_r_2 FOR VALUES IN (2, 1)
2966 CREATE TABLE fk_r ( id int PRIMARY KEY, p_id int NOT NULL, p_jd int NOT NULL,
2967 FOREIGN KEY (p_id, p_jd) REFERENCES fk_p (id, jd)
2968 ) PARTITION BY list (id);
2969 SET search_path TO fkpart12;
2970 ALTER TABLE fk_p_1_2 DROP COLUMN x, DROP COLUMN y;
2971 ALTER TABLE fk_p_1 ATTACH PARTITION fk_p_1_2 FOR VALUES IN (2);
2972 ALTER TABLE fk_r_1 DROP COLUMN x;
2973 INSERT INTO fk_p VALUES (1, 1);
2974 ALTER TABLE fk_r ATTACH PARTITION fk_r_1 FOR VALUES IN (1);
2975 ALTER TABLE fk_r ATTACH PARTITION fk_r_2 FOR VALUES IN (2);
2976 \d fk_r_2
2977 Partitioned table "fkpart12.fk_r_2"
2978 Column | Type | Collation | Nullable | Default
2979 --------+---------+-----------+----------+---------
2980 id | integer | | not null |
2981 p_id | integer | | not null |
2982 p_jd | integer | | not null |
2983 Partition of: fk_r FOR VALUES IN (2)
2984 Partition key: LIST (id)
2985 Indexes:
2986 "fk_r_2_pkey" PRIMARY KEY, btree (id)
2987 Foreign-key constraints:
2988 TABLE "fk_r" CONSTRAINT "fk_r_p_id_p_jd_fkey" FOREIGN KEY (p_id, p_jd) REFERENCES fk_p(id, jd)
2989 Number of partitions: 1 (Use \d+ to list them.)
2991 INSERT INTO fk_r VALUES (1, 1, 1);
2992 INSERT INTO fk_r VALUES (2, 2, 1);
2993 ERROR: insert or update on table "fk_r_2_1" violates foreign key constraint "fk_r_p_id_p_jd_fkey"
2994 DETAIL: Key (p_id, p_jd)=(2, 1) is not present in table "fk_p".
2995 ALTER TABLE fk_r DETACH PARTITION fk_r_1;
2996 ALTER TABLE fk_r DETACH PARTITION fk_r_2;
2997 \d fk_r_2
2998 Partitioned table "fkpart12.fk_r_2"
2999 Column | Type | Collation | Nullable | Default
3000 --------+---------+-----------+----------+---------
3001 id | integer | | not null |
3002 p_id | integer | | not null |
3003 p_jd | integer | | not null |
3004 Partition key: LIST (id)
3005 Indexes:
3006 "fk_r_2_pkey" PRIMARY KEY, btree (id)
3007 Foreign-key constraints:
3008 "fk_r_p_id_p_jd_fkey" FOREIGN KEY (p_id, p_jd) REFERENCES fk_p(id, jd)
3009 Number of partitions: 1 (Use \d+ to list them.)
3011 INSERT INTO fk_r_1 (id, p_id, p_jd) VALUES (2, 1, 2); -- should fail
3012 ERROR: insert or update on table "fk_r_1" violates foreign key constraint "fk_r_p_id_p_jd_fkey"
3013 DETAIL: Key (p_id, p_jd)=(1, 2) is not present in table "fk_p".
3014 DELETE FROM fk_p; -- should fail
3015 ERROR: update or delete on table "fk_p_1_1" violates foreign key constraint "fk_r_1_p_id_p_jd_fkey1" on table "fk_r_1"
3016 DETAIL: Key (id, jd)=(1, 1) is still referenced from table "fk_r_1".
3017 ALTER TABLE fk_r ATTACH PARTITION fk_r_1 FOR VALUES IN (1);
3018 ALTER TABLE fk_r ATTACH PARTITION fk_r_2 FOR VALUES IN (2);
3019 \d fk_r_2
3020 Partitioned table "fkpart12.fk_r_2"
3021 Column | Type | Collation | Nullable | Default
3022 --------+---------+-----------+----------+---------
3023 id | integer | | not null |
3024 p_id | integer | | not null |
3025 p_jd | integer | | not null |
3026 Partition of: fk_r FOR VALUES IN (2)
3027 Partition key: LIST (id)
3028 Indexes:
3029 "fk_r_2_pkey" PRIMARY KEY, btree (id)
3030 Foreign-key constraints:
3031 TABLE "fk_r" CONSTRAINT "fk_r_p_id_p_jd_fkey" FOREIGN KEY (p_id, p_jd) REFERENCES fk_p(id, jd)
3032 Number of partitions: 1 (Use \d+ to list them.)
3034 DELETE FROM fk_p; -- should fail
3035 ERROR: update or delete on table "fk_p_1_1" violates foreign key constraint "fk_r_p_id_p_jd_fkey2" on table "fk_r"
3036 DETAIL: Key (id, jd)=(1, 1) is still referenced from table "fk_r".
3037 -- these should all fail
3038 ALTER TABLE fk_r_1 DROP CONSTRAINT fk_r_p_id_p_jd_fkey;
3039 ERROR: cannot drop inherited constraint "fk_r_p_id_p_jd_fkey" of relation "fk_r_1"
3040 ALTER TABLE fk_r DROP CONSTRAINT fk_r_p_id_p_jd_fkey1;
3041 ERROR: cannot drop inherited constraint "fk_r_p_id_p_jd_fkey1" of relation "fk_r"
3042 ALTER TABLE fk_r_2 DROP CONSTRAINT fk_r_p_id_p_jd_fkey;
3043 ERROR: cannot drop inherited constraint "fk_r_p_id_p_jd_fkey" of relation "fk_r_2"
3044 SET client_min_messages TO warning;
3045 DROP SCHEMA fkpart12 CASCADE;
3046 RESET client_min_messages;
3047 RESET search_path;