Re: SQL Contraints

From: Arjan van Bentem <avbentem_at_DONT-YOU-DAREdds.nl>
Date: Tue, 18 May 1999 18:40:12 +0200
Message-ID: <7hs5eu$l41$1@weber.a2000.nl>

Patrick Lanphier wrote
> account_number INTEGER DEFAULT ADDRESSES.nextval

As far as I know, you cannot do this. Instead, you'd need a trigger. Something like:

    create or replace trigger biu_account

        before insert or update on account
        for each row

    as
    begin

        if :new.account_number is null then
            select addresses.nextval
            into :new.account_number
            from dual;
        end if;

    end;
    /
    show errors

If you do not want the key to be changed during an update, use:

    if inserting and (:new.account_number is null) then

            select addresses.nextval
            into :new.account_number
            from dual;
    elseif updating then
        :new.account_number = :old.account_number;

    end if;

Arjan. Received on Tue May 18 1999 - 11:40:12 CDT