primary keys and auto numbering

[niqola]

Hi,
I must create an Oracle DB and I can inspire me of the old DB in Access. In some tables, there's an auto-numbering. For example:
tbl_Deliverers(
no_Deliverer(auto-number)
name_Deliverer(text))

I think of throwing away "no_deliverer" but of keeping "name_Deliverer" under Oracle. I would just get a kind of lookup table. But I may not be aware of the consequences.
I have many tables like that under access and i'm looking for advice.
Thx

 

[sem]

If you need some kind of unique numbering look at sequences

Regards, Dima

 

[jee]

If I were you, I would keep the columns that are auto-numbered in access and declare these to be the primary keys on those tables. There are numerous good reasons for using "artificial" primary keys. As DIMA stated above, you can get the same functionality as auto-numbering by using a sequence combined with a BEFORE ROW trigger. When a user executes an insert statement like this:

INSERT INTO tbl_Delivers (name_Deliverer)
VALUES ('XXX');

The following trigger will cause the no_Deliverer column to be set to a unique value:

CREATE OR REPLACE TRIGGER gen_pk_for_tbl_Delivers
BEFORE INSERT
FOR EACH ROW

BEGIN
   SELECT tbl_Delivers_seq.NEXTVAL INTO :NEW.no_Deliverer
     FROM dual;
END;

Assumes that you have previously created the sequence tbl_Delivers_seq