如何在没有PL/SQL的Oracle中创建此约束

Question

这是我的玩具装置。我有三张桌子。一张桌子是客人列表（姓、名，随便什么）、食物列表（豆类、糊类、米饭、蔬菜）和订单列表（客人id、食物id）。我感兴趣的是订单清单。我想对它进行以下条件的约束。

客人必须有3份订单。客人可以有一个空订单。每个客人的订单不能相同，除非该订单为空。

因此订单表将如下所示（使用字符串而不是fk ID）：

steve, beans
steve, rice
steve, veggies
joey, rice
joey, beans
joey, <null>
sarah, rice
sarah, <null>
sarah, <null>
sam, <null>
sam, <null>
sam, <null>

create table order (
  guest_uid FK,
  food_uid FK,
  CONSTRAINT order_unique UNIQUE (guest_uid, food_uid)
)

如果唯一有效的答案是“使用PL/SQL或触发器”，我会确保将其标记为正确的答案。

Answer 1

您可以使用基于函数的索引来强制行之间的唯一性（注意，我将表称为guest_food，因为order是一个保留字

create unique index idx_order_unique
    on guest_food( case when food_uid is not null
                        then guest_uid
                        else null
                    end,
                   case when food_uid is not null
                        then food_uid
                        else null
                    end );

这将允许您对任何特定的guest_uid有尽可能多的nullfood_uid行，同时不允许重复的非null值。

在guest_food表中每个guest必须有三行的要求不是您可以通过约束来强制执行的。您可能会创建一个物化视图，在提交时进行快速刷新，存储每个guest_uid的行数计数，并添加一个约束，如果该计数不是3，则会引发错误。但这通常是一个很不寻常的事情想要强制执行，所以它会倾向于让我怀疑数据模型有点偏离。