Enforce a constraint problem This business model is for a case management database. This is closely modeled on the idea of a file folder representing the phase and a sequential checklist representing the stages. A case consists of a phase that can have one or more stages.  A phase can only have one stage that is "Current" or open at any one point in time.  A case can only start from one type of stage but can progress to any one of a number of stages that are end types. In this business model there are many different types of phases and stages

An example: you apply for a license. The process always starts with you submitting a form but can have different endings: the application is approved or rejected or sent back for more information. 

Edit: @Colin 't Hart  asks what a phase is in relation to a case. Here is where trying to simplify a question can omit details.  The complete schema structure is:
- one case can have one or more phases but only one phase is open or "current" at at time. 
- each phase can have one or more stages but only one phase is open or "current" at a time.
- there are different types of cases/phases/stages and transitions from the current unit to the next unit require adding a close date to the current and inserting a new record with an open date.
An example: a production line for widgets


a production ticket initiates the creation of the case
the first phase: sourcing components is created
the first stage: contacting suppliers is created
the first stage is completed, the second stage: orders from suppliers is opened
orders stage is closed, inventory check stage is created
inventory stage is closed, sourcing components phase is closed
new phase: assembly is opened
new stage: move components to shop floor is opened
moving components stage is closed, new stage production line is opened
and so on....


Problem:


the existing table structure is flawed in that the same information (what is the first type of stage for a kind of phase) is stored in two different tables
You can have more than one entry in STAGE where IS_START_STAGE = 1 which violates a business rule
You can insert a new entry into STAGE where IS_START_STAGE = 1 and this does not match the corresponding entry in PHASE_FIRST_STAGE
the relationship should be something like constraint PHASE_FIRST_STAGE.STAGE_ID can only be in the entries in STAGE where IS_FIRST_STAGE = 1
Is there anyway to enforce these business rules?




CREATE TABLE PHASE_FIRST_STAGE
(
  PHASE_ID           NUMBER(9)           NOT NULL, --PRIMARY KEY and foreign key to PHASE
  STAGE_ID           NUMBER(9)           NOT NULL,  --FOREIGN KEY to STAGE table
);
ALTER TABLE PHASE_FIRST_STAGE ADD (CONSTRAINT PFS01
  FOREIGN KEY (PHASE_ID) 
  REFERENCES PHASE (ID),
  FOREIGN KEY (STAGE_ID) 
  REFERENCES STAGE (ID));
COMMENT ON TABLE PHASE_FIRST_STAGE IS 'Contains the default first stages to enter when a phase is entered.';

CREATE TABLE STAGE
(
  ID               NUMBER(9)           NOT NULL, --PRIMARY KEY
  PHASE_ID         NUMBER(9)           NOT NULL,  --FOREIGN KEY to PHASE
  DISABLED         NUMBER(1)           DEFAULT 0   NOT NULL,  --CHECK IN (0,1)
  IS_START_STAGE            NUMBER(1),--CHECK IN (0,1)
  IS_END_STAGE              NUMBER(1) --CHECK IN (0,1)
);

COMMENT ON TABLE STAGE IS 'Contains all the stages a phase can have. Each stage must have only one phase. ';
--not shown is a similar table called PHASE with a one phase =&gt; many type of stage relationship
Justin Cave's answer here and Tom Kyte's pointed me to a solution using a function based index. I think this can be made even simpler with some more thought but this works now:
CREATE OR REPLACE FUNCTION UNIQUE_START_STAGE (
   phase_id_in   IN NUMBER,
   stage_id_in   IN NUMBER)
   RETURN NUMBER
   DETERMINISTIC
IS
 -- PURPOSE:enforce business logic that a phase can have only one stage where
 -- the disabled field has a value of 0 and IS_START_STAGE has a value of 1

   v_count   NUMBER (9);
BEGIN
   SELECT COUNT (s.id)
     INTO v_count
     FROM STAGE s
    WHERE     S.IS_START_STAGE = 1
          AND s.disabled = 0
          AND S.PHASE_ID = phase_id_in;

 IF v_count = 1
 THEN
     --return the primary key if there is only one
     v_count := stage_id_in;
  ELSIF v_count &lt; 1
  THEN
     v_count := NULL;
  END IF;

   RETURN v_count;
END UNIQUE_START_STAGE;

and then we create an index based the idea that there can only be one child stage that is enabled for a phase that is the start stage
CREATE UNIQUE INDEX unique_start_stage_idx
   ON stage (
      CASE
         WHEN disabled = 1 THEN NULL
         WHEN is_start_stage = 0 THEN NULL
         ELSE UNIQUE_START_STAGE (phase_id, id)
      END);

--and add the same constraint to the other table

CREATE UNIQUE INDEX unique_start_stage_idx2
ON PHASE_FIRST_STAGE (
UNIQUE_START_STAGE (phase_id, stage_id));
This solution partially solves the problem:

it enforces that there is only one entry in STAGE for each value of PHASE_ID where IS_START_STAGE =1 and DISABLED = 0
it enforces this same uniqueness in PHASE_FIRST_STAGE
it does not enforce that an entry in STAGE is also in PHASE_FIRST_STAGE
you could replace the PHASE_FIRST_STAGE table with a view of STAGE that cleans up the last issue