Can this business logic be enforced by a conditional database constraint?

Question

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.

Unfortunately not. A case stage can have one or more recommendations at multiple stages. There can be zero to many stages with CASE_STAGE.STAGE_ID=1646 — kevinsky, Sep 13 '12 at 16:09
You can't use referential integrity with non-unique columns unfortunately. — Vincent Malgrat, Sep 13 '12 at 16:14
What's so special about 1646? Is changing the design (splitting table into two) an option? — ypercubeᵀᴹ, Sep 13 '12 at 16:50
And how strictly is that "when inserting into" to be interpreted ? Is it valid to do an allowed insert, and then delete the referenced row from the parent ? — Erwin Smout, Sep 13 '12 at 17:12

Chris Saxon · Accepted Answer · 2012-09-17 11:41:09Z

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

Fabulous, I ran into this with another MV but only saw adding a constraint as a problem not a solution — kevinsky, Sep 16 '12 at 16:43
I don't like this solution. Expressing some kind of referential integrity in this way is possible but very confusing. You have pointed out that the checking of the constraints will be done at the end of the transaction. That can also be confusing. Also the way implementing the check by storing this joined data will take a lot of space. You implement the fact that there are n recommadations for a case_stage by storing n rows. For me this looks like if one wants to get the sum of to numbers a and b one increments a b-times to get the result. — miracle173, Sep 17 '12 at 7:14
Could you store only the rows that need to be checked in the MV (by adding a WHERE clause to the MV query)? Or even better, store only the offending rows (so that the MV stays empty). — Vincent Malgrat, Sep 17 '12 at 7:55
Good point Vincent, I've added an update for that. If I can think of a way to exclude all rows (or anyone else can tell me!) I'll update it again. — Chris Saxon, Sep 17 '12 at 11:42
@miracle173 - I never said this was a good solution, just that it's possible! I agree that this is complicated and potentially confusing. However the procedural code required to implement similar functionality in a multi-user environment is likely to be complicated as well - particularly if deletes are allowed. At least this solution guarantees the database can't be in an inconsistent state as defined by the rules in the question. — Chris Saxon, Sep 17 '12 at 11:45

Vincent Malgrat · Accepted Answer · 2012-09-13 16:28:57Z

If CASE_RECOMMENDATION.CASE_STAGE_ID is unique, you can use referential integrity with a virtual column (11g+) to make it conditional:

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

Let's check:

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.

Thank you, a great answer that I will be able to use for other "rules", just not this one. — kevinsky, Sep 13 '12 at 16:19

Leigh Riffel · Accepted Answer · 2012-09-14 12:35:43Z

Putting business logic in the database is admirable, and you certainly should implement constraints for things like this when you can. However, a trigger isn't the only alternative. You can solve the problem in the PL/SQL package doing the insert. By doing so you gain other benefits such as reduced client side code, fewer context switches, automatic binds, client application independence, etc. Here is an incomplete example (without the locking that would be necessary to run this concurrently).

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

I'm a great believer in packages and already have one that does CRUD operations and logging on these tables. However I cannot enforce the use of the package if another database attempts inserts through a link. I have to rely on the due diligence of the programmer. A constraint is cleaner, easier to discover and document but your answer would work if a package was used. — kevinsky, Sep 13 '12 at 21:57
While a procedure is definitely the better choice when constraints are unavailable or overly complicated, they are harder to code properly. For instance your procedure can be abused in a multi-user environment: User A inserts a 1646, user B deletes the recommendation, user A commits => DB is left in an inconsistent state. You would need strong locks to achieve the same functionalities as a constraint. — Vincent Malgrat, Sep 14 '12 at 8:48
Excellent point, now I see why the application does not allow deletes on entries in CASE_STAGE or CASE_RECOMMENDATION after the stage is closed. — kevinsky, Sep 14 '12 at 12:15
@kevinsky - I agree that this logic would be better in a constraint, regardless, why can't you enforce use of the package? By not letting anyone login as the table owner and not granting insert privileges on the table you can ensure that all access goes through the package. — Leigh Riffel, Sep 14 '12 at 12:18
@Vincent Malgrat - You are absolutely correct. Constraints are preferred when possible and locks would be needed in a concurrent environment. — Leigh Riffel, Sep 14 '12 at 12:30

miracle173 · Accepted Answer · 2012-09-17 09:57:48Z

I am missing a table like CASE_RECOMMENDATION_LIST(CASE_STAGE_ID NUMBER(9) PRIMARY KEY) in your design. Each CASE_RECOMMENDATION is member of the correspondent CASE_RECOMMENDATION_LIST This can be handled by a foreign key. A CASE_RECOMMENDATION_LIST must contain at least one CASE_RECOMMENDATION. Creation and deletion of a CASE_RECOMMENDATION_LIST can be handled by simple triggers: create a CASE_RECOMMENDATION_LIST for a CASE_STAGE_ID before the first CASE_RECOMMENDATION for this CASE_STAGE_ID is created, delete it after the last CASE_RECOMMENDATION for this CASE_STGE_ID is deleted. CASE_STAGE references at most one CASE_RECOMMENDATION_LIST using the CASE_STAGE.PREVIOUS_CASE_STAGE_ID. CASE_STAGE.PREVOUS_STAGE_ID must not be null if CASE_STAGE.STAGE_ID=1646. A an errormessage will be raised immediately after one wants to delete the last CASE_RECOMMENDATION of a CASE_STAGE_ID if this will be still needed.

Perhaps it would be a better way to create an own entity (and therefore a table) for the 1646 CASE_STAGEs but I will not analyze this further.

Stack Exchange Network

current community

your communities

more stack exchange communities

Can this business logic be enforced by a conditional database constraint?

4 Answers 4

Your Answer

Not the answer you're looking for? Browse other questions tagged oracle database-design oracle-11g-r2 constraint or ask your own question.

Linked

Hot Network Questions

Can this business logic be enforced by a conditional database constraint?

4 Answers 4

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged oracle database-design oracle-11g-r2 constraint or ask your own question.

Linked

Related

Hot Network Questions