Ошибка: "нет уникального ограничения, соответствующего заданным ключам для ссылочной таблицы" с разбиенной таблицей в PostgreSQL

Содержание

Вопрос или проблема
SQL для создания таблицы:
Что я пробовал:
Ответ или решение
Причина ошибки
Решение проблемы
Заключение

Вопрос или проблема

Я столкнулся с проблемой с разделенной таблицей в PostgreSQL, которая вызывает ошибку ограничения внешнего ключа. Вот суть:

У меня есть таблица logbook_entries, которая разделена по столбцу user_id. Структура таблицы включает составной первичный ключ (id, user_id), и я пытаюсь создать ссылку внешнего ключа на эту таблицу из другой таблицы. Однако, когда я пытаюсь создать внешний ключ, я получаю следующую ошибку:

ОШИБКА: отсутствует уникальное ограничение, соответствующее данным ключам для ссылочной таблицы "logbook_entries"
Состояние SQL: 42830

SQL для создания таблицы:

CREATE TABLE some_composite_table (
    id UUID NOT NULL DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    ...
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, 
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    PRIMARY KEY (id, user_id) -- Составной первичный ключ
) PARTITION BY LIST (user_id); -- Разделение по user_id

Что я пробовал:

Я убедился, что первичный ключ в logbook_entries является составным ключом, включающим как id, так и user_id, как требуется для разделения.

Попробовал использовать UNIQUE для ID как id UUID NOT NULL UNIQUE DEFAULT gen_random_uuid() – возникает ошибка:

ОШИБКА:  Уникальное ограничение на таблице "logbook_entries" не имеет столбца "user_id", который является частью ключа разделения. Уникальное ограничение на разделенной таблице должно включать все столбцы разделения
ОШИБКА:  уникальное ограничение на разделенной таблице должно включать все столбцы разделения
Состояние SQL: 0A000
Подробности: Уникальное ограничение на таблице "logbook_entries" не имеет столбца "user_id", который является частью ключа разделения.

И я не могу использовать уникальность для user_id, так как в одной таблице могут быть дубликаты, поэтому составной индекс.

Попробовал использовать PARTITION BY HASH (user_id) – та же ошибка.
Попробовал добавить UNIQUE (id, user_id) – та же ошибка.
Неиспользование составного первичного ключа приводит к другой ошибке.

Почему возникает эта ошибка, и как я могу её исправить?

Ответ или решение

Проблема, с которой вы столкнулись, связана с ограничениями целостности при работе с партиционированными таблицами в PostgreSQL. Ошибка "no unique constraint matching given keys for referenced table" означает, что PostgreSQL не может найти уникальное ограничение для столбцов, на которые вы ссылаетесь при создании внешнего ключа. Давайте подробно разберем причины этой ошибки и возможные решения.

Причина ошибки

В PostgreSQL, когда у вас есть партиционированная таблица, все ограничения целостности данных, такие как первичные ключи и уникальные ограничения, должны охватывать все столбцы, по которым производится партиционирование. В вашем случае таблица logbook_entries партиционирована по столбцу user_id, поэтому для корректной работы нужно, чтобы значение, на которое ссылается внешний ключ, также имело уникальное ограничение на основе этого столбца.

Ваши текущие попытки создания уникального или первичного ключа не увенчались успехом, так как они не учитывают эту особенность. Хотя вы создали составной первичный ключ (id, user_id), PostgreSQL требует, чтобы каждой партиции таблицы было присвоено ограничение уникальности, которое включает все столбцы партиционирования. То есть, оно должно выглядеть так: UNIQUE (user_id, id) в каждом разделе.

Решение проблемы

Для решения данной проблемы необходимо выполнить следующие шаги:

Изменение структуры таблицы logbook_entries:
Убедитесь, что в таблице logbook_entries, для каждой партиции, вы добавили уникальное ограничение, которое включает user_id:

CREATE TABLE logbook_entries (
   id UUID NOT NULL,
   user_id UUID NOT NULL,
   ...
   PRIMARY KEY (id, user_id) -- Составной первичный ключ
) PARTITION BY LIST (user_id);

CREATE TABLE logbook_entries_user1 PARTITION OF logbook_entries
   FOR VALUES IN ('user1_id')
   UNIQUE (user_id, id); -- Уникальное ограничение для партиции

CREATE TABLE logbook_entries_user2 PARTITION OF logbook_entries
   FOR VALUES IN ('user2_id')
   UNIQUE (user_id, id); -- Уникальное ограничение для партиции

Создание внешнего ключа к logbook_entries:
Теперь, когда у вас есть уникальное ограничение, вы сможете создать внешний ключ из таблицы some_composite_table:

CREATE TABLE some_composite_table (
   id UUID NOT NULL DEFAULT gen_random_uuid(),
   user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
   ...
   created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, 
   updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
   PRIMARY KEY (id, user_id)
);

Заключение

Проблема с ограничением уникальности при создании внешних ключей в партиционированных таблицах PostgreSQL вызвана тем, что все партиционируемые столбцы должны быть учтены в уникальных ограничениях. Убедитесь, что у каждой партиции вашего основного логирования есть соответствующие уникальные ограничения, и это исправит вашу ошибку.

В случае дальнейших вопросов или необходимости в подробном разборе других аспектов работы с PostgreSQL, всегда можно обратиться за помощью к профессионалам в данной области.

Ошибка: “нет уникального ограничения, соответствующего заданным ключам для ссылочной таблицы” с разбиенной таблицей в PostgreSQL