CODEWITHSUNDEEP
postgresql
Sandip
Sandip

Reputation: 481

Can we use inheritance in PostgreSQL for tables containing reference keys to another table?

I have 2 tables named item and itemdetail. The itemdetail table contains more than 200,000 records. The itemdetail table has reference keys to other tables and I'd like to partition this table by date (once per every year).

What I'd like to know is how I could partition the itemdetail table as it contains reference keys to many other tables. I read somewhere that we can not partition or use inheritance if table contains any reference keys.

Does anyone have any suggestions on how to achieve this?

Thanks

Upvotes: 0

Views: 111

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324475

In PostgreSQL (9.3)'s current inheritance-based implementation of partitioning, you pretty much get partitioning or foreign key enforcement.

Partitioned tables can reference other tables as foreign keys, but they cannot really be the subject of a foreign key reference from another table unless the related tables are also partitioned on the same key, so their references can be to the same sub-table.

Upvotes: 2

Related Questions