Link to home
Start Free TrialLog in
Avatar of anusha
anusha

asked on

Oracle - PK field auto increment

Hello everyone,

I am new to Oracle and I read that to implement auto increment on a field such needs to be done.

CREATE SEQUENCE emp_seq
start with 1
increament by 1
nomaxvalue
nocycle
nocache
;

then use:

insert into emp (empno, ename, sal, deptno)
values (seq_emp.nextval, 'Oracle Guru', 2300, 10)

OR create a trigger to do this.

now the question is do I have to create several emp_seq's?? because I have several tables that needs the auto increment. I understand how the sequence and trigger is used as a work around here. but are there any other options available? I am running Oracle 9i (9.2)

Avatar of Xavi23
Xavi23

the value is important?
if not, you can only create a one sequence and then use this sequence for every table (the value is unique)
Well, that's pretty much up to you... you could just have one sequence that multiple tables refer to... but, depending on your needs you might want to have a sequence per table, that's my approach anyway...

no matter what you end up doing... caching w/ sequences is a GOOD idea though depending on how often you will be popping values off of it... if it's quite frequently you certainly do NOT want nocache... because then the database has to do some work in the background for you (updating the sequence information in the data dictionary etc..) EVERY TIME you hit the sequence.. caching helps you scale a little bit more ya know?

-jim



 
Avatar of anusha

ASKER

what are my consequences of using 1 sequence for all tables ?
I have about 10 tables that will need auto increment and the values of the ID are important. They are my key fields.

I would like to take your advice on the caching issue

How should I write the sequence then?

CREATE SEQUENCE emp_seq
start with 1
increament by 1
nomaxvalue
nocycle
;
ASKER CERTIFIED SOLUTION
Avatar of legendcain
legendcain

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
... sorry, forgot to address the "important" question...

if the values of the ID are important meaning that they're your keys and you're only concerned about uniqueness.. that's fine... sequences will only return unique values of course...

the only thing you'll end up w/ in that case would be table EMP might have ID 1, 2 ... another table might have ID 3, 4 ... and so on and so forth...

as long as that's fine w/ your application then it's not a problem...

even with a sequence per table.. you'll more than likely run into that scenario w/ caching... say you set your cache on EMP_SEQ to 100 and in one day you only create 50 EMP's... then that nite you restart your DB... 51-100 will be missing because those values were cached and "lost" when you restarted... no biggee tho... you'll have a hard time trying to exhaust a sequence :)

-jim
... in a toned down environment you'll probably not notice having only one sequence... but, it just makes sense if you think about it.. you have a shared resource... the more tables that share that resource... the higher the point of contention... with one sequence you're lessening the stress on that one sequence by putting more stress on other sequences... i guess i just don't like putting all my eggs in one basket as they say.. ;)

-jim
i think that in your case you should use a sequence for each table, it makes more sence for the application.
hi

i assume that each table has different functional purposes ie for eg. one table is for storing Purchase order, one table for receipt documents, one for issue documents like wise

if above is the case then u can have a document key table with different document types and their start value, last value, end value. have a pl/sql block which will fetch the last value from the document key table and increment it by one and inserting the incremented value to the table for every document table.

if above mentioned is not the scenario, and ur requirement is only to just have a id then i think having one sequence no for each table will be a better option with caching for each sequence based on the no of transactions for each table.

regards
annamalai

annamalai77 => <"if above is the case then u can have a document key table with different document types and their start value, last value, end value. have a pl/sql block which will fetch the last value from the document key table and increment it by one and inserting the incremented value to the table for every document table.">

why doing this?? why implementing this thing when he can use sequences???
hi

well my friend odedmaimon i gave him 2 options one as u said, and the other one by sequences. its a better way to do it, by having a document key value table. then having a sequence. having sequence will put extra load on the server thats why.

regards
annamalai
Hi

The best practise would be to introduce a new table called Document_numbers and a function to get the Document Number for different table based on the Document Type. This way you can generate two types of Primary key Numbers one is for the transactions based on the Financial Month & Year like 2004040001,2004040002...  and other one is for just the number count 1,2,3... so on

Create table document_numbers
(
DNU_DOCTYPE                   VARCHAR2(10) NOT NULL,
DNU_DOCNAME                  VARCHAR2(30) NOT NULL,
DNU_FINYEAR                    NUMBER(8),
DNU_LASTDOCNO              NUMBER(10) NOT NULL
);


and the function goes like this

FUNCTION FN_GET_DOCNO(P_DOCTYPE IN VARCHAR2,P_DOCNAME IN VARCHAR2,P_TRANTYPE IN VARCHAR2 )
RETURN VARCHAR2 IS
CURSOR C_MAS IS SELECT NVL(DON_LASTNO,0)+1 LASTNO FROM DOCUMENT_NUMBERS
                WHERE DON_TYPE=P_DOCTYPE FOR UPDATE OF DON_LASTNO NOWAIT;
CURSOR C_TRAN IS SELECT NVL(DON_LASTNO,0)+1 LASTNO FROM DOCUMENT_NUMBERS
                WHERE DON_TYPE=P_DOCTYPE AND DON_DOCYYYYMM = TO_CHAR(SYSDATE,'YYYYMM')
                FOR UPDATE OF DON_LASTNO NOWAIT;
TMP_VAL NUMBER:=0;
BEGIN
IF P_TRANTYPE='M' THEN
    OPEN C_MAS;
    FETCH C_MAS INTO TMP_VAL;
    IF C_MAS%FOUND THEN
      UPDATE DOCUMENT_NUMBERS
      SET DON_LASTNO=TMP_VAL WHERE CURRENT OF C_MAS;
      RETURN TMP_VAL ;
    ELSE
       INSERT INTO DOCUMENT_NUMBERS
       VALUES  (P_DOCTYPE,  P_DOCNAME,NULL,1);
        RETURN 1;
    END IF;  
    CLOSE C_MAS;
ELSIF P_TRANTYPE='T' THEN
    OPEN C_TRAN;
    FETCH C_TRAN INTO TMP_VAL;
    IF C_TRAN%FOUND THEN
      UPDATE DOCUMENT_NUMBERS
      SET DON_LASTNO=TMP_VAL WHERE CURRENT OF C_TRAN;
      RETURN TO_CHAR(SYSDATE,'YYYYMM') || lpad(TMP_VAL,5,'00000') ;
    ELSE      
       INSERT INTO DOCUMENT_NUMBERS
        VALUES  (P_DOCTYPE, P_DOCNAME,TO_CHAR(SYSDATE,'YYYYMM'),1);
        RETURN TO_CHAR(SYSDATE,'YYYYMM') || lpad(1,5,'00000');
    END IF;  
    CLOSE C_TRAN;
END IF;
EXCEPTION
WHEN OTHERS THEN
 RAISE_APPLICATION_ERROR('-20001',SQLERRM);
END;

now for any kind of operation that you need to generate number based on the Financial Year & month You can do so by calling the function

Fn_GET_DOCNO with the required parameter. The Function will return the Unique Number for any type of Insert.

example:
insert into invoice_header (invno,invdt) values (FN_GET_DOCNO('INV','INVOICE HEADER','T'),sysdate)


Any more queries welcome

Rgds
Sara