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

Hello David,

I agree with Preston and Mark on using sequences. I gather the ID column's purpose is simply to serve as the primary key, correct?

Multiple users accessing your system will easily encounter unique constraint errors if you populate the id using the max function. See below:

SQL> drop table t;

Table dropped.

SQL> create table t(t_pk number(11) not null,   2 name varchar2(10));

Table created.
SQL> alter table t add constraint t_primary primary key(t_pk);

Table altered.

Now, I'll insert a record using the max function:

SQL> insert into t(t_pk,name) values ((select max(t_pk)+1 from t),'Arun');
insert into t(t_pk,name) values ((select max(t_pk)+1 from t),'Arun')
*

ERROR at line 1:
ORA-01400: cannot insert NULL into ("AMATHUR"."T"."T_PK")

The max function returns a null because there aren't any rows. But, even if I fix this, I'll still run into problems:

SQL> insert into t(t_pk,name) values ((select nvl(max(t_pk),0)+1 from t),'Arun')
;

1 row created.

Let's say another user logs in and tries the same thing: SQL> conn amathur_at_dbdev
Enter password:
Connected.
SQL> insert into t(t_pk,name) values ( (select nvl(max(t_pk)+1,0)+1 from t),'Mathur');
 **I'm blocked. I'll go and commit the previous insert. SQL> insert into t(t_pk,name) values ((select nvl(max(t_pk),0)+1 from t),'Arun')
;

1 row created.

SQL> commit;

Commit complete.

But, look what I encounter when I go back to the second session:

SQL> insert into t(t_pk,name) values ( (select nvl(max(t_pk)+1,0)+1 from t),'Mathur');
insert into t(t_pk,name) values ( (select nvl(max(t_pk)+1,0)+1 from t),'Mathur')

*

ERROR at line 1:
ORA-00001: unique constraint (AMATHUR.T_PRIMARY) violated

You won't get this problem with sequences.

Session 1:

SQL> create sequence t_seq start with 10;

Sequence created.

SQL> insert into t(t_pk,name) values (t_seq.nextval,'Name 1');

1 row created.

Session 2:

SQL> insert into t(t_pk,name) values (t_seq.nextval,'Name 2');

1 row created.

So, I think it's worth changing the application code.

Regards,
Arun Received on Thu Mar 24 2005 - 11:13:37 CST