diff options
| -rw-r--r-- | src/test/regress/expected/foreign_key.out | 24 | ||||
| -rw-r--r-- | src/test/regress/sql/foreign_key.sql | 19 |
2 files changed, 43 insertions, 0 deletions
diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out index 5525dd75b9f..421ffbeae7f 100644 --- a/src/test/regress/expected/foreign_key.out +++ b/src/test/regress/expected/foreign_key.out @@ -399,6 +399,30 @@ SELECT * from FKTABLE; DROP TABLE FKTABLE; DROP TABLE PKTABLE; +-- restrict with null values +CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, UNIQUE(ptest1, ptest2, ptest3) ); +CREATE TABLE FKTABLE ( ftest1 int, ftest2 int, ftest3 int, ftest4 int, CONSTRAINT constrname3 + FOREIGN KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE (ptest1, ptest2, ptest3)); +INSERT INTO PKTABLE VALUES (1, 2, 3, 'test1'); +INSERT INTO PKTABLE VALUES (1, 3, NULL, 'test2'); +INSERT INTO PKTABLE VALUES (2, NULL, 4, 'test3'); +INSERT INTO FKTABLE VALUES (1, 2, 3, 1); +DELETE FROM PKTABLE WHERE ptest1 = 2; +SELECT * FROM PKTABLE; + ptest1 | ptest2 | ptest3 | ptest4 +--------+--------+--------+-------- + 1 | 2 | 3 | test1 + 1 | 3 | | test2 +(2 rows) + +SELECT * FROM FKTABLE; + ftest1 | ftest2 | ftest3 | ftest4 +--------+--------+--------+-------- + 1 | 2 | 3 | 1 +(1 row) + +DROP TABLE FKTABLE; +DROP TABLE PKTABLE; -- cascade update/delete CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2, ptest3) ); CREATE TABLE FKTABLE ( ftest1 int, ftest2 int, ftest3 int, ftest4 int, CONSTRAINT constrname3 diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql index 615588c3181..d3ed72b1fc0 100644 --- a/src/test/regress/sql/foreign_key.sql +++ b/src/test/regress/sql/foreign_key.sql @@ -260,6 +260,25 @@ SELECT * from FKTABLE; DROP TABLE FKTABLE; DROP TABLE PKTABLE; +-- restrict with null values +CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, UNIQUE(ptest1, ptest2, ptest3) ); +CREATE TABLE FKTABLE ( ftest1 int, ftest2 int, ftest3 int, ftest4 int, CONSTRAINT constrname3 + FOREIGN KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE (ptest1, ptest2, ptest3)); + +INSERT INTO PKTABLE VALUES (1, 2, 3, 'test1'); +INSERT INTO PKTABLE VALUES (1, 3, NULL, 'test2'); +INSERT INTO PKTABLE VALUES (2, NULL, 4, 'test3'); + +INSERT INTO FKTABLE VALUES (1, 2, 3, 1); + +DELETE FROM PKTABLE WHERE ptest1 = 2; + +SELECT * FROM PKTABLE; +SELECT * FROM FKTABLE; + +DROP TABLE FKTABLE; +DROP TABLE PKTABLE; + -- cascade update/delete CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2, ptest3) ); CREATE TABLE FKTABLE ( ftest1 int, ftest2 int, ftest3 int, ftest4 int, CONSTRAINT constrname3 |