diff options
Diffstat (limited to 'src')
| -rw-r--r-- | src/test/regress/expected/foreign_key.out | 17 | ||||
| -rw-r--r-- | src/test/regress/sql/foreign_key.sql | 17 |
2 files changed, 34 insertions, 0 deletions
diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out index 36cbdc0d8a5..e42e007d4f4 100644 --- a/src/test/regress/expected/foreign_key.out +++ b/src/test/regress/expected/foreign_key.out @@ -341,6 +341,18 @@ SELECT * FROM PKTABLE; DROP TABLE FKTABLE; DROP TABLE PKTABLE; +-- +-- Check initial check upon ALTER TABLE +-- +CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, PRIMARY KEY(ptest1, ptest2) ); +CREATE TABLE FKTABLE ( ftest1 int, ftest2 int ); +INSERT INTO PKTABLE VALUES (1, 2); +INSERT INTO FKTABLE VALUES (1, NULL); +ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) REFERENCES PKTABLE MATCH FULL; +ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_fkey" +DETAIL: MATCH FULL does not allow mixing of null and nonnull key values. +DROP TABLE FKTABLE; +DROP TABLE PKTABLE; -- MATCH SIMPLE -- Base test restricting update/delete CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2, ptest3) ); @@ -1607,6 +1619,11 @@ INSERT INTO fk_partitioned_fk (a,b) VALUES (NULL, NULL); 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; +INSERT INTO fk_partitioned_fk_full VALUES (1, NULL); +ALTER TABLE fk_partitioned_fk_full ADD FOREIGN KEY (x, y) REFERENCES fk_notpartitioned_pk MATCH FULL; -- fails +ERROR: insert or update on table "fk_partitioned_fk_full" violates foreign key constraint "fk_partitioned_fk_full_x_fkey" +DETAIL: MATCH FULL does not allow mixing of null and nonnull key values. +TRUNCATE fk_partitioned_fk_full; 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" diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql index 2112f24d6b9..42274f472eb 100644 --- a/src/test/regress/sql/foreign_key.sql +++ b/src/test/regress/sql/foreign_key.sql @@ -219,6 +219,20 @@ SELECT * FROM PKTABLE; DROP TABLE FKTABLE; DROP TABLE PKTABLE; +-- +-- Check initial check upon ALTER TABLE +-- +CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, PRIMARY KEY(ptest1, ptest2) ); +CREATE TABLE FKTABLE ( ftest1 int, ftest2 int ); + +INSERT INTO PKTABLE VALUES (1, 2); +INSERT INTO FKTABLE VALUES (1, NULL); + +ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) REFERENCES PKTABLE MATCH FULL; + +DROP TABLE FKTABLE; +DROP TABLE PKTABLE; + -- MATCH SIMPLE @@ -1214,6 +1228,9 @@ INSERT INTO fk_partitioned_fk (a,b) VALUES (NULL, NULL); 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; +INSERT INTO fk_partitioned_fk_full VALUES (1, NULL); +ALTER TABLE fk_partitioned_fk_full ADD FOREIGN KEY (x, y) REFERENCES fk_notpartitioned_pk MATCH FULL; -- fails +TRUNCATE fk_partitioned_fk_full; 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; |