Oracle – Enforcing Business Logic with Conditional Database Constraints

constraintdatabase-designoracleoracle-11g-r2

I am trying to duplicate the business logic embodied an intranet C# web application in the database so that other databases can access it and work under the same rules. This "rule" seems difficult to implement without using hacks.

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
);

and

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));

The business logic can be summed up as

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

Can this logic be embodied in a Check constraint or is an ugly trigger the only way?

Edit:

  • For all values of CASE_STAGE.STAGE_ID the value for PREVIOUS_STAGE_ID must be found in CASE_STAGE.ID
  • The application does not allow deletions from CASE_RECOMMENDATION once it is no longer CURRENT ( ie when the value of CASE_STAGE.CURRENT is 0 this stage is closed and can no longer be changed, when = 1 this is the stage, or row, that is active and can be changed now.)

Edit: using all the excellent ideas and comments here is a working solution to this problem

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)
);

When inserting a 1646 stage using existing packages without a recommendation the error was

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

Job done! Not what a materialized view was intended for but better than a trigger.

Best Answer

If you have complex constraints you want to apply "invisibly" in the database, you can do so by creating a materialized view then applying constraints to that.

In this case, you can do it using an MV outer-joining CASE_RECOMMENDATION.CASE_STAGE_ID to CASE_STAGE.PREVIOUS_CASE_STAGE_ID. A check should then be made that neither of these are null when the CASE_STAGE.STAGE_ID = 1646, like so:

--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"

The check constraint on the MV will only be invoked when it is refreshed, so for this to work successfully you need to ensure that this is done on COMMIT. This will add to your commit time processing, so you'll need to bear in mind the following:

  • Unless you datasets are trivially small, the MV should be REFRESH FAST. There are some restrictions on how you construct your MV to allow this
  • If you have multiple inserts in a transaction the error will only be thrown when you commit, possibly making it harder to identify the offending statement
  • If you have a high level of concurrent inserts, this could lead to some concurrency issues

As this solution implements the constraints in the SQL layer, it overcomes some of the concurrency issues discussed in the procedural solution however.

UPDATE

As pointed out by Vincent, the size of the MV can be reduced by only including the rows with stage_id = 1646. It may be possible to re-write the query to consume no rows, but I can't think how to do that right now:

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)
);
Related Question