PostgreSQL Partition and Unique Conflict

April 20, 2022

I am attempting to set up a database with partitions, however I am running into an error when creating the activity table. The 3 Table CREATEs (without the actual data) are as follows:

CREATE TABLE collection (
    id SERIAL PRIMARY KEY
);


CREATE TABLE asset (
    id SERIAL,
    collection_id INT,
    CONSTRAINT fk_collection
        FOREIGN KEY(collection_id)
            REFERENCES collection(id),
    CONSTRAINT asset_pkey PRIMARY KEY(id, collection_id)
)PARTITION BY LIST(collection_id);


CREATE TABLE activity(
    id SERIAL,
    collection_id INT,
    CONSTRAINT fk_collection
        FOREIGN KEY(collection_id)
            REFERENCES collection(id),
    CONSTRAINT activity_pkey PRIMARY KEY(id, collection_id),
    asset_id INT,
    CONSTRAINT fk_asset
        FOREIGN KEY(asset_id)
            REFERENCES asset(id)
) PARTITION BY LIST(collection_id);

ERROR:  there is no unique constraint matching given keys for referenced table "asset"
SQL state: 42830

My understanding of the error is that foreign keys must be unique. Because I must add collection_id as a primary key to partition item, I cannot also designate id as unique. When I attempt to make id unique, this is what happens:

ALTER TABLE asset
    ADD CONSTRAINT u_id UNIQUE (id);

ERROR:  unique constraint on partitioned table must include all partitioning columns
DETAIL:  UNIQUE constraint on table "asset" lacks column "collection_id" which is part of the partition key.
SQL state: 0A000

Even if I successfully add the UNIQUE constraint on id and collection_id, it continues to throw me SQL state: 42830. Do I have to restructure my data or is there a better way to go about this that I’m not thinking of?

>Solution :

Would it be enough to merge the foreign keys on asset into one composite key, or does that violate some other constraint of your data?

CREATE TABLE activity(
    id SERIAL,
    collection_id INT,
    asset_id INT,
    CONSTRAINT activity_pkey PRIMARY KEY(id, collection_id),
    CONSTRAINT fk_asset_collection
        FOREIGN KEY(asset_id, collection_id)
            REFERENCES asset(id, collection_id)
) PARTITION BY LIST(collection_id);