Блог

Главная - Вопросы по программированию - PostgreSQL: ограничение, которое влияет на несколько таблиц

PostgreSQL: ограничение, которое влияет на несколько таблиц

#sql #postgresql #constraints

#sql #postgresql #ограничения

Вопрос:

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

 CREATE TABLE users (
  id SERIAL NOT NULL PRIMARY KEY,
  -- some fields
);

CREATE TABLE entities (
  id SERIAL NOT NULL PRIMARY KEY,
  parent_id INTEGER NOT NULL,
  owner_id INTEGER NOT NULL,
  FOREIGN KEY (parent_id) REFERENCES entities(id),
  FOREIGN KEY (owner_id) REFERENCES users(id)
);

CREATE TABLE entity_names (
  id SERIAL NOT NULL PRIMARY KEY,
  entity_id INTEGER NOT NULL,
  name VARCHAR(30) NOT NULL,
  FOREIGN KEY (entity_id) REFERENCES entities(id)
);

И мне нужно убедиться, что нет объектов с повторяющимся именем (конечно, с тем же parent_id и owner_id ).

Я могу убедиться, что прямо перед добавлением имени entity_names в запрос, подобный этому:

 SELECT COUNT(t.id)
  FROM entity_names n
  JOIN entities e ON n.entity_id = e.id
  WHERE e.parent_id = 123 and e.owner_id = 456 and n.name = 'foo'

Но мне интересно, возможно ли (и разумно) реализовать это ограничение в базе данных?

Ответ №1:

В Postgres нет ограничений для нескольких таблиц, но вы можете эмулировать check ограничение, поместив свой запрос в триггер on tag_names .

Ответ №2:

Каждая entity_id строка в entity_names таблице идентифицирует строку в entities таблице. Попытка добавить повторяющееся имя для определенного объекта будет выглядеть следующим образом:

 INSERT INTO entity_names (entity_id, name) VALUES (2, 'Name1');

Каждый объект имеет своего собственного родителя и владельца, но по-прежнему однозначно идентифицируется с помощью an id . Таким образом, я думаю, что вы переоцениваете свою проблему и нуждаетесь не в ограничении между таблицами, а в простом UNIQUE :

 CREATE UNIQUE INDEX ON entity_names (entity_id, name);

Это предполагает, что нет двух записей с одинаковыми parent_id и owner_id вашими сущностями — я сомневаюсь, что это имело бы какой-либо смысл, поскольку для меня они кажутся ключом-кандидатом.

Если это на самом деле не так (для меня это маловероятно), то создайте функцию, которая возвращает триггер, и выполните там проверку, затем создайте BEFORE INSERT триггер в entity_names таблице.