Oracle10g ->创建复杂约束

Question

我应该修复DB层中的问题，而不是代码中的问题，但这有点复杂。我搜索了一下，但找不到解决办法：

DB版本: Oracle数据库10g企业版发布版10.2.0.3.0 - Prod

好的，假设我有一个多行的表，但是现在只有这些对我们来说是有趣的。

CREATE TABLE "TRANSPORT" 
(   "O_PREFIX" VARCHAR2(3 BYTE), 
    "O_NUMBER" NUMBER(4,0), 
    "O_SUFFIX" CHAR(1 BYTE), 
    "OP_DAYS" VARCHAR2(7 BYTE), 
    "VALID_FROM" DATE, 
    "VALID_TO" DATE, 
);

• O_PREFIX+O_NUMBER+O_SUFFIX生成一个订单ID，如: AB2000S
• OP_DAYS:标记运输发生的日子，即: 1235 ->是指第一天(星期一)、第二天(星期二)等等。如果它每天都装船，它看起来是: 1234567，如果只是周一的船:1。
• VALID_FROM+VALID_TO:给我们出货的范围。例如，如果VALID_FROM: 01SEP13、VALID_TO: 14 OCT 13和OP_DAYS是: 15，这意味着在9月1日至10月14日之间每周一和周五装运一批货物。

为了简单起见，让我们将第一个称为"ID"，第二个可以保持OP_DAYS，"Range“则称为最后一个。

我必须确保没有插入重叠的记录。这意味着ID和OP_DAYS以及Range不匹配。

• ID匹配是直接向前的，它们是相同的字符串。
• OP_DAYS匹配意味着有一个公共数字，因此在我们的例子中，例如"1234“和"456”匹配，但是"123“和"456”不匹配。
• 范围匹配意味着日期有重叠，例如"01SEP13-15SEP13“和"10SEP13-01OCT13”匹配，但"01SEP13-15SEP13“和"16SEP13-01OCT13”则可以。

以上条件必须满足DB才能拒绝行的插入。因此，必须有一个ID匹配，一个OP_DAYS垫和一个范围匹配，在这种情况下，数据应该被拒绝。

还有一件事:如果这样做更容易，我就可以制作OP_DAYS 7列，所以每天都会有单独的列。这是只有当它使它真的很难或不可能在没有这种改变的情况下作出这种约束。

Answer 1

不能通过约束强制执行这样的规则。这可以使用触发器来完成，由于必须避免“变异表”问题，同时仍然要确保事务完整性(因此而不是使用自治事务的！)

还有一种使用带有约束的物化视图的方法，我已经编写了关于在我的博客上的文章(参见第一个示例)。然而，这是相当试验性的，在实际数据库中可能不实用(例如，可能对性能产生不利影响)。

最常见的解决方案是编写PL/SQL包API来执行逻辑和强制应用程序使用API，而不是直接插入/更新表。

Answer 2

下面是“后插入”或“更新”触发器，该触发器检查交叉并在找到异常时引发异常。

CREATE OR REPLACE TRIGGER transport_intersection_ck_trg
  AFTER INSERT OR UPDATE 
  ON transport
DECLARE
  cnt NUMBER;
BEGIN
  SELECT count(*) 
  INTO cnt
  FROM transport t1, transport t2
  WHERE t1.rowid != t2.rowid
  AND t1.PREFIX || t1."NUMBER" || t1.SUFFIX = t2.PREFIX || t2."NUMBER" || t2.SUFFIX
  AND 1 = CASE 
            WHEN INSTR(t1.op_days, 1) > 0 AND INSTR(t2.op_days, 1) > 0  THEN 1
            WHEN INSTR(t1.op_days, 2) > 0 AND INSTR(t2.op_days, 2) > 0  THEN 1
            WHEN INSTR(t1.op_days, 3) > 0 AND INSTR(t2.op_days, 3) > 0  THEN 1
            WHEN INSTR(t1.op_days, 4) > 0 AND INSTR(t2.op_days, 4) > 0  THEN 1
            WHEN INSTR(t1.op_days, 5) > 0 AND INSTR(t2.op_days, 5) > 0  THEN 1
            WHEN INSTR(t1.op_days, 6) > 0 AND INSTR(t2.op_days, 6) > 0  THEN 1
            WHEN INSTR(t1.op_days, 7) > 0 AND INSTR(t2.op_days, 7) > 0  THEN 1
            ELSE 0
          END
  AND  t1.valid_from >= t2.valid_to
  AND  t2.valid_from >= t1.valid_to
  ;        
  IF cnt > 0 THEN
    raise_application_error(-20000, 'intersection found');
  END IF;
END;
/

Answer 3

不要使用触发器来强制数据一致性。编写一个执行数据库端验证的模块。