2

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.

Parent Table:source_record

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.

1 Answer 1

3

No, there is no way to avoid that. Moreover, if you define a foreign key that references a partitioned table, you can no longer detach partitions.

The best thing you can do (in my opinion) is not to defile a primary key on the partitioned table, but to define a primary key on source_record_id on each partition. Sacrifice referential integrity and don't define a foreign key constraint at all. You have to live with reduced data integrity guarantees if you want to benefit from partitioning.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.