Re: Default column value of MAX +1 of column

From: <fitzjarrell_at_cox.net>
Date: 24 Mar 2005 09:03:26 -0800
Message-ID: <1111683806.331194.320520@z14g2000cwz.googlegroups.com>

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.
>
> Is it at all possible to have a default value as I outline above?
>
> Any help or pointers you can give would be greatly appreciated!
>
> David.

I supply the following example in hopes it will be of use:

SQL> create table seq_test (myid number, 2 myval varchar2(40));

Table created.

SQL>
SQL> alter table seq_test add constraint seq_test_pk primary key(myid);

Table altered.

SQL>
SQL> create sequence myseq start with 1 increment by 1 nomaxvalue;

Sequence created.

SQL>
SQL> create trigger pop_myid
  2 before insert on seq_test
  3 for each row
  4 begin

  5  	     select myseq.nextval into :new.myid
  6  	     from dual;

7 end;
8 /

Trigger created.

SQL>
SQL> insert into seq_test (myval) values ('This is a test.');

1 row created.

SQL> insert into seq_test (myval) values ('This is also a test.');

1 row created.

SQL> insert into seq_test (myval) values ('More testing.');

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select myid, myval
2 from seq_test;

MYID MYVAL

---------- ----------------------------------------
         1 This is a test.
         2 This is also a test.
         3 More testing.

SQL> Note the increasing value of MYID, and how the sequence is used via a before insert trigger. Yes, this could cause some code changes, as a column list will be necessary for your insert statements to successfully execute. This, to me, is a small price to pay for such a convenient and reliable method of ID population.

David Fitzjarrell
(no relation) Received on Thu Mar 24 2005 - 11:03:26 CST

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