auto number??

rhs98 · #1 (**permalink**) 03-03-03, 12:49

Is there an oracle equivelent of an Access "auto number" field - i.e a number that is unique and automatically generated?

Or do i have to write a trigger or something???

andrewst · #2 (**permalink**) 03-03-03, 13:17

You have to create a SEQUENCE:

CREATE SEQUENCE myseq;

Then you can use the sequence in insert statements like:

INSERT INTO t (id) VALUES ( myseq.NEXTVAL );

Or of course you can create a trigger to set :NEW.id := myseq.NEXTVAL

rhs98 · #3 (**permalink**) 03-03-03, 13:34

yeah I was just looking squences. Would this be the correct syntax for a trigger???

Code:

CREATE TRIGGER mk_pk BEFORE INSERT ON blah
FOR EACH ROW
BEGIN
:NEW.code_name := myseq.NEXTVAL ;
END;

assuming the trigger was created ok.

also, if i just do

Code:

CREATE SEQUENCE myseq;

what kind of squence will this give me? It doesn't really matter what numbers; but what datatype do the fields in the table (the primary key) need to be? INT?

andrewst · #4 (**permalink**) 03-03-03, 15:24

Yes, your trigger is correct.

Sequences always return integer values - by default, starting from 1 and incrementing by 1 (though there may be gaps). You can modify the starting and increment values if you need to. The column in the table should just be a NUMBER, or you can specify NUMBER(size,0) if you like, as long as size is big enough.

rhs98 · #5 (**permalink**) 03-03-03, 17:13

I don't mind gaps...the users will just have to cope

Thanks! Will give it all a try in the morning...

Internet Services

Web Development

Digital Marketing

Consumer Tech