diff options
| -rw-r--r-- | src/test/regress/expected/foreign_key.out | 17 | ||||
| -rw-r--r-- | src/test/regress/sql/foreign_key.sql | 14 |
2 files changed, 30 insertions, 1 deletions
diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out index 421ffbeae7f..36cbdc0d8a5 100644 --- a/src/test/regress/expected/foreign_key.out +++ b/src/test/regress/expected/foreign_key.out @@ -143,6 +143,12 @@ SELECT * FROM FKTABLE; | | 8 (5 rows) +-- Check update with part of key null +UPDATE FKTABLE SET ftest1 = NULL WHERE ftest1 = 1; +ERROR: insert or update on table "fktable" violates foreign key constraint "constrname" +DETAIL: MATCH FULL does not allow mixing of null and nonnull key values. +-- Check update with old and new key values equal +UPDATE FKTABLE SET ftest1 = 1 WHERE ftest1 = 1; -- Try altering the column type where foreign keys are involved ALTER TABLE PKTABLE ALTER COLUMN ptest1 TYPE bigint; ALTER TABLE FKTABLE ALTER COLUMN ftest1 TYPE bigint; @@ -158,11 +164,11 @@ SELECT * FROM PKTABLE; SELECT * FROM FKTABLE; ftest1 | ftest2 | ftest3 --------+--------+-------- - 1 | 3 | 5 3 | 6 | 12 | | 0 | | 4 | | 8 + 1 | 3 | 5 (5 rows) DROP TABLE PKTABLE CASCADE; @@ -1597,6 +1603,15 @@ INSERT INTO fk_notpartitioned_pk VALUES (2502, 2503); INSERT INTO fk_partitioned_fk_3 (a, b) VALUES (2502, 2503); -- this always works INSERT INTO fk_partitioned_fk (a,b) VALUES (NULL, NULL); +-- MATCH FULL +INSERT INTO fk_notpartitioned_pk VALUES (1, 2); +CREATE TABLE fk_partitioned_fk_full (x int, y int) PARTITION BY RANGE (x); +CREATE TABLE fk_partitioned_fk_full_1 PARTITION OF fk_partitioned_fk_full DEFAULT; +ALTER TABLE fk_partitioned_fk_full ADD FOREIGN KEY (x, y) REFERENCES fk_notpartitioned_pk MATCH FULL; +INSERT INTO fk_partitioned_fk_full VALUES (1, NULL); -- fails +ERROR: insert or update on table "fk_partitioned_fk_full_1" violates foreign key constraint "fk_partitioned_fk_full_x_fkey" +DETAIL: MATCH FULL does not allow mixing of null and nonnull key values. +DROP TABLE fk_partitioned_fk_full; -- ON UPDATE SET NULL SELECT tableoid::regclass, a, b FROM fk_partitioned_fk WHERE b IS NULL ORDER BY a; tableoid | a | b diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql index d3ed72b1fc0..2112f24d6b9 100644 --- a/src/test/regress/sql/foreign_key.sql +++ b/src/test/regress/sql/foreign_key.sql @@ -97,6 +97,12 @@ UPDATE PKTABLE SET ptest1=1 WHERE ptest1=2; -- Check FKTABLE for update of matched row SELECT * FROM FKTABLE; +-- Check update with part of key null +UPDATE FKTABLE SET ftest1 = NULL WHERE ftest1 = 1; + +-- Check update with old and new key values equal +UPDATE FKTABLE SET ftest1 = 1 WHERE ftest1 = 1; + -- Try altering the column type where foreign keys are involved ALTER TABLE PKTABLE ALTER COLUMN ptest1 TYPE bigint; ALTER TABLE FKTABLE ALTER COLUMN ftest1 TYPE bigint; @@ -1204,6 +1210,14 @@ INSERT INTO fk_partitioned_fk_3 (a, b) VALUES (2502, 2503); -- this always works INSERT INTO fk_partitioned_fk (a,b) VALUES (NULL, NULL); +-- MATCH FULL +INSERT INTO fk_notpartitioned_pk VALUES (1, 2); +CREATE TABLE fk_partitioned_fk_full (x int, y int) PARTITION BY RANGE (x); +CREATE TABLE fk_partitioned_fk_full_1 PARTITION OF fk_partitioned_fk_full DEFAULT; +ALTER TABLE fk_partitioned_fk_full ADD FOREIGN KEY (x, y) REFERENCES fk_notpartitioned_pk MATCH FULL; +INSERT INTO fk_partitioned_fk_full VALUES (1, NULL); -- fails +DROP TABLE fk_partitioned_fk_full; + -- ON UPDATE SET NULL SELECT tableoid::regclass, a, b FROM fk_partitioned_fk WHERE b IS NULL ORDER BY a; UPDATE fk_notpartitioned_pk SET a = a + 1 WHERE a = 2502; |