diff options
| author | Peter Eisentraut <peter@eisentraut.org> | 2019-01-16 16:53:38 +0100 |
|---|---|---|
| committer | Peter Eisentraut <peter@eisentraut.org> | 2019-01-16 16:56:18 +0100 |
| commit | cdaf4a472776141899dfdb742c9b73581f19f59a (patch) | |
| tree | e70472631c6cb77181d10e295065327233246c57 | |
| parent | 74bd06648b720bb48f50bd32848f2f3cf2deb1f6 (diff) | |
| download | postgresql-cdaf4a472776141899dfdb742c9b73581f19f59a.tar.gz postgresql-cdaf4a472776141899dfdb742c9b73581f19f59a.zip | |
Increase test coverage in RI_FKey_pk_upd_check_required()
This checks the case where the primary key has at least one null
column.
Reviewed-by: Alvaro Herrera <alvherre@2ndquadrant.com>
Reviewed-by: Mi Tar <mmitar@gmail.com>
Discussion: https://www.postgresql.org/message-id/flat/7ae17c95-0c99-d420-032a-c271f510112b@2ndquadrant.com/
| -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 |