Re: Does oracle have an auto increment column

From: Jonathan Gennick <gennick_at_worldnet.att.net>
Date: Sun, 07 Jun 1998 17:04:42 GMT
Message-ID: <6leh79$h5e@bgtnsc02.worldnet.att.net>

On Fri, 5 Jun 1998 09:12:20 +0200, "Dennis JR Harding" <dennish_at_wpr.co.za> wrote:

>Sorry to be a boar, being unfamiliar to Oracle I have the following
>question:
>Does Oracle have a data type of auto increment when creating a table
>(similar to Informix Serial data type of the Paradox AutoNumber).
>
>please respond by email to snseabrook @ wpr . co . za
Where is ZA?

Oracle doesn't have an auto incrementing column, but it does have autoincrementing sequences. You can use them to do what you want. Take a look at the following example. The SQL Reference manual explains sequences in more detail. Look under CREATE SEQUENCE.

SQL> create sequence key_for_table

  2         increment by 1
  3         start with 1
  4         nomaxvalue  --implies 10^27

  5 ;

Sequence created.

SQL>
SQL> select key_for_table.NextVal from dual;

  NEXTVAL

---

        1

SQL> select key_for_table.NextVal from dual;

  NEXTVAL

---

        2

SQL> select key_for_table.NextVal from dual;

  NEXTVAL

---

        3

SQL>
SQL> create table my_table (

  2          fld_pk  integer,
  3          fld_text        varchar2(2),
  4          constraint my_table_key
  5                  primary key (fld_pk)
  6          );

Table created.

SQL>
SQL> create or replace trigger my_table_set_key

  2          before insert on my_table
  3          referencing new as n
  4          for each row

  5 declare
  6 new_key integer;
  7 begin
  8 select key_for_table.nextval into new_key from dual;   9 :n.fld_pk := new_key;
 10 end;
 11 /

Trigger created.

SQL>
SQL> insert into my_table (fld_text) values ('A');

1 row created.

SQL> insert into my_table (fld_text) values ('B');

1 row created.

SQL>
SQL> select * from my_table;

   FLD_PK FL
--------- --

        4 A
        5 B

SQL>
SQL> insert into my_table (fld_pk,fld_text) values (1,'C');

1 row created.

SQL> select * from my_table;

   FLD_PK FL
--------- --

        4 A
        5 B
        6 C

SQL> commit;

Commit complete. Received on Sun Jun 07 1998 - 12:04:42 CDT