| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: ok, no ROWID's as ID, what should i use instead? :-)
On Fri, 28 Jul 2000 01:06:17 -0800, you wrote:
>Thanx for all the info about ROWID's. I still got the following =
questions:
>* In many examples, people use NUMBER(38,0) for every ID. Is there a
>certain speedup to use such large ID?=20
Not that I'm aware of. I worked on a system a year or so back where we used NUMBER(9,0) for ID fields. The key thing is to make sure your field is large enough to accomodate the largest ID that you expect to ever have.=20
>* ROWID is like an autonumber in access: it automagically increases when=
you
>insert a row in table. But ROWID's can't be used as a foreign key, so i
>can't use that. Is there something else in oracle that automatically
>increases and that can be used as index?
You can use what in Oracle is called a SEQUENCE. It's not as easy as with Access, because you have to write triggers to set your IDs to the sequence numbers. Someday I'm going to write an article about this. The question gets asked a lot. Briefly, you want to do something like this:
CREATE SEQUENCE winecolor_seq
START WITH 1
MAXVALUE 9999;
CREATE TABLE winecolor (
ID NUMBER (4,0),
COLOR VARCHAR2(10));
CREATE OR REPLACE TRIGGER winecolor_insert
BEFORE INSERT ON winecolor
=46OR EACH ROW=20
DECLARE
next_winecolor_seq NUMBER;
BEGIN
--Get the next sequence
SELECT winecolor_seq.NEXTVAL=20
INTO next_winecolor_seq
FROM dual;
:new.ID :=3D next_winecolor_seq;
END;
/
Then you can insert rows and the ID will be set automatically from the sequence. For example:
SQL> insert into winecolor (color) values ('red');
1 row created.
SQL> insert into winecolor (id,color) values (999,'blue');
1 row created.
SQL> select * from winecolor;
ID COLOR
---------- ----------
2 red
3 blue
Note that even though the second insert specified an ID number, it was overridden by the value from the sequence.
Hope this helps. One other thing to consider when using a solution like this is whether you want updates to be able to change an ID number. It's possible to write an update trigger to prevent that from ever happening.=20
Jonathan
 |
 |