| | 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;

SELECT * FROM FKTABLE;

ftest1 | ftest2 | ftest3

--------+--------+--------

1 | 3 | 5

3 | 6 | 12

| | 0

| | 4

| | 8

1 | 3 | 5

(5 rows)

DROP TABLE PKTABLE CASCADE;

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

SELECT*FROM FKTABLE;

UPDATE FKTABLESET ftest1=NULLWHERE ftest1=1;

UPDATE FKTABLESET ftest1=1WHERE ftest1=1;

ALTERTABLE PKTABLE ALTER COLUMN ptest1 TYPEbigint;

ALTERTABLE FKTABLE ALTER COLUMN ftest1 TYPEbigint;

INSERT INTO fk_partitioned_fk (a,b)VALUES (NULL,NULL);

INSERT INTO fk_notpartitioned_pkVALUES (1,2);

CREATETABLEfk_partitioned_fk_full (xint, yint) PARTITION BY RANGE (x);

CREATETABLEfk_partitioned_fk_full_1 PARTITION OF fk_partitioned_fk_full DEFAULT;

ALTERTABLE fk_partitioned_fk_full ADDFOREIGN KEY (x, y)REFERENCES fk_notpartitioned_pk MATCH FULL;

INSERT INTO fk_partitioned_fk_fullVALUES (1,NULL);-- fails

DROPTABLE fk_partitioned_fk_full;

SELECT tableoid::regclass, a, bFROM fk_partitioned_fkWHERE b ISNULLORDER BY a;

UPDATE fk_notpartitioned_pkSET a= a+1WHERE a=2502;