Re: Automatic primary key in Oracle

Michael Ho wrote:
>
> Veikko V{{t{j{ TKKK wrote:
> >
> > How to use the oracle sequence feature? I want to use it for an auto
> > increment primary key field but I can't figure out how to do it. If I'm
> > doing something totally wrong, tell me! This is what I do:
> >
> > First I create the sequence with CREATE SEQUENCE SEQ_PRKEY;. Then I
> > create a table that uses that key with CREATE TABLE SOMETABLE (ID INTEGER
> > NOT NULL, SOMEFIELD VARCHAR(10),...,PRIMARY KEY(ID)); Oracle manuals say
> > clearly that I can't use sequences in default values, so I figure that I need
> > a trigger to put the sequence number in a record records primary key field.
> > But how do I get the sequence number in a PL/SQL trigger??? Oracle doesn't
> > accept NEW.ID:=SEQ_PRKEY.NEXTVAL; in the trigger body. WHY?
> > have you try :
> select schema.SEQ_PRKEY.NEXTVAL into NEW.ID from dual;
>
> > Only thing that I want to do is to have an totally automatic primary key
> > field. In interbase it works just like I describe above. How do I do it in
> > Oracle???
> >
> > Thanks!
> >
> > Veikko Vaataja
> > veikko.vaataja_at_abo.fi
> >
>
> To me, the problem is not how, but why you do this ?
> In Oracle, we don't need every table to have a primary key. I can't see why you include something
> into your table which is technically unneccessary.
>
> Mind you : Oracle have unique ROWID which could uniquely identify every row in the table, much
> like the sequence you want.
>
> I hope I can help. If there is additional reason, mail me. (I am lazy browsing the news)
>
> Michael Ho

Michael

This is not good advice.

A unique primary key is required in a table when it is to be referenced by a foreign key in another table. Database designers often choose to use an Oracle sequence because it guarantees the uniqueness constraint.

The Oracle ROWID should NEVER BE USED FOR THIS PURPOSE as it may change as the table is modified.

Jeff Stander
WillStand Consultants Pty Ltd
Hobart, Tasmania Received on Sun Jan 21 1996 - 00:00:00 CET