Re: Complicated unique constraint

From: Ken Leach <KLeach_at_ids.net>
Date: Thu, 25 Feb 1999 12:17:12 -0500
Message-ID: <36D58598.AA116571@ids.net>

If I understand at all what you are talking about, your problem seems to be with database modeling NOT a constraint issue.

You said you have "roy" and "smithr01" accounts on several machines. I am guessing on this one but, is the problem that you have the same accounts on several machines that exist on the same system? So your table will not allow

SYSTEM      USERNAME  PER_ID
mainframe   roy       12
mainframe             12  (assuming the kerberos_id from PERSON)

You said "our idea was to use the convention that a null in account.username means to use the kerberos_id contained in the pointed-to record of the person table."

I think that the NULL field is the source of your problem. Write a TRIGGER on the ACCOUNT table checking for a NULL value for the username field.
If it is NULL then automatically populate it with the PERSON.KERBEROUS_ID

• Trigger ACCOUNT_BIU

CREATE OR REPLACE TRIGGER account_biu_t BEFORE INSERT OR UPDATE ON account
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
WHEN ( NEW.USERNAME IS NULL ) BEGIN
  SELECT LTRIM(kerberos_id,20)
  INTO :NEW.username
  FROM person
  WHERE person.per_id = :NEW.per_id;

  EXCEPTION
    WHEN OTHERS THEN

      dbms_output.put_line('Error with Trigger: account_biu_t');
      dbms_output.put_line('Error: ' || SQLERRM);
      RAISE;

    END;
END;
/

• End of DDL script for ACCOUNT_BIU

If I totally missed the point, which is possible here. You seem to have a data modeling problem here as well. If there are several accounts per system, then you need to adjust your model to support that, and not try some fancy NULL column technique.

Hope I was at least a little helpful..

Good luck..

-Ken Leach
 Shoreline Software Received on Thu Feb 25 1999 - 11:17:12 CST