self-fk-after-part-attach.sql

DROP TABLE IF EXISTS parent, child1;

CREATE TABLE parent (
    id bigint NOT NULL default 1,
    no_part smallint NOT NULL,
    id_abc bigint,
    id_def bigint,
    id_ghi bigint,
    id_jkl bigint,
    ts_creation timestamp without time zone default now()  NOT NULL,
    FOREIGN KEY (id_abc, no_part) REFERENCES parent(id, no_part) ON UPDATE RESTRICT ON DELETE RESTRICT,
    FOREIGN KEY (id_def, no_part) REFERENCES parent(id, no_part) ON UPDATE RESTRICT ON DELETE RESTRICT,
    FOREIGN KEY (id_ghi, no_part) REFERENCES parent(id, no_part) ON UPDATE RESTRICT ON DELETE RESTRICT,
    FOREIGN KEY (id_jkl, no_part) REFERENCES parent(id, no_part) ON UPDATE RESTRICT ON DELETE RESTRICT,
    PRIMARY KEY (id, no_part)
)
PARTITION BY LIST (no_part);

-- p1
CREATE TABLE child1 (
    id bigint NOT NULL default 1,
    no_part smallint NOT NULL,
    id_abc bigint,
    id_def bigint,
    id_ghi bigint,
    id_jkl bigint,
    ts_creation timestamp without time zone default now()  NOT NULL,
    FOREIGN KEY (id_abc, no_part) REFERENCES child1(id, no_part) ON UPDATE RESTRICT ON DELETE RESTRICT,
    FOREIGN KEY (id_def, no_part) REFERENCES child1(id, no_part) ON UPDATE RESTRICT ON DELETE RESTRICT,
    FOREIGN KEY (id_ghi, no_part) REFERENCES child1(id, no_part) ON UPDATE RESTRICT ON DELETE RESTRICT,
    FOREIGN KEY (id_jkl, no_part) REFERENCES child1(id, no_part) ON UPDATE RESTRICT ON DELETE RESTRICT,
    CONSTRAINT child1 CHECK ((no_part = 1)),
    PRIMARY KEY (id, no_part)
);

ALTER TABLE ONLY parent ATTACH PARTITION child1 FOR VALUES IN ('1');

SELECT conname, conparentid, conrelid, confrelid
FROM pg_constraint
WHERE conname ~ '_no_part_fkey'
ORDER BY 1;

\d+ child1