I have created partition by range based on row_created_tmst for PostgreSQL table. Since partition keys needs to be part of the primary key in PostgreSQL, row_created_tmst is included in the primary key of the table.
With this change, I am facing an issue with foreign key in the child tables.
source_record_id bigint NOT NULL, row_created_tmst timestamp with time zone NOT NULL, list of other attributes.. CONSTRAINT source_record_pk PRIMARY KEY (source_record_id, row_created_tmst) Child Table: role_player (one source_record have 1 or more role player) role_player_id bigint NOT NULL, row_created_tmst timestamp with timezone NOT NULL, source_record_id bigint NOT NULL, -- Foreign Key sr_row_created_tmst bigint NOT NULL, -- Foreign Key list of other attributes.. CONSTRAINT role_player_pk PRIMARY KEY (role_player_id, row_created_tmst) CONSTRAINT role_player_ak FOREIGN KEY (source_record_id, sr_row_created_tmst)
Because of partitioning based on row_created_tmst column, all child tables of source_record is having 2 row_created_tmst columns (sr_row_created_tmst (FK), row_created_tmst)
Is there a way to aviod having both parent and child table row_created_tmst column in child tables.