PostgreSQL: как реализовать минимальную мощность?

Question

Как ответили на этот вопрос: количество элементов в PostgreSQL, количество элементов увеличивается с помощью ограничений.

Правила кардинальности определяют допустимые значения отношений - один-ко-многим, многие-ко-многим и т. Д. Многие-ко-многим достигаются с помощью таблиц соединения и один-ко-многим с помощью FOREIGN KEY.

Но как можно реализовать отношение "один к одному" или "один к одному". Что то же самое, что спросить: как я могу обеспечить минимальную мощность в PostgreSQL?

Практическая ситуация может быть в том случае, когда необходимо сохранить, скажем, адрес (или номер телефона), который ДОЛЖЕН быть предоставлен (но может быть больше одного) человеком (скажем, пользователем или клиентом).

Редактировать:

Вышеуказанная ситуация является частным случаем (с кардинальной единицей) общей проблемы. Общая проблема заключается в следующем: Как обеспечить количество элементов произвольного числа?

Как ответил кувшин, в качестве обходного пути можно использовать ненулевую ссылку FOREIGN KEY, если минимальная мощность равна единице. Это также обеспечит дополнительную функцию для выбора по умолчанию среди многих.

Но рассмотрим еще одну ситуацию взаимоотношений между командой по крикету и ее игроками. Каждая команда ДОЛЖНА иметь МИНИМУМ 11 игроков, чтобы квалифицироваться как команда. Здесь минимальная мощность равна одиннадцати (11).

Аналогично, отношение между курсом и учеником в школе, где каждый ученик ДОЛЖЕН записаться на AT-LEAST 5 курсов, и каждый курс ДОЛЖЕН иметь МИНИМУМ 10 учеников.

Answer 1

Нет возможности обеспечить соблюдение таких правил, используя только FOREIGN KEY ограничения.

1) Один из способов - разрешить циклические ссылки между таблицами (столбец "по умолчанию", рекомендуется jug). Это приводит к проблемам курицы и яйца, которыми трудно управлять, вам придется использовать отложенные ограничения. Кроме того, эта опция просто недоступна в некоторых СУБД. Еще одним недостатком является то, что для футбольной команды вам придется добавить 11 столбцов "по умолчанию" (и вам придется решать проблему курицы и 11 яиц)!

2) Другой вариант - использовать триггеры.

3) Другой вариант - использовать ограничение уровня базы данных между двумя таблицами. Не уверен, есть ли какая-либо СУБД, которая имеет такую ​​функциональность. Помимо типичного UNIQUE, PRIMARY а также FOREIGN KEY ограничения, большинство СУБД имеют только ограничения на уровне строк и с ограничениями (без подзапросов и т. д.).

4) Другой вариант заключается в применении таких правил путем создания соответствующих процедур INSERT, UPDATE и DELETE, которые могут обращаться только к двум связанным таблицам и обеспечивать целостность в соответствии с этими правилами. Это лучший подход (на мой взгляд).

5) Еще один вариант, который проще реализовать, - это использовать стандартные ограничения внешнего ключа, обеспечивающие отношение 1-ко-многим, и иметь представление, которое показывает те команды, в которых на самом деле 11 или более игроков. Это, конечно, означает, что вы на самом деле не соблюдаете правила, о которых просите. Но возможно (и я могу сказать вероятное), что вы можете себе позволить не слишком. Например, если футболисты погибают в результате несчастного случая, команда больше не может играть в турнирах, но это все-таки команда. Таким образом, вы можете определить две сущности: Team (базовая таблица) и ProperTeam (View), которые могут играть в игры. Пример:

CREATE VIEW AS ProperTeam
( SELECT *
  FROM Team
  WHERE ( SELECT COUNT(*)
          FROM Player
          WHERE Player.TeamId = Team.TeamId
        ) >= 11
) 

---

Варианты 1 и 2 выглядят довольно "грязно", но это только личное мнение, многим людям нравятся триггеры.

Я бы выбрал вариант 4, если только я не могу ("обмануть" и) фактически не применять ограничение с помощью варианта 5.

Answer 2

Нет способа указать это с помощью ограничения CHECK, поэтому я думаю, что лучший подход - это триггер:

http://www.postgresql.org/docs/9.1/static/sql-createtrigger.html
http://www.postgresql.org/docs/9.1/static/plpgsql-trigger.html

В итоге вы получите что-то вроде (я не проверял это или что-то еще):

CREATE TRIGGER at_least_one before INSERT, UPDATE, DELETE ON the_one_table  FOR EACH ROW EXECUTE PROCEDURE check_at_least_one();

CREATE OR REPLACE FUNCTION check_at_least_one() RETURNS trigger AS $$
    BEGIN
    nmany := select count(*) from the_many_table where the_many_table.the_one_id=NEW.id;   
    IF nmany > 0 THEN 
        RETURN NEW;
    END IF;
    RETURN NULL;
END;

Answer 3

Если у вас есть адреса в одной таблице адресов, вы можете определить столбец "default_address" (в таблице клиентов), который является ненулевой ссылкой внешнего ключа на адрес, который должен быть предоставлен.

Если у вас есть таблицы пересылок, которые обеспечивают необязательное отношение "многие ко многим" путем ссылки на человека, адрес и, возможно, порядок (-item), то вы можете использовать coalesce для перезаписи значений NULL для адресов, которые вы получаете во внешнем соединении между (внутренние заказы клиентов) и shipping_addresses (представление, объединяющее доставки с адресами). Но чтобы избежать проблем с, возможно, различным числом ненулевых компонентов адресов, Стефан Фарулт рекомендует в своей (настоятельно рекомендуется!) Книге "Искусство SQL" использовать технику "скрытого ключа сортировки" (предполагая, что customer_with_default_address - это представление, объединяющее клиентов с адреса с использованием "default_address":

select *
  from (select 1 as sortkey,
               line_1,
               line_2,
               city,
               state,
               postal_code,
               country
        from shipping_addresses
          where customer_id = ?
        union
        select 2 as sortkey,
               line_1,
               line_2,
               city,
               state,
               postal_code,
               country
        from customers_with_default_address
          where customer_id = ?
        order by 1) actual_shipping_address
      limit 1

Answer 4

Подход, который работал для меня и требовал разумного количества кодирования, был (переведен на вопрос вашей команды / игрока):

• создать отложенное ограничение внешнего ключа, чтобы обеспечить соблюдение отношения "у каждого игрока есть одна команда".
• создайте только один триггер на столе команды, который проверяет, что по крайней мере n игроков присоединены к команде. Триггер должен выдать исключение, если кардинальность не соблюдается, как указывает AdamKG.

Это будет обеспечивать ограничение, но в качестве побочного эффекта это также позволит только один способ кодирования новой команды игроков (то есть, не отвергая это как ключевое нарушение)

start transaction;
set constraints all deferred;
insert player_1 with teamPK --teamPK is not yet in table team, will be below
...
insert player_n with teamPK
insert teamPK --this fires the trigger which is successful.
commit transaction; --this fires the foreign key deferred check, successful.

Обратите внимание, что я делаю это, используя самостоятельно определяемые первичные ключи (для teamPK), например, уникальное имя команды, чтобы я мог знать teamPK до фактического вставления строки в таблицу team (в отличие от использования автоматически увеличиваемого идентификатора).