主页 / dba / 问题 / 24280
Accepted
kevinskio
Asked: 2012-09-14 07:03:58 +0800 CST

这个业务逻辑可以通过条件数据库约束来强制执行吗？

  • 772

我试图在数据库中复制包含 Intranet C# Web 应用程序的业务逻辑，以便其他数据库可以访问它并在相同的规则下工作。如果不使用 hack，这个“规则”似乎很难实施。

CREATE TABLE CASE_STAGE
(
  ID                        NUMBER(9)           PRIMARY KEY NOT NULL, 
  STAGE_ID                  NUMBER(9)           NOT NULL,
  CASE_PHASE_ID             NUMBER(9)           NOT NULL,
  DATE_CREATED              TIMESTAMP(6)        DEFAULT CURRENT_TIMESTAMP     NOT NULL,
  END_REASON_ID             NUMBER(9),
  PREVIOUS_CASE_STAGE_ID    NUMBER(9),
  "CURRENT"                 NUMBER(1)           NOT NULL,
  DATE_CLOSED               TIMESTAMP(6)        DEFAULT NULL
);

和

CREATE TABLE CASE_RECOMMENDATION
(
  CASE_ID                   NUMBER(9)           NOT NULL,
  RECOMMENDATION_ID         NUMBER(9)           NOT NULL,
  "ORDER"                   NUMBER(9)           NOT NULL,
  DATE_CREATED              TIMESTAMP(6)        DEFAULT CURRENT_TIMESTAMP     NOT NULL,
  CASE_STAGE_ID             NUMBER(9)           NOT NULL
);

ALTER TABLE CASE_RECOMMENDATION ADD (
  CONSTRAINT SYS_C00000
 PRIMARY KEY
 (CASE_ID, RECOMMENDATION_ID));

业务逻辑可以概括为

When Inserting into CASE_STAGE
If CASE_STAGE.STAGE_ID = 1646
THEN
 CASE_STAGE.PREVIOUS_STAGE_ID must be found in CASE_RECOMMENDATION.CASE_STAGE_ID

这个逻辑可以体现在 Check 约束中还是丑陋的触发器是唯一的方法？

编辑：

  • 对于 CASE_STAGE.STAGE_ID 的所有值，必须在 CASE_STAGE.ID 中找到 PREVIOUS_STAGE_ID 的值
  • 应用程序不允许从 CASE_RECOMMENDATION 中删除，一旦它不再是 CURRENT（即当 CASE_STAGE.CURRENT 的值为 0 时，该阶段关闭并且不能再更改，当 = 1 时，这是活动的阶段或行现在可以更改。）

编辑：在这里使用所有优秀的想法和评论是解决这个问题的有效方法

CREATE MATERIALIZED VIEW LOG ON CASE_STAGE
TABLESPACE USERS
STORAGE    (
            BUFFER_POOL      DEFAULT
           )
NOCACHE
LOGGING
NOPARALLEL
WITH ROWID;

CREATE MATERIALIZED VIEW LOG ON CASE_RECOMMENDATION
TABLESPACE USERS
STORAGE    (
            BUFFER_POOL      DEFAULT
           )
NOCACHE
LOGGING
NOPARALLEL
WITH ROWID;

CREATE MATERIALIZED VIEW CASE_RECOMMENDATION_MV REFRESH FAST ON COMMIT AS
  SELECT
         cr.ROWID cr_rowid, --necessary for fast refresh
         cs.ROWID cs_rowid, --necessary for fast refresh
         cr.case_id,
         cs.stage_id,
         cr.recommendation_id
         cr.case_stage_id,
         cs.previous_case_stage_id
  FROM   CASE_RECOMMENDATION cr,
         case_stage cs
  WHERE  cs.previous_case_stage_id = cr.case_stage_id (+)
  AND CS.PREVIOUS_CASE_STAGE_ID IS NOT NULL
  AND EXTRACT (YEAR FROM CS.DATE_CREATED) > 2010 --covers non conforming legacy data
  AND CR.RECOMMENDATION_ID IS NULL
  AND cs.stage_id =1646;  
--this last line excludes everything but problem cases due to the outer join

ALTER TABLE CASE_RECOMMENDATION_MV ADD CONSTRAINT CASE_RECOMMENDATION_ck CHECK (
    (previous_case_stage_id IS NOT NULL AND case_stage_id IS NOT NULL)
);

在没有推荐的情况下使用现有包插入 1646 阶段时，错误是

ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (APPBASE.CASE_RECOMMENDATION_MV_C01) violated
ORA-06512: at line 49

任务完成！不是物化视图的目的，但比触发器更好。

oracle database-design

4 个回答

  1. 如果CASE_RECOMMENDATION.CASE_STAGE_ID是唯一的，您可以将参照完整性与虚拟列 (11g+) 结合使用以使其成为条件：

    alter table CASE_RECOMMENDATION ADD CONSTRAINT unique_case_stage unique (CASE_STAGE_ID);

-- virtual column only defined when stage_id=1646
alter table CASE_STAGE add 
   (case_1646 as (case when stage_id=1646 then previous_case_stage_id end));

-- check that the virtual column is defined when stage_id=1646
alter table case_stage add 
    constraint chk_1646 check ( stage_id!=1646 or previous_case_stage_id is not null);

-- referential integrity
alter table case_stage add 
     constraint fk_1646 foreign key (case_1646) 
     references case_recommendation (case_stage_id);

    让我们检查：

    SQL> insert into  CASE_STAGE values (1, 1, 1, sysdate, null, null, 1, null, default);

1 row(s) inserted.

SQL> insert into CASE_RECOMMENDATION values (1, 1, 1, sysdate, 1);

1 row(s) inserted.

SQL> -- fails because previous_case_stage_id is null
SQL> insert into CASE_STAGE values (2, 1646, 1, sysdate, null, null, 1, null, default);

ORA-02290: check constraint (VNZ_TEST3.CHK_1646) violated

SQL> -- fails because previous_case_stage_id doesn't exist in CASE_RECOMMENDATION
SQL> insert into CASE_STAGE values (2, 1646, 1, sysdate, null, 2, 1, null, default); 

ORA-02291: integrity constraint (VNZ_TEST3.FK_1646) violated - parent key not found

SQL> -- succeeds !
SQL> insert into CASE_STAGE values (2, 1646, 1, sysdate, null, 1, 1, null, default); 

1 row(s) inserted.
    • 6
  2. Best Answer

    如果您想在数据库中“不可见地”应用复杂的约束，您可以通过创建物化视图然后对其应用约束来实现。

    在这种情况下，您可以使用 MV 外部连接CASE_RECOMMENDATION.CASE_STAGE_ID来实现CASE_STAGE.PREVIOUS_CASE_STAGE_ID。然后应该检查当 时这些都不为空CASE_STAGE.STAGE_ID = 1646，如下所示：

    --necessary for fast refresh
create materialized view log on case_stage with rowid;
create materialized view log on case_recommendation with rowid;

create materialized view mv refresh fast on commit as 
  select 
         cr.rowid cr_rowid, --necessary for fast refresh
         cs.rowid cs_rowid, --necessary for fast refresh
         cr.case_id,
         cr.recommendation_id,
         case when cs.stage_id = 1646 then
           'Y'
         else
           'N'
         end do_chk,
         cr.case_stage_id,
         cs.previous_case_stage_id
  from   CASE_RECOMMENDATION cr, 
         case_stage cs
  where  cs.previous_case_stage_id = cr.case_stage_id (+);

alter table mv add constraint mv_ck check (
    (do_chk = 'Y' and previous_case_stage_id is not null and case_stage_id is not null )
    or
    (do_chk = 'N')
);

insert into  CASE_STAGE values (1, 1, 1, sysdate, null, null, 1, null);

insert into CASE_RECOMMENDATION values (1, 1, 1, sysdate, 1);
commit;

insert into CASE_STAGE values (2, 1646, 1, sysdate, null, null, 1, null);

pro fails because previous_case_stage_id is null
commit;
SQL Error: ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (CHRIS.MV_CK) violated
12008. 00000 -  "error in materialized view refresh path"

insert into CASE_STAGE values (2, 1646, 1, sysdate, null, 2, 1, null); 

pro fails because previous_case_stage_id doesn't exist in CASE_RECOMMENDATION'
commit;
SQL Error: ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (CHRIS.MV_CK) violated
12008. 00000 -  "error in materialized view refresh path"

pro succeeds !
insert into CASE_STAGE values (2, 1646, 1, sysdate, null, 1, 1, null); 
commit;

pro we can't delete stuff from case recommendation now 
delete CASE_RECOMMENDATION;
commit;
SQL Error: ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (CHRIS.MV_CK) violated
12008. 00000 -  "error in materialized view refresh path"

    MV 上的检查约束只会在刷新时调用，因此要成功运行，您需要确保在 COMMIT 时完成。这将增加您的提交时间处理，因此您需要牢记以下几点：

    • 除非你的数据集非常小，否则 MV 应该是 REFRESH FAST。在构建 MV 的方式上有一些限制，以允许这样做
    • 如果您在一个事务中有多个插入，则只有在您提交时才会抛出错误，这可能会使识别有问题的语句变得更加困难
    • 如果您有高水平的并发插入，这可能会导致一些并发问题

    由于此解决方案在 SQL 层中实施约束，因此它克服了过程解决方案中讨论的一些并发问题。

    更新

    正如 Vincent 所指出的，MV 的大小可以通过仅包含 stage_id = 1646 的行来减小。可以重新编写查询以不消耗任何行，但我想不出该怎么做现在：

    create materialized view mv refresh fast on commit as 
  select 
         cr.rowid cr_rowid, --necessary for fast refresh
         cs.rowid cs_rowid, --necessary for fast refresh
         cr.case_id,
         cr.recommendation_id,
         cr.case_stage_id,
         cs.previous_case_stage_id
  from   CASE_RECOMMENDATION cr, 
         case_stage cs
  where  cs.previous_case_stage_id = cr.case_stage_id (+)
  and    cs.stage_id = 1646;

alter table mv add constraint mv_ck check (
    (previous_case_stage_id is not null and case_stage_id is not null)
);
    • 4

  3. 将业务逻辑放在数据库中是令人钦佩的，你当然应该尽可能地为这样的事情实施约束。然而，触发器并不是唯一的选择。您可以在执行插入的 PL/SQL 包中解决问题。通过这样做，您可以获得其他好处，例如减少客户端代码、减少上下文切换、自动绑定、客户端应用程序独立性等。这是一个不完整的示例（没有同时运行它所必需的锁定）。

    CREATE OR REPLACE PACKAGE CaseStage As

Procedure InsertCaseStage (
   pId                      In Case_Stage.Id%Type,
   pStage_Id                In Case_Stage.Stage_Id%Type,
   pDate_Created            In Case_Stage.Date_Created%Type DEFAULT Current_Timestamp,
   pEnd_Reason_Id           In Case_Stage.End_Reason_Id%Type,
   pPrevious_Case_Stage_Id  In Case_Stage.Previous_Case_Stage_Id%Type,
   pCurrent                 In Case_Stage."CURRENT"%Type,
   pDate_Closed             In Case_Stage.Date_Closed%Type DEFAULT NULL
   );
END;
/

CREATE OR REPLACE PACKAGE BODY CaseStage As

Procedure InsertCaseStage (
   pId                      In Case_Stage.Id%Type,
   pStage_Id                In Case_Stage.Stage_Id%Type,
   pDate_Created            In Case_Stage.Date_Created%Type DEFAULT Current_Timestamp,
   pEnd_Reason_Id           In Case_Stage.End_Reason_Id%Type,
   pPrevious_Case_Stage_Id  In Case_Stage.Previous_Case_Stage_Id%Type,
   pCurrent                 In Case_Stage."CURRENT"%Type,
   pDate_Closed             In Case_Stage.Date_Closed%Type DEFAULT NULL
   ) 
As
   cPreviousCaseStageCheck Case_Stage.Stage_Id%Type := 1646;   
   vPreviousCount Number(1);
Begin
   If (pStage_Id = cPreviousCaseStageCheck) Then
      SELECT count(*) INTO vPreviousCount FROM Case_Recommendation 
      WHERE Case_Stage_Id = pPrevious_Case_Stage_Id
      AND rownum<=1;
      If (vPreviousCount <> 1) Then
         Raise_Application_Error(-20001,'Previous_Stage_Id must be found in '
            || 'Case_Recommendation. ' || pPrevious_Case_Stage_Id || ' was not.');
      End If;
   End If;

   /* INSERT... */
End;

END;
/
    • 3

  4. 我在您的设计中缺少像 CASE_RECOMMENDATION_LIST(CASE_STAGE_ID NUMBER(9) PRIMARY KEY) 这样的表。每个 CASE_RECOMMENDATION 都是对应的 CASE_RECOMMENDATION_LIST 的成员，这可以由外键处理。CASE_RECOMMENDATION_LIST 必须至少包含一个 CASE_RECOMMENDATION。CASE_RECOMMENDATION_LIST 的创建和删除可以通过简单的触发器处理：在创建此 CASE_STAGE_ID 的第一个 CASE_RECOMMENDATION 之前为 CASE_STAGE_ID 创建一个 CASE_RECOMMENDATION_LIST，在删除此 CASE_STGE_ID 的最后一个 CASE_RECOMMENDATION 后删除它。CASE_STAGE 最多引用一个使用 CASE_STAGE.PREVIOUS_CASE_STAGE_ID 的 CASE_RECOMMENDATION_LIST。如果 CASE_STAGE.STAGE_ID=1646，CASE_STAGE.PREVOUS_STAGE_ID 不得为空。

    为 1646 个 CASE_STAGE 创建一个自己的实体（并因此创建一个表）也许是更好的方法，但我不会进一步分析它。

    • 0

相关问题