Referential Intigrity

Question

I have following tables.

ACCOUNTS (Parent Table) with columns as ACC_ID, VALID_FROM and VALID_TO with unique key on ACC_ID, VALID_FROM.

ACCOUNT_BUSII_FUNCTIONS (Child Table) with columns as ACC_ID, BUSINESS_FUNCTION, VALID_FROM and VALID_TO with unique key on ACC_ID, BUSINESS_FUNCTION, VALID_FROM.

VALID_FROM and VALID_TO are dates.

I need to build a relationship where every record deleted in ACCOUNTS (Parent Table) should check for the child record falling in the same date range. Similarly, before inserting into child, check if parent exists with the valid date range.

Obviously, I can not use foreign key constraints as date ranges are involved.

Tried writing a function and calling in CHECK constraint but did not work as CHECK constraint did not allow user defined function.

Am clueless... any help please...

Cheers TZH

"Obviously, I can not use foreign key constraints as date ranges are involved." Date ranges aren't involved in the key constraints. "VALID_FROM" is a date; it's not a range. There's no reason a foreign key constraint won't work as far as the dbms is concerned. If there's a business reason a foreign key including "VALID_FROM" won't work, edit your question and make that clearer. — Mike Sherrill 'Cat Recall'
@MikeSherrill'CatRecall' - It appears the child records can fall within the validity range of the parent record: the bounds don't have to match. — APC

Bob Jarvis - Reinstate Monica Bob Jarvis - Reinstate Monica · Accepted Answer · 2014-12-25T00:14:07

I think triggers would be a better choice than a function. Try something like:

CREATE TRIGGER ACCOUNTS_BD
  BEFORE DELETE ON ACCOUNTS
  FOR EACH ROW
DECLARE
  nChild_rows  NUMBER:
BEGIN
  SELECT COUNT(*)
    INTO nChild_rows
    FROM ACCOUNT_BUSII_FUNCTIONS a
    WHERE a.ACC_ID = :OLD.ACC_ID AND
          (:OLD.VALID_FROM BETWEEN a.VALID_FROM AND a.VALID_TO OR
           :OLD.VALID_TO BETWEEN a.VALID_FROM AND a.VALID_TO OR
           a.VALID_FROM BETWEEN :OLD.VALID_FROM AND :OLD.VALID_TO OR
           a.VALID_TO BETWEEN :OLD.VALID_FROM AND :OLD.VALID_TO);

  IF nChild_rows > 0 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Child rows found when deleting from ACCOUNTS');
  END IF;
END ACCOUNTS_BD;

CREATE TRIGGER ACCOUNT_BUSII_FUNCTIONS_BI
  BEFORE INSERT ON ACCOUNT_BUSII_FUNCTIONS
  FOR EACH ROW
DECLARE
  nParent_rows  NUMBER;
BEGIN
  SELECT COUNT(*)
    INTO nParent_rows
    FROM ACCOUNTS a
    WHERE a.ACC_ID = :NEW.ACC_ID AND
          (:NEW.VALID_FROM BETWEEN a.VALID_FROM AND a.VALID_TO OR
           :NEW.VALID_TO BETWEEN a.VALID_FROM AND a.VALID_TO OR
           a.VALID_FROM BETWEEN :NEW.VALID_FROM AND :NEW.VALID_TO OR
           a.VALID_TO BETWEEN :NEW.VALID_FROM AND :NEW.VALID_TO);

  IF nParent_rows = 0 THEN
    RAISE_APPLICATION_ERROR(-20002, 'No parent row found when inserting into ' ||
                                    'ACCOUNT_BUSII_FUNCTIONS');
  END IF;
END ACCOUNT_BUSII_FUNCTIONS_BI;

Not tested on animals - you'll be first! :-)

Share and enjoy.

Referential Intigrity

1 Answers