Re: Default column value of MAX +1 of column - possible in Oracle 9i?

"Preston Kemp" <dontwantany_at_nowhere.invalid> wrote in message news:gJB0e.6554$Ww.4817_at_newsfe3-win.ntli.net...
> David FitzGerald wrote:
>
>> Hello -
>>
>> I'm trying to do something which I think ought to be simple, but am
>> finding hard!
>>
>> I have a table which I want the default value of the "ID" column to be
>> "MAX(ID)+1" - I can't get this to work. Is this possible to do, or am
>> I barking up the wrong tree? The reason I need to do this is that an
>> application needs to add to this table, but will not be able to do a
>> "select MAX(ID)+1 from emp" before it inserts the new row. It also
>> can't do a subselect in its insert. A PITA!
>>
>> I have thought about using a sequence instead, but this would require
>> changing application code which I am reluctant to do.
>
> You could put a 'before insert' trigger on the table to select from a
> sequence.
>
>
> --
> Preston.

I would agree, but say 'should' instead of 'could'

The default value you are attempting to define is not legal for a number of reasons -- suggest you check out the limitations listed in Oracle's SQL Reference manual.

Using a sequence with the trigger is the recommended approach with Oracle. Be aware, however, that you will have gaps in your ID's -- which should not be a problem in the vast majority of cases.

++ mcs Received on Thu Mar 24 2005 - 10:24:11 CST