Re: Autonumber

On Thu, 9 Apr 1998 15:50:50 -0400, "bubba" <brittonb_at_webt.com> wrote:

>In MS Access there is an autonumber type which guarentees a unique primary
>key that is automatically generated.
>
>I have created a test table with two fields on an oracle database.
>
>testID ---primary key - unique
>TestData -- varchar2 (50) nulls allowed
>
>When I try and enter data into the Testdata field using ODBC via MS access
>linked tables, I get an error message that says that testID cannot be null.
>
>Why doesn't oracle create a unique number here?
>
>What am i doing wrong?

You are doing nothing "wrong". You just aren't doing everything that you need to do. You should read up on Oracle "sequences". A "sequence" is simply a stored counter that is incremented each time it is referenced.

To get autoincrementing keys in Oracle, you first need to create the table and a sequence to go along with it. Then you need to write a trigger to set the sequence for each row inserted into the table. See the example I've pasted in below.

SQL> create table test2
  2 (testID number,
  3 TestData varchar2(50));

Table created.

SQL> create sequence test2_pk_seq;

Sequence created.

SQL> create or replace trigger test2_set_key   2 before insert on test2
  3 for each row
  4 declare
  5 new_key number;
  6 begin
  7 select test2_pk_seq.NEXTVAL
  8 into new_key from dual;
  9 :new.testID := new_key;
 10 end;
 11 /

Trigger created.

SQL> insert into test2 (testData) values ('row one');

1 row created.

SQL> insert into test2 (testData) values ('row two');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test2;

   TESTID TESTDATA

--------- --------------------------------------------------
        1 row one
        2 row two