6 -- First test, check and cascade
8 CREATE TABLE PKTABLE ( ptest1 int PRIMARY KEY, ptest2 text );
9 NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable'
10 CREATE TABLE FKTABLE ( ftest1 int REFERENCES PKTABLE MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE, ftest2 int );
11 NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
12 -- Insert test data into PKTABLE
13 INSERT INTO PKTABLE VALUES (1, 'Test1');
14 INSERT INTO PKTABLE VALUES (2, 'Test2');
15 INSERT INTO PKTABLE VALUES (3, 'Test3');
16 INSERT INTO PKTABLE VALUES (4, 'Test4');
17 INSERT INTO PKTABLE VALUES (5, 'Test5');
18 -- Insert successful rows into FK TABLE
19 INSERT INTO FKTABLE VALUES (1, 2);
20 INSERT INTO FKTABLE VALUES (2, 3);
21 INSERT INTO FKTABLE VALUES (3, 4);
22 INSERT INTO FKTABLE VALUES (NULL, 1);
23 -- Insert a failed row into FK TABLE
24 INSERT INTO FKTABLE VALUES (100, 2);
25 ERROR: <unnamed> referential integrity violation - key referenced from fktable not found in pktable
27 SELECT * FROM FKTABLE;
36 -- Delete a row from PK TABLE
37 DELETE FROM PKTABLE WHERE ptest1=1;
38 -- Check FKTABLE for removal of matched row
39 SELECT * FROM FKTABLE;
47 -- Update a row from PK TABLE
48 UPDATE PKTABLE SET ptest1=1 WHERE ptest1=2;
49 -- Check FKTABLE for update of matched row
50 SELECT * FROM FKTABLE;
59 NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "fktable"
62 -- check set NULL and table constraint on multiple columns
64 CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 text, PRIMARY KEY(ptest1, ptest2) );
65 NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable'
66 CREATE TABLE FKTABLE ( ftest1 int, ftest2 int, ftest3 int, CONSTRAINT constrname FOREIGN KEY(ftest1, ftest2)
67 REFERENCES PKTABLE MATCH FULL ON DELETE SET NULL ON UPDATE SET NULL);
68 NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
69 -- Insert test data into PKTABLE
70 INSERT INTO PKTABLE VALUES (1, 2, 'Test1');
71 INSERT INTO PKTABLE VALUES (1, 3, 'Test1-2');
72 INSERT INTO PKTABLE VALUES (2, 4, 'Test2');
73 INSERT INTO PKTABLE VALUES (3, 6, 'Test3');
74 INSERT INTO PKTABLE VALUES (4, 8, 'Test4');
75 INSERT INTO PKTABLE VALUES (5, 10, 'Test5');
76 -- Insert successful rows into FK TABLE
77 INSERT INTO FKTABLE VALUES (1, 2, 4);
78 INSERT INTO FKTABLE VALUES (1, 3, 5);
79 INSERT INTO FKTABLE VALUES (2, 4, 8);
80 INSERT INTO FKTABLE VALUES (3, 6, 12);
81 INSERT INTO FKTABLE VALUES (NULL, NULL, 0);
82 -- Insert failed rows into FK TABLE
83 INSERT INTO FKTABLE VALUES (100, 2, 4);
84 ERROR: constrname referential integrity violation - key referenced from fktable not found in pktable
85 INSERT INTO FKTABLE VALUES (2, 2, 4);
86 ERROR: constrname referential integrity violation - key referenced from fktable not found in pktable
87 INSERT INTO FKTABLE VALUES (NULL, 2, 4);
88 ERROR: constrname referential integrity violation - MATCH FULL doesn't allow mixing of NULL and NON-NULL key values
89 INSERT INTO FKTABLE VALUES (1, NULL, 4);
90 ERROR: constrname referential integrity violation - MATCH FULL doesn't allow mixing of NULL and NON-NULL key values
92 SELECT * FROM FKTABLE;
93 ftest1 | ftest2 | ftest3
94 --------+--------+--------
102 -- Delete a row from PK TABLE
103 DELETE FROM PKTABLE WHERE ptest1=1 and ptest2=2;
104 -- Check FKTABLE for removal of matched row
105 SELECT * FROM FKTABLE;
106 ftest1 | ftest2 | ftest3
107 --------+--------+--------
115 -- Delete another row from PK TABLE
116 DELETE FROM PKTABLE WHERE ptest1=5 and ptest2=10;
117 -- Check FKTABLE (should be no change)
118 SELECT * FROM FKTABLE;
119 ftest1 | ftest2 | ftest3
120 --------+--------+--------
128 -- Update a row from PK TABLE
129 UPDATE PKTABLE SET ptest1=1 WHERE ptest1=2;
130 -- Check FKTABLE for update of matched row
131 SELECT * FROM FKTABLE;
132 ftest1 | ftest2 | ftest3
133 --------+--------+--------
142 NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "fktable"
145 -- check set default and table constraint on multiple columns
147 CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 text, PRIMARY KEY(ptest1, ptest2) );
148 NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable'
149 CREATE TABLE FKTABLE ( ftest1 int DEFAULT -1, ftest2 int DEFAULT -2, ftest3 int, CONSTRAINT constrname2 FOREIGN KEY(ftest1, ftest2)
150 REFERENCES PKTABLE MATCH FULL ON DELETE SET DEFAULT ON UPDATE SET DEFAULT);
151 NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
152 -- Insert a value in PKTABLE for default
153 INSERT INTO PKTABLE VALUES (-1, -2, 'The Default!');
154 -- Insert test data into PKTABLE
155 INSERT INTO PKTABLE VALUES (1, 2, 'Test1');
156 INSERT INTO PKTABLE VALUES (1, 3, 'Test1-2');
157 INSERT INTO PKTABLE VALUES (2, 4, 'Test2');
158 INSERT INTO PKTABLE VALUES (3, 6, 'Test3');
159 INSERT INTO PKTABLE VALUES (4, 8, 'Test4');
160 INSERT INTO PKTABLE VALUES (5, 10, 'Test5');
161 -- Insert successful rows into FK TABLE
162 INSERT INTO FKTABLE VALUES (1, 2, 4);
163 INSERT INTO FKTABLE VALUES (1, 3, 5);
164 INSERT INTO FKTABLE VALUES (2, 4, 8);
165 INSERT INTO FKTABLE VALUES (3, 6, 12);
166 INSERT INTO FKTABLE VALUES (NULL, NULL, 0);
167 -- Insert failed rows into FK TABLE
168 INSERT INTO FKTABLE VALUES (100, 2, 4);
169 ERROR: constrname2 referential integrity violation - key referenced from fktable not found in pktable
170 INSERT INTO FKTABLE VALUES (2, 2, 4);
171 ERROR: constrname2 referential integrity violation - key referenced from fktable not found in pktable
172 INSERT INTO FKTABLE VALUES (NULL, 2, 4);
173 ERROR: constrname2 referential integrity violation - MATCH FULL doesn't allow mixing of NULL and NON-NULL key values
174 INSERT INTO FKTABLE VALUES (1, NULL, 4);
175 ERROR: constrname2 referential integrity violation - MATCH FULL doesn't allow mixing of NULL and NON-NULL key values
177 SELECT * FROM FKTABLE;
178 ftest1 | ftest2 | ftest3
179 --------+--------+--------
187 -- Delete a row from PK TABLE
188 DELETE FROM PKTABLE WHERE ptest1=1 and ptest2=2;
189 -- Check FKTABLE to check for removal
190 SELECT * FROM FKTABLE;
191 ftest1 | ftest2 | ftest3
192 --------+--------+--------
200 -- Delete another row from PK TABLE
201 DELETE FROM PKTABLE WHERE ptest1=5 and ptest2=10;
202 -- Check FKTABLE (should be no change)
203 SELECT * FROM FKTABLE;
204 ftest1 | ftest2 | ftest3
205 --------+--------+--------
213 -- Update a row from PK TABLE
214 UPDATE PKTABLE SET ptest1=1 WHERE ptest1=2;
215 -- Check FKTABLE for update of matched row
216 SELECT * FROM FKTABLE;
217 ftest1 | ftest2 | ftest3
218 --------+--------+--------
227 NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "fktable"
230 -- First test, check with no on delete or on update
232 CREATE TABLE PKTABLE ( ptest1 int PRIMARY KEY, ptest2 text );
233 NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable'
234 CREATE TABLE FKTABLE ( ftest1 int REFERENCES PKTABLE MATCH FULL, ftest2 int );
235 NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
236 -- Insert test data into PKTABLE
237 INSERT INTO PKTABLE VALUES (1, 'Test1');
238 INSERT INTO PKTABLE VALUES (2, 'Test2');
239 INSERT INTO PKTABLE VALUES (3, 'Test3');
240 INSERT INTO PKTABLE VALUES (4, 'Test4');
241 INSERT INTO PKTABLE VALUES (5, 'Test5');
242 -- Insert successful rows into FK TABLE
243 INSERT INTO FKTABLE VALUES (1, 2);
244 INSERT INTO FKTABLE VALUES (2, 3);
245 INSERT INTO FKTABLE VALUES (3, 4);
246 INSERT INTO FKTABLE VALUES (NULL, 1);
247 -- Insert a failed row into FK TABLE
248 INSERT INTO FKTABLE VALUES (100, 2);
249 ERROR: <unnamed> referential integrity violation - key referenced from fktable not found in pktable
251 SELECT * FROM FKTABLE;
261 SELECT * FROM PKTABLE;
271 -- Delete a row from PK TABLE (should fail)
272 DELETE FROM PKTABLE WHERE ptest1=1;
273 ERROR: <unnamed> referential integrity violation - key in pktable still referenced from fktable
274 -- Delete a row from PK TABLE (should succeed)
275 DELETE FROM PKTABLE WHERE ptest1=5;
276 -- Check PKTABLE for deletes
277 SELECT * FROM PKTABLE;
286 -- Update a row from PK TABLE (should fail)
287 UPDATE PKTABLE SET ptest1=0 WHERE ptest1=2;
288 ERROR: <unnamed> referential integrity violation - key in pktable still referenced from fktable
289 -- Update a row from PK TABLE (should succeed)
290 UPDATE PKTABLE SET ptest1=0 WHERE ptest1=4;
291 -- Check PKTABLE for updates
292 SELECT * FROM PKTABLE;
302 NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "fktable"
305 -- Base test restricting update/delete
306 CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2, ptest3) );
307 NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable'
308 CREATE TABLE FKTABLE ( ftest1 int, ftest2 int, ftest3 int, ftest4 int, CONSTRAINT constrname3
309 FOREIGN KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE);
310 NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
311 -- Insert Primary Key values
312 INSERT INTO PKTABLE VALUES (1, 2, 3, 'test1');
313 INSERT INTO PKTABLE VALUES (1, 3, 3, 'test2');
314 INSERT INTO PKTABLE VALUES (2, 3, 4, 'test3');
315 INSERT INTO PKTABLE VALUES (2, 4, 5, 'test4');
316 -- Insert Foreign Key values
317 INSERT INTO FKTABLE VALUES (1, 2, 3, 1);
318 INSERT INTO FKTABLE VALUES (NULL, 2, 3, 2);
319 INSERT INTO FKTABLE VALUES (2, NULL, 3, 3);
320 INSERT INTO FKTABLE VALUES (NULL, 2, 7, 4);
321 INSERT INTO FKTABLE VALUES (NULL, 3, 4, 5);
322 -- Insert a failed values
323 INSERT INTO FKTABLE VALUES (1, 2, 7, 6);
324 ERROR: constrname3 referential integrity violation - key referenced from fktable not found in pktable
326 SELECT * from FKTABLE;
327 ftest1 | ftest2 | ftest3 | ftest4
328 --------+--------+--------+--------
336 -- Try to update something that should fail
337 UPDATE PKTABLE set ptest2=5 where ptest2=2;
338 ERROR: constrname3 referential integrity violation - key in pktable still referenced from fktable
339 -- Try to update something that should succeed
340 UPDATE PKTABLE set ptest1=1 WHERE ptest2=3;
341 -- Try to delete something that should fail
342 DELETE FROM PKTABLE where ptest1=1 and ptest2=2 and ptest3=3;
343 ERROR: constrname3 referential integrity violation - key in pktable still referenced from fktable
344 -- Try to delete something that should work
345 DELETE FROM PKTABLE where ptest1=2;
346 -- Show PKTABLE and FKTABLE
347 SELECT * from PKTABLE;
348 ptest1 | ptest2 | ptest3 | ptest4
349 --------+--------+--------+--------
355 SELECT * from FKTABLE;
356 ftest1 | ftest2 | ftest3 | ftest4
357 --------+--------+--------+--------
366 NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "pktable"
367 NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "pktable"
369 -- cascade update/delete
370 CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2, ptest3) );
371 NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable'
372 CREATE TABLE FKTABLE ( ftest1 int, ftest2 int, ftest3 int, ftest4 int, CONSTRAINT constrname3
373 FOREIGN KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE
374 ON DELETE CASCADE ON UPDATE CASCADE);
375 NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
376 -- Insert Primary Key values
377 INSERT INTO PKTABLE VALUES (1, 2, 3, 'test1');
378 INSERT INTO PKTABLE VALUES (1, 3, 3, 'test2');
379 INSERT INTO PKTABLE VALUES (2, 3, 4, 'test3');
380 INSERT INTO PKTABLE VALUES (2, 4, 5, 'test4');
381 -- Insert Foreign Key values
382 INSERT INTO FKTABLE VALUES (1, 2, 3, 1);
383 INSERT INTO FKTABLE VALUES (NULL, 2, 3, 2);
384 INSERT INTO FKTABLE VALUES (2, NULL, 3, 3);
385 INSERT INTO FKTABLE VALUES (NULL, 2, 7, 4);
386 INSERT INTO FKTABLE VALUES (NULL, 3, 4, 5);
387 -- Insert a failed values
388 INSERT INTO FKTABLE VALUES (1, 2, 7, 6);
389 ERROR: constrname3 referential integrity violation - key referenced from fktable not found in pktable
391 SELECT * from FKTABLE;
392 ftest1 | ftest2 | ftest3 | ftest4
393 --------+--------+--------+--------
401 -- Try to update something that will cascade
402 UPDATE PKTABLE set ptest2=5 where ptest2=2;
403 -- Try to update something that should not cascade
404 UPDATE PKTABLE set ptest1=1 WHERE ptest2=3;
405 -- Show PKTABLE and FKTABLE
406 SELECT * from PKTABLE;
407 ptest1 | ptest2 | ptest3 | ptest4
408 --------+--------+--------+--------
415 SELECT * from FKTABLE;
416 ftest1 | ftest2 | ftest3 | ftest4
417 --------+--------+--------+--------
425 -- Try to delete something that should cascade
426 DELETE FROM PKTABLE where ptest1=1 and ptest2=5 and ptest3=3;
427 -- Show PKTABLE and FKTABLE
428 SELECT * from PKTABLE;
429 ptest1 | ptest2 | ptest3 | ptest4
430 --------+--------+--------+--------
436 SELECT * from FKTABLE;
437 ftest1 | ftest2 | ftest3 | ftest4
438 --------+--------+--------+--------
445 -- Try to delete something that should not have a cascade
446 DELETE FROM PKTABLE where ptest1=2;
447 -- Show PKTABLE and FKTABLE
448 SELECT * from PKTABLE;
449 ptest1 | ptest2 | ptest3 | ptest4
450 --------+--------+--------+--------
455 SELECT * from FKTABLE;
456 ftest1 | ftest2 | ftest3 | ftest4
457 --------+--------+--------+--------
465 NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "pktable"
466 NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "pktable"
468 -- set null update / set default delete
469 CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2, ptest3) );
470 NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable'
471 CREATE TABLE FKTABLE ( ftest1 int DEFAULT 0, ftest2 int, ftest3 int, ftest4 int, CONSTRAINT constrname3
472 FOREIGN KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE
473 ON DELETE SET DEFAULT ON UPDATE SET NULL);
474 NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
475 -- Insert Primary Key values
476 INSERT INTO PKTABLE VALUES (1, 2, 3, 'test1');
477 INSERT INTO PKTABLE VALUES (1, 3, 3, 'test2');
478 INSERT INTO PKTABLE VALUES (2, 3, 4, 'test3');
479 INSERT INTO PKTABLE VALUES (2, 4, 5, 'test4');
480 -- Insert Foreign Key values
481 INSERT INTO FKTABLE VALUES (1, 2, 3, 1);
482 INSERT INTO FKTABLE VALUES (2, 3, 4, 1);
483 INSERT INTO FKTABLE VALUES (NULL, 2, 3, 2);
484 INSERT INTO FKTABLE VALUES (2, NULL, 3, 3);
485 INSERT INTO FKTABLE VALUES (NULL, 2, 7, 4);
486 INSERT INTO FKTABLE VALUES (NULL, 3, 4, 5);
487 -- Insert a failed values
488 INSERT INTO FKTABLE VALUES (1, 2, 7, 6);
489 ERROR: constrname3 referential integrity violation - key referenced from fktable not found in pktable
491 SELECT * from FKTABLE;
492 ftest1 | ftest2 | ftest3 | ftest4
493 --------+--------+--------+--------
502 -- Try to update something that will set null
503 UPDATE PKTABLE set ptest2=5 where ptest2=2;
504 -- Try to update something that should not set null
505 UPDATE PKTABLE set ptest2=2 WHERE ptest2=3 and ptest1=1;
506 -- Show PKTABLE and FKTABLE
507 SELECT * from PKTABLE;
508 ptest1 | ptest2 | ptest3 | ptest4
509 --------+--------+--------+--------
516 SELECT * from FKTABLE;
517 ftest1 | ftest2 | ftest3 | ftest4
518 --------+--------+--------+--------
527 -- Try to delete something that should set default
528 DELETE FROM PKTABLE where ptest1=2 and ptest2=3 and ptest3=4;
529 -- Show PKTABLE and FKTABLE
530 SELECT * from PKTABLE;
531 ptest1 | ptest2 | ptest3 | ptest4
532 --------+--------+--------+--------
538 SELECT * from FKTABLE;
539 ftest1 | ftest2 | ftest3 | ftest4
540 --------+--------+--------+--------
549 -- Try to delete something that should not set default
550 DELETE FROM PKTABLE where ptest2=5;
551 -- Show PKTABLE and FKTABLE
552 SELECT * from PKTABLE;
553 ptest1 | ptest2 | ptest3 | ptest4
554 --------+--------+--------+--------
559 SELECT * from FKTABLE;
560 ftest1 | ftest2 | ftest3 | ftest4
561 --------+--------+--------+--------
571 NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "pktable"
572 NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "pktable"
574 -- set default update / set null delete
575 CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2, ptest3) );
576 NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable'
577 CREATE TABLE FKTABLE ( ftest1 int DEFAULT 0, ftest2 int DEFAULT -1, ftest3 int, ftest4 int, CONSTRAINT constrname3
578 FOREIGN KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE
579 ON DELETE SET NULL ON UPDATE SET DEFAULT);
580 NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
581 -- Insert Primary Key values
582 INSERT INTO PKTABLE VALUES (1, 2, 3, 'test1');
583 INSERT INTO PKTABLE VALUES (1, 3, 3, 'test2');
584 INSERT INTO PKTABLE VALUES (2, 3, 4, 'test3');
585 INSERT INTO PKTABLE VALUES (2, 4, 5, 'test4');
586 INSERT INTO PKTABLE VALUES (2, -1, 5, 'test5');
587 -- Insert Foreign Key values
588 INSERT INTO FKTABLE VALUES (1, 2, 3, 1);
589 INSERT INTO FKTABLE VALUES (2, 3, 4, 1);
590 INSERT INTO FKTABLE VALUES (2, 4, 5, 1);
591 INSERT INTO FKTABLE VALUES (NULL, 2, 3, 2);
592 INSERT INTO FKTABLE VALUES (2, NULL, 3, 3);
593 INSERT INTO FKTABLE VALUES (NULL, 2, 7, 4);
594 INSERT INTO FKTABLE VALUES (NULL, 3, 4, 5);
595 -- Insert a failed values
596 INSERT INTO FKTABLE VALUES (1, 2, 7, 6);
597 ERROR: constrname3 referential integrity violation - key referenced from fktable not found in pktable
599 SELECT * from FKTABLE;
600 ftest1 | ftest2 | ftest3 | ftest4
601 --------+--------+--------+--------
611 -- Try to update something that will fail
612 UPDATE PKTABLE set ptest2=5 where ptest2=2;
613 ERROR: constrname3 referential integrity violation - key referenced from fktable not found in pktable
614 -- Try to update something that will set default
615 UPDATE PKTABLE set ptest1=0, ptest2=5, ptest3=10 where ptest2=2;
616 UPDATE PKTABLE set ptest2=10 where ptest2=4;
617 -- Try to update something that should not set default
618 UPDATE PKTABLE set ptest2=2 WHERE ptest2=3 and ptest1=1;
619 -- Show PKTABLE and FKTABLE
620 SELECT * from PKTABLE;
621 ptest1 | ptest2 | ptest3 | ptest4
622 --------+--------+--------+--------
630 SELECT * from FKTABLE;
631 ftest1 | ftest2 | ftest3 | ftest4
632 --------+--------+--------+--------
642 -- Try to delete something that should set null
643 DELETE FROM PKTABLE where ptest1=2 and ptest2=3 and ptest3=4;
644 -- Show PKTABLE and FKTABLE
645 SELECT * from PKTABLE;
646 ptest1 | ptest2 | ptest3 | ptest4
647 --------+--------+--------+--------
654 SELECT * from FKTABLE;
655 ftest1 | ftest2 | ftest3 | ftest4
656 --------+--------+--------+--------
666 -- Try to delete something that should not set null
667 DELETE FROM PKTABLE where ptest2=5;
668 -- Show PKTABLE and FKTABLE
669 SELECT * from PKTABLE;
670 ptest1 | ptest2 | ptest3 | ptest4
671 --------+--------+--------+--------
677 SELECT * from FKTABLE;
678 ftest1 | ftest2 | ftest3 | ftest4
679 --------+--------+--------+--------
690 NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "pktable"
691 NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "pktable"
693 CREATE TABLE PKTABLE (ptest1 int PRIMARY KEY);
694 NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable'
695 CREATE TABLE FKTABLE_FAIL1 ( ftest1 int, CONSTRAINT fkfail1 FOREIGN KEY (ftest2) REFERENCES PKTABLE);
696 NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
697 ERROR: CREATE TABLE: column "ftest2" referenced in foreign key constraint does not exist
698 CREATE TABLE FKTABLE_FAIL2 ( ftest1 int, CONSTRAINT fkfail1 FOREIGN KEY (ftest1) REFERENCES PKTABLE(ptest2));
699 NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
700 ERROR: UNIQUE constraint matching given keys for referenced table "pktable" not found
701 DROP TABLE FKTABLE_FAIL1;
702 ERROR: table "fktable_fail1" does not exist
703 DROP TABLE FKTABLE_FAIL2;
704 ERROR: table "fktable_fail2" does not exist
706 -- Test for referencing column number smaller than referenced constraint
707 CREATE TABLE PKTABLE (ptest1 int, ptest2 int, UNIQUE(ptest1, ptest2));
708 NOTICE: CREATE TABLE/UNIQUE will create implicit index 'pktable_ptest1_key' for table 'pktable'
709 CREATE TABLE FKTABLE_FAIL1 (ftest1 int REFERENCES pktable(ptest1));
710 NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
711 ERROR: UNIQUE constraint matching given keys for referenced table "pktable" not found
712 DROP TABLE FKTABLE_FAIL1;
713 ERROR: table "fktable_fail1" does not exist
716 -- Tests for mismatched types
718 -- Basic one column, two table setup
719 CREATE TABLE PKTABLE (ptest1 int PRIMARY KEY);
720 NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable'
721 -- This next should fail, because text=int does not exist
722 CREATE TABLE FKTABLE (ftest1 text REFERENCES pktable);
723 NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
724 ERROR: Unable to identify an operator '=' for types 'text' and 'integer'
725 You will have to retype this query using an explicit cast
726 -- This should also fail for the same reason, but here we
727 -- give the column name
728 CREATE TABLE FKTABLE (ftest1 text REFERENCES pktable(ptest1));
729 NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
730 ERROR: Unable to identify an operator '=' for types 'text' and 'integer'
731 You will have to retype this query using an explicit cast
732 -- This should succeed, even though they are different types
733 -- because varchar=int does exist
734 CREATE TABLE FKTABLE (ftest1 varchar REFERENCES pktable);
735 NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
737 NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "pktable"
738 NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "pktable"
740 CREATE TABLE FKTABLE (ftest1 varchar REFERENCES pktable(ptest1));
741 NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
743 NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "pktable"
744 NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "pktable"
746 -- Two columns, two tables
747 CREATE TABLE PKTABLE (ptest1 int, ptest2 text, PRIMARY KEY(ptest1, ptest2));
748 NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable'
749 -- This should fail, because we just chose really odd types
750 CREATE TABLE FKTABLE (ftest1 cidr, ftest2 datetime, FOREIGN KEY(ftest1, ftest2) REFERENCES pktable);
751 NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
752 ERROR: Unable to identify an operator '=' for types 'cidr' and 'integer'
753 You will have to retype this query using an explicit cast
754 -- Again, so should this...
755 CREATE TABLE FKTABLE (ftest1 cidr, ftest2 datetime, FOREIGN KEY(ftest1, ftest2) REFERENCES pktable(ptest1, ptest2));
756 NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
757 ERROR: Unable to identify an operator '=' for types 'cidr' and 'integer'
758 You will have to retype this query using an explicit cast
759 -- This fails because we mixed up the column ordering
760 CREATE TABLE FKTABLE (ftest1 int, ftest2 text, FOREIGN KEY(ftest2, ftest1) REFERENCES pktable);
761 NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
762 ERROR: Unable to identify an operator '=' for types 'text' and 'integer'
763 You will have to retype this query using an explicit cast
765 CREATE TABLE FKTABLE (ftest1 int, ftest2 text, FOREIGN KEY(ftest2, ftest1) REFERENCES pktable(ptest1, ptest2));
766 NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
767 ERROR: Unable to identify an operator '=' for types 'text' and 'integer'
768 You will have to retype this query using an explicit cast
770 CREATE TABLE FKTABLE (ftest1 int, ftest2 text, FOREIGN KEY(ftest1, ftest2) REFERENCES pktable(ptest2, ptest1));
771 NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
772 ERROR: Unable to identify an operator '=' for types 'integer' and 'text'
773 You will have to retype this query using an explicit cast
775 CREATE TABLE FKTABLE (ftest1 int, ftest2 text, FOREIGN KEY(ftest2, ftest1) REFERENCES pktable(ptest2, ptest1));
776 NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
778 NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "pktable"
779 NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "pktable"
781 CREATE TABLE FKTABLE (ftest1 int, ftest2 text, FOREIGN KEY(ftest1, ftest2) REFERENCES pktable(ptest1, ptest2));
782 NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
784 NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "pktable"
785 NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "pktable"
787 -- Two columns, same table
788 -- Make sure this still works...
789 CREATE TABLE PKTABLE (ptest1 int, ptest2 text, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2), FOREIGN KEY(ptest3,
790 ptest4) REFERENCES pktable(ptest1, ptest2));
791 NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable'
792 NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
795 CREATE TABLE PKTABLE (ptest1 int, ptest2 text, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2), FOREIGN KEY(ptest3,
796 ptest4) REFERENCES pktable);
797 NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable'
798 NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
800 -- This shouldn't (mixed up columns)
801 CREATE TABLE PKTABLE (ptest1 int, ptest2 text, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2), FOREIGN KEY(ptest3,
802 ptest4) REFERENCES pktable(ptest2, ptest1));
803 NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable'
804 NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
805 ERROR: Unable to identify an operator '=' for types 'integer' and 'text'
806 You will have to retype this query using an explicit cast
807 -- Nor should this... (same reason, we have 4,3 referencing 1,2 which mismatches types
808 CREATE TABLE PKTABLE (ptest1 int, ptest2 text, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2), FOREIGN KEY(ptest4,
809 ptest3) REFERENCES pktable(ptest1, ptest2));
810 NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable'
811 NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
812 ERROR: Unable to identify an operator '=' for types 'text' and 'integer'
813 You will have to retype this query using an explicit cast
814 -- Not this one either... Same as the last one except we didn't defined the columns being referenced.
815 CREATE TABLE PKTABLE (ptest1 int, ptest2 text, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2), FOREIGN KEY(ptest4,
816 ptest3) REFERENCES pktable);
817 NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable'
818 NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
819 ERROR: Unable to identify an operator '=' for types 'text' and 'integer'
820 You will have to retype this query using an explicit cast
822 -- Now some cases with inheritance
823 -- Basic 2 table case: 1 column of matching types.
824 create table pktable_base (base1 int not null);
825 create table pktable (ptest1 int, primary key(base1), unique(base1, ptest1)) inherits (pktable_base);
826 NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable'
827 NOTICE: CREATE TABLE/UNIQUE will create implicit index 'pktable_base1_key' for table 'pktable'
828 create table fktable (ftest1 int references pktable(base1));
829 NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
830 -- now some ins, upd, del
831 insert into pktable(base1) values (1);
832 insert into pktable(base1) values (2);
833 -- let's insert a non-existant fktable value
834 insert into fktable(ftest1) values (3);
835 ERROR: <unnamed> referential integrity violation - key referenced from fktable not found in pktable
836 -- let's make a valid row for that
837 insert into pktable(base1) values (3);
838 insert into fktable(ftest1) values (3);
839 -- let's try removing a row that should fail from pktable
840 delete from pktable where base1>2;
841 ERROR: <unnamed> referential integrity violation - key in pktable still referenced from fktable
842 -- okay, let's try updating all of the base1 values to *4
843 -- which should fail.
844 update pktable set base1=base1*4;
845 ERROR: <unnamed> referential integrity violation - key in pktable still referenced from fktable
846 -- okay, let's try an update that should work.
847 update pktable set base1=base1*4 where base1<3;
848 -- and a delete that should work
849 delete from pktable where base1>3;
852 NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "pktable"
853 NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "pktable"
855 -- Now 2 columns 2 tables, matching types
856 create table fktable (ftest1 int, ftest2 int, foreign key(ftest1, ftest2) references pktable(base1, ptest1));
857 NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
858 -- now some ins, upd, del
859 insert into pktable(base1, ptest1) values (1, 1);
860 insert into pktable(base1, ptest1) values (2, 2);
861 -- let's insert a non-existant fktable value
862 insert into fktable(ftest1, ftest2) values (3, 1);
863 ERROR: <unnamed> referential integrity violation - key referenced from fktable not found in pktable
864 -- let's make a valid row for that
865 insert into pktable(base1,ptest1) values (3, 1);
866 insert into fktable(ftest1, ftest2) values (3, 1);
867 -- let's try removing a row that should fail from pktable
868 delete from pktable where base1>2;
869 ERROR: <unnamed> referential integrity violation - key in pktable still referenced from fktable
870 -- okay, let's try updating all of the base1 values to *4
871 -- which should fail.
872 update pktable set base1=base1*4;
873 ERROR: <unnamed> referential integrity violation - key in pktable still referenced from fktable
874 -- okay, let's try an update that should work.
875 update pktable set base1=base1*4 where base1<3;
876 -- and a delete that should work
877 delete from pktable where base1>3;
880 NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "pktable"
881 NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "pktable"
883 drop table pktable_base;
884 -- Now we'll do one all in 1 table with 2 columns of matching types
885 create table pktable_base(base1 int not null, base2 int);
886 create table pktable(ptest1 int, ptest2 int, primary key(base1, ptest1), foreign key(base2, ptest2) references
887 pktable(base1, ptest1)) inherits (pktable_base);
888 NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable'
889 NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
890 insert into pktable (base1, ptest1, base2, ptest2) values (1, 1, 1, 1);
891 insert into pktable (base1, ptest1, base2, ptest2) values (2, 1, 1, 1);
892 insert into pktable (base1, ptest1, base2, ptest2) values (2, 2, 2, 1);
893 insert into pktable (base1, ptest1, base2, ptest2) values (1, 3, 2, 2);
894 -- fails (3,2) isn't in base1, ptest1
895 insert into pktable (base1, ptest1, base2, ptest2) values (2, 3, 3, 2);
896 ERROR: <unnamed> referential integrity violation - key referenced from pktable not found in pktable
897 -- fails (2,2) is being referenced
898 delete from pktable where base1=2;
899 ERROR: <unnamed> referential integrity violation - key in pktable still referenced from pktable
900 -- fails (1,1) is being referenced (twice)
901 update pktable set base1=3 where base1=1;
902 ERROR: <unnamed> referential integrity violation - key in pktable still referenced from pktable
903 -- this sequence of two deletes will work, since after the first there will be no (2,*) references
904 delete from pktable where base2=2;
905 delete from pktable where base1=2;
907 drop table pktable_base;
908 -- 2 columns (2 tables), mismatched types
909 create table pktable_base(base1 int not null);
910 create table pktable(ptest1 text, primary key(base1, ptest1)) inherits (pktable_base);
911 NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable'
912 -- just generally bad types (with and without column references on the referenced table)
913 create table fktable(ftest1 cidr, ftest2 int[], foreign key (ftest1, ftest2) references pktable);
914 NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
915 ERROR: Unable to identify an operator '=' for types 'cidr' and 'integer'
916 You will have to retype this query using an explicit cast
917 create table fktable(ftest1 cidr, ftest2 int[], foreign key (ftest1, ftest2) references pktable(base1, ptest1));
918 NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
919 ERROR: Unable to identify an operator '=' for types 'cidr' and 'integer'
920 You will have to retype this query using an explicit cast
921 -- let's mix up which columns reference which
922 create table fktable(ftest1 int, ftest2 text, foreign key(ftest2, ftest1) references pktable);
923 NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
924 ERROR: Unable to identify an operator '=' for types 'text' and 'integer'
925 You will have to retype this query using an explicit cast
926 create table fktable(ftest1 int, ftest2 text, foreign key(ftest2, ftest1) references pktable(base1, ptest1));
927 NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
928 ERROR: Unable to identify an operator '=' for types 'text' and 'integer'
929 You will have to retype this query using an explicit cast
930 create table fktable(ftest1 int, ftest2 text, foreign key(ftest1, ftest2) references pktable(ptest1, base1));
931 NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
932 ERROR: Unable to identify an operator '=' for types 'integer' and 'text'
933 You will have to retype this query using an explicit cast
935 drop table pktable_base;
936 -- 2 columns (1 table), mismatched types
937 create table pktable_base(base1 int not null, base2 int);
938 create table pktable(ptest1 text, ptest2 text[], primary key(base1, ptest1), foreign key(base2, ptest2) references
939 pktable(base1, ptest1)) inherits (pktable_base);
940 NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable'
941 NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
942 ERROR: Unable to identify an operator '=' for types 'text[]' and 'text'
943 You will have to retype this query using an explicit cast
944 create table pktable(ptest1 text, ptest2 text, primary key(base1, ptest1), foreign key(base2, ptest2) references
945 pktable(ptest1, base1)) inherits (pktable_base);
946 NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable'
947 NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
948 ERROR: Unable to identify an operator '=' for types 'integer' and 'text'
949 You will have to retype this query using an explicit cast
950 create table pktable(ptest1 text, ptest2 text, primary key(base1, ptest1), foreign key(ptest2, base2) references
951 pktable(base1, ptest1)) inherits (pktable_base);
952 NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable'
953 NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
954 ERROR: Unable to identify an operator '=' for types 'text' and 'integer'
955 You will have to retype this query using an explicit cast
956 create table pktable(ptest1 text, ptest2 text, primary key(base1, ptest1), foreign key(ptest2, base2) references
957 pktable(base1, ptest1)) inherits (pktable_base);
958 NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable'
959 NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
960 ERROR: Unable to identify an operator '=' for types 'text' and 'integer'
961 You will have to retype this query using an explicit cast
963 ERROR: table "pktable" does not exist
964 drop table pktable_base;