如何约束具有大量非唯一组合的组合键

Question

给定一个表结构如下:

 Order_date DATE
 Order_id   NUMBER
 State      VARCHAR2(16)
 ...
 other properties/attributes

请记住，我可以在这里使用一个整数序列并生成一个PK，但是我不感兴趣，因为我在主应用程序中如何使用这个表。

因此组合键由Order_date、Order_id和State组成。这种组合的问题是，它不一定是唯一的，但它在某种程度上是受限的。

,

Order_date  |  Order_id  |  State
21-09-2014     7218821      Pending
22-09-2014     2771272      Pending
20-09-2014     3277127      Approved
13-08-2014     2218765      Done
13-08-2014     2218765      Cancelled

约束条件包括:

• 不可能有相同的order_date和order_id和state Done将在此
中复制
• 可以有任意数量的相同的order_date和order_id与任何其他状态而不是完成
• 不能添加状态为完成或错误的记录
• 你不能跳过一个状态到另一个状态的自然顺序(注册->等待->批准->完成|取消|错误)

对我来说，为Oracle数据库实现这些约束的最佳方式是什么?

Answer 1

第一个由主键或唯一键处理。

第二个比较棘手。第二个可以用基于函数的唯一键来处理，因为Oracle允许多个NULL值:

create unique index unq_order_date_id_done on 
    orders(order, order_date, order_id, 
           (case when state = 'DONE' then state end));

我认为第三和第四个需要一个触发器来防止添加值

Answer 2

一个字接一个字:

• 在不需要监控的情况下，这很可能是真的。尽管您没有显示它，但DATE字段包含精确到秒的时间。为了获得一个副本，同一订单的状态必须在同一秒内更改两次。
• 表示怀疑。除非你的处理允许同一订单在一秒钟内发生多个状态变化。
• 您的示例数据显示状态为DONE。那是怎么来的?
• 你的描述说，在批准后，只允许的状态是完成或取消或错误。您的示例数据显示订单从DONE到CANCELED。这似乎是不允许的。实际上，你的第二个项目表明ERROR状态在任何情况下都是不允许的。

您可以有重复(订单，日期)值的唯一方法是如果状态变化发生得非常快——在同一秒内。还是……从日期字段中截断时间值。这似乎不太可能，因为没有理由丢弃记录状态更改的时间这样有价值的信息。你没有得到任何好处，处理变得更加困难:输/输。