Re: Surrogat data types

Achim Reiners wrote:

> Hello,
>
> I'm looking for a surrogat data type for primary keys in ORACLE.
> Something like SERIAL in Informix or IDENTITY in Sybase.
> The database should generate a unique value by itself.
>
> I'm not yet very familar with ORACLE. There is a construct called
> SEQUENCE
> in ORACLE generating unique numbers. But as far as I know there is no
> relation between the sequence and the column that should have
> incremented values.

The "relationship" is a trigger on the table which puts the sequence value into the surrogate key.For example

create trigger mytable_ir
before insert on mytable
for each row
begin
  select my_sequence.nextval
  into :NEW.surrogate_key_column
  from dual;
end;
/

One aspect of sequences which causes a regular number of questions in this group is that the sequence number (as used in the code above) will be lost if the transaction is rolled back. Therefore, you cannot rely on the surrogate keys being consecutive or in any particular order.

HTH
Geoff

> The problem is that the program/person doing the insert is responsible
> for choosing the
> correct sequence type. This is a quite bad situation, isn't it.
>
> My question:
> Is there another way to have ORACLE generate surrogat-keys in a way that
> the user
> does not have to keep track of choosing the right sequence-type?
>
> Thanks for any help
>
> Achim
>
> P.S.: As I don't read this group regularly, respond by Email in
> addition.
Received on Thu May 20 1999 - 21:49:50 CDT