PostgreSQL：如何实现最小基数？

Question

正如这个问题所回答的：PostgreSQL 中的基数，使用约束。

基数规则定义了允许的关系数量——一对多、多对多等。多对多是使用连接表实现的，一对多是使用外键实现的。

但是如何实现一对一或多（一对一+）关系。这与问：如何在 PostgreSQL 中强制执行最小基数？

一种实际情况是，需要存储个人（例如用户或客户）必须提供的地址（或电话号码）（但可以不止一个）。

编辑：

上述情况是一般问题的特殊情况（基数为一）。一般问题是：如何强制任意数的基数？

正如由jug回答非空< /em> 如果最小基数为 1，则可以使用外键引用作为解决方法。它还将提供一个附加功能来在众多功能中选择默认值。

但考虑一下板球队与其球队之间关系的另一种情况强>玩家。每支球队必须至少有 11 名球员才能获得团队资格。这里的最小基数是十一 (11)。

类似地，学校中课程和学生之间的关系，每个学生必须注册至少 5 门课程，并且每门课程必须至少有 10 名学生。

Answer 1

无法使用 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 2

仅使用 FOREIGN KEY 约束无法强制执行此类规则。

1）一种方法是允许表之间的循环引用（“默认”列，由 jug 建议）。这会导致难以管理的先有鸡还是先有蛋的问题，您必须使用可延迟约束。另外，这个选项在某些 DBMS 中根本不可用。另一个缺点是，对于一支足球队，您必须添加 11 个“默认”列（并且您必须处理鸡和 11 个蛋的问题）！

2）另一种选择是使用触发器。

3) 另一种选择是在两个表之间使用数据库级约束。不确定是否有任何 DBMS 具有此类功能。除了典型的 UNIQUE、PRIMARY 和 FOREIGN KEY 约束之外，大多数 DBMS 仅具有行级约束和限制（无子查询等）。

4) 另一种选择是通过创建适当的 INSERT、UPDATE 和 DELETE 过程来强制执行此类规则，这些过程只能访问两个相关表并根据这些规则强制执行完整性。这是更好的方法（在我看来）。

5) 另一种更容易实现的选择是使用标准外键约束，强制执行一对多关系，并拥有一个显示实际拥有 11 名或更多玩家的团队的视图。这当然意味着您实际上并没有执行您所要求的规则。但有可能（我可以说很可能）你也负担不起。例如，如果足球运动员在事故中丧生，球队就不能再参加锦标赛，但它仍然是一支球队。因此，您可以定义两个可以玩游戏的实体：Team（基本表）和 ProperTeam（视图）。示例：

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

---

选项 1 和 2 看起来相当“混乱”，但这只是个人意见，很多人喜欢触发器。

我会选择选项 4，除非我可以（“作弊”并且）实际上不强制执行选项 5 的约束。

Answer 3

如果您的地址位于一个表 adresses 中，则可以定义一个非空列“default_address”（在表 customers 中）。 > 必须提供的地址的外键引用。

如果您有一个表 shippings 通过引用人员、地址以及订单（-item）来提供可选的多对多关系，那么您可以使用合并来覆盖地址的 NULL您在（客户内部联接订单）和shipping_addresses之间建立了外部联接（将shippings与地址）。但是，为了防止地址中可能存在不同数量的非空组件出现问题，Stephane Faroult 在他的（强烈推荐！）一书中推荐使用“隐藏的 SQL 艺术”排序键”技术（假设 customers_with_default_address 是使用“default_address”将 customers 与 addresses 连接起来的视图：

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），例如唯一的团队名称，以便我可以在实际将行插入表 team 之前知道 teamPK（而不是使用自动递增 id）。