Составной FK, ссылающийся на атомарный PK не уникальный атрибут

ProgramBox

Составной FK, ссылающийся на атомарный PK не уникальный атрибут

Post author:admin
Запись опубликована:14 января, 2022
Post category:Вопросы по программированию

#postgresql #database-design #foreign-keys

Вопрос:

Я пытаюсь создать следующие таблицы в Postgres 13.3:

 CREATE TABLE IF NOT EXISTS accounts (
    account_id Integer PRIMARY KEY NOT NULL
);

CREATE TABLE IF NOT EXISTS users (
    user_id Integer PRIMARY KEY NOT NULL,
    account_id Integer NOT NULL REFERENCES accounts(account_id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS calendars (
    calendar_id Integer PRIMARY KEY NOT NULL,
    user_id Integer NOT NULL,
    account_id Integer NOT NULL,
    FOREIGN KEY (user_id, account_id) REFERENCES users(user_id, account_id) ON DELETE CASCADE
);

Но при создании таблицы календарей я получаю следующую ошибку:

 ERROR:  there is no unique constraint matching given keys for referenced table "users"

Что для меня не имеет особого смысла, поскольку внешний ключ содержит идентификатор пользователя, который является PK таблицы пользователей и, следовательно, также имеет ограничение уникальности. Если я добавлю явное ограничение уникальности для объединенного идентификатора пользователя и идентификатора учетной записи, например:

 ALTER TABLE users ADD UNIQUE (user_id, account_id);

Затем я могу создать таблицу календарей. Это уникальное ограничение кажется мне ненужным, так как идентификатор пользователя уже уникален. Может кто-нибудь, пожалуйста, объяснить мне, чего мне здесь не хватает?

1. Есть ли какие-либо особые причины, по которым вы хотите создать составной FK для таблицы с атомарным PK?

2. account_id это не уникальный ключ users . Если вам нужно отношение n-n, подумайте о промежуточной таблице.

3. В чем смысл/намерение таблицы календарей?

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

5. @wildplasser Календарь является частью моей схемы, которая группирует события календаря (которые я здесь не добавил). Пользователь может владеть многими календарями, но календарь может принадлежать только одному пользователю. Таким образом, это отношение 1-n

Ответ №1:

Postgres настолько умен/глуп, что не предполагает, что дизайнер будет делать глупости.

Дизайнеры Postgres могли бы использовать другие стратегии:

Определите переходность и сделайте так, чтобы FK зависел не только от users.id, но также и на users.account_id -> accounts.id. Это выполнимо, но дорого. Это также включает добавление нескольких записей зависимостей в каталоги для одного ограничения FK. При наложении ограничения(ОБНОВЛЕНИЕ или УДАЛЕНИЕ в любой из двух указанных таблиц) оно может стать очень сложным.
Определите переходность и молча игнорируйте избыточную ссылку на столбец. Это подразумевает: ложь программисту. Он также должен быть представлен в каталогах.
каскадные операции DDL также станут более сложными. (помните: DDL уже очень сложен, т. е. параллелизм/управление версиями)

С точки зрения выполнения/производительности: наложение ограничений в настоящее время включает «псевдо-триггеры» на индексы указанной таблицы. (за исключением ОТЛОЖЕННЫХ, которые должны быть обработаны специально)

Итак, ИМХО, разработчики Postgres сделали разумный выбор, отказавшись делать глупые сложные вещи.