Re: Automatic primary key in Oracle

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 Received on Thu Jan 18 1996 - 00:00:00 CET