partitioning - How to use references to partitioned table in PostgreSQL? - Stack Overflow

admin管理员组

文章数量:1401598

I research declarative partitioning and prepared such query for partitioned table:

create table _my_message
(
id uuid not null,
chat_id uuid constraint _my_message_chat_fkey references _chat,
...

CONSTRAINT _my_message_id_chat_id_pk PRIMARY KEY (id, chat_id)
) PARTITION BY HASH (chat_id);

And I have another table where I have to have reference to id column of _my_message

CREATE TABLE _document
(
    id uuid NOT NULL CONSTRAINT _documents_pkey PRIMARY KEY,
    chat_id uuid NOT NULL,
    message_id uuid CONSTRAINT _documents_message_pkey REFERENCES _my_message (id)
);

But now I have an error because of lack of unique constraint for id in _my_message

SQL State  : 42830
Error Code : 0
Message    : ERROR: there is no unique constraint matching given keys for referenced table "_partitioned_message"

And I'm really confused because it's not allowed to have more unique constraints for partitioned table.

If I try to use construction with id uuid not null primary key I'll get exception [42P16] ERROR: multiple primary keys for table "_my_message" are not allowed.

If I try to use id uuid not null unique I'll get exception [0A000] ERROR: unique constraint on partitioned table must include all partitioning columns

I research declarative partitioning and prepared such query for partitioned table:

create table _my_message
(
id uuid not null,
chat_id uuid constraint _my_message_chat_fkey references _chat,
...

CONSTRAINT _my_message_id_chat_id_pk PRIMARY KEY (id, chat_id)
) PARTITION BY HASH (chat_id);

And I have another table where I have to have reference to id column of _my_message

CREATE TABLE _document
(
    id uuid NOT NULL CONSTRAINT _documents_pkey PRIMARY KEY,
    chat_id uuid NOT NULL,
    message_id uuid CONSTRAINT _documents_message_pkey REFERENCES _my_message (id)
);

But now I have an error because of lack of unique constraint for id in _my_message

SQL State  : 42830
Error Code : 0
Message    : ERROR: there is no unique constraint matching given keys for referenced table "_partitioned_message"

And I'm really confused because it's not allowed to have more unique constraints for partitioned table.

If I try to use construction with id uuid not null primary key I'll get exception [42P16] ERROR: multiple primary keys for table "_my_message" are not allowed.

If I try to use id uuid not null unique I'll get exception [0A000] ERROR: unique constraint on partitioned table must include all partitioning columns

• postgresql
• partitioning
• database-partitioning

Share Improve this question Follow edited Mar 23 at 23:48 Bergi 666k161161 gold badges1k1k silver badges1.5k1.5k bronze badges asked Mar 23 at 23:09 NeverSleepsNeverSleeps 1,95022 gold badges2727 silver badges4949 bronze badges 1

• I would get rid of the foreign key and the primary key on the partitioned table. Use primary keys on the individual partitions instead. If you can partition the referencing table appropriately, that would allow you to define foreign key constraints between the individual partitions. – Laurenz Albe Commented Mar 24 at 6:51

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 3

You use two columns for identifying your _my_message rows (in its primary key). You need to use a multi-column foreign key constraint as well then to reference them, which seems easily possible since your _document rows appear to belong to the same chat_id:

CREATE TABLE _document (
    id uuid NOT NULL PRIMARY KEY,
    chat_id uuid NOT NULL,
    message_id uuid,
    CONSTRAINT _documents_message FOREIGN KEY (chat_id, message_id) REFERENCES _my_message(chat_id, id)
);

本文标签： partitioningHow to use references to partitioned table in PostgreSQLStack Overflow