Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Auto Increment

Posted on 1999-07-12
6
Medium Priority
?
1,808 Views
Last Modified: 2012-05-04
I need to create a table with an auto-increment column.  e.g.

myTable.idCol

Is this an option and if not, what code do I need in what trigger
0
Comment
Question by:gmoriak
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 2

Accepted Solution

by:
dslavin earned 200 total points
ID: 1087371
The best method is to create a sequence and then populate the column from the sequence.

To create the sequence, execute this command in a SQL*Plus window or equivalent:

create sequence myseq start with 1 increment by 1;

To get the next sequence number in a trigger by something like this:

create or replace trigger
  mytrigger
BEFORE
  INSERT
ON
  mytable
FOR EACH ROW

BEGIN
  select
     myseq.nextval
  into
     :new.mycolumn
  from
     dual
  ;
END;

Where mytable.mycolumn is the column you want to increment.
0
 
LVL 3

Author Comment

by:gmoriak
ID: 1087372
Please bear with me.  I know Microsoft SQL Server inside and out, but I'm an Oracle novice.

How do you create a sequence mySeq?

What if I need to do this for many tables?

And there isn't an autoIncrement type option?
0
 
LVL 3

Author Comment

by:gmoriak
ID: 1087373
This didn't work - ti_users is invalid and failed re-validation

create trigger ti_users
  BEFORE INSERT
  on Users
  REFERENCING OLD AS preInsert NEW AS postInsert
  for each row
/* ERwin Builtin Tue Jul 13 10:07:12 1999 */
/* default body for ti_users */

BEGIN
  select seqUsers.nextval into :postInsert.UserId from dual;
END;

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 2

Expert Comment

by:dslavin
ID: 1087374
>>How do you create a sequence mySeq?

create sequence myseq start with 1 increment by 1;

If you create this sequence as one user and try to access it as another, then you might have to create a synonym for this sequence and also grant permissions to users (or public) so that they can all see and increment the sequence generator, like this:

create public synonym myseq for myseq;
grant select on myseq to public;

This sequence can be used for more than one table, or you can create a sequence for each and every table.  You can start at any number and increment by any number.

I don't know of any auto-increment function.  The problem with such a method would be if you have multiple people trying to add elements to the same table at the same time.  By splitting out this function and giving it to another object (e.g. a sequence) you minimize the time that the table is locked and you eliminate the possibility that two people will try to generate a record with the same value.

If you don't want to use a sequence generator, then you can do something like this:

create or replace trigger
        mytrigger
      BEFORE
        INSERT
      ON
        mytable
      FOR EACH ROW

      DECLARE
        maxnum  NUMBER;

      BEGIN
        select
           max(mycolumn)
        into
           maxnum
        from
           mytable
        ;
        :new.mycolumn := maxnum + 1;
      END;

I don't think you need all the REFERENCing stuff.  I've never used it myself.
0
 
LVL 6

Expert Comment

by:mshaikh
ID: 1087375
CREATE SEQUENCE schema.mySeq
INCREMENT BY  1
START WITH 1
NOMAXVALUE
NOCYCLE

The above statement creates a sequence called mySeq owned by schema.

Here are some more option you can use in create sequence statement:

schema  
 is the schema to contain the sequence. If you omit schema, Oracle creates the sequence in your own schema.  
 
mySeq  
 is the name of the sequence to be created.  
 
INCREMENT BY  
 specifies the interval between sequence numbers. This integer value can be any positive or negative integer, but it cannot be 0. This value can have 28 or fewer digits. The absolute of this value must be less than the difference of MAXVALUE and MINVALUE. If this value is negative, then the sequence descends. If the increment is positive, then the sequence ascends. If you omit this clause, the interval defaults to 1. See also "Incrementing Sequence Values".  
 
START WITH  
 specifies the first sequence number to be generated. Use this option to start an ascending sequence at a value greater than its minimum or to start a descending sequence at a value less than its maximum. For ascending sequences, the default value is the sequence's minimum value. For descending sequences, the default value is the sequence's maximum value. This integer value can have 28 or fewer digits.  
 
MAXVALUE  
 specifies the maximum value the sequence can generate. This integer value can have 28 or fewer digits. MAXVALUE must be equal to or less than START WITH and must be greater than MINVALUE.  
 
NOMAXVALUE  
 specifies a maximum value of 10^27 for an ascending sequence or -1 for a descending sequence. This is the default.  
 
MINVALUE  
 specifies the sequence's minimum value. This integer value can have 28 or fewer digits. MINVALUE must be less than or equal to START WITH and must be less than MAXVALUE.  
 
NOMINVALUE  
 specifies a minimum value of 1 for an ascending sequence or -(10^26) for a descending sequence. This is the default.  
 
CYCLE  
 specifies that the sequence continues to generate values after reaching either its maximum or minimum value. After an ascending sequence reaches its maximum value, it generates its minimum value. After a descending sequence reaches its minimum, it generates its maximum.  
 
NOCYCLE  
 specifies that the sequence cannot generate more values after reaching its maximum or minimum value. This is the default.  
 
CACHE  
 specifies how many values of the sequence Oracle preallocates and keeps in memory for faster access. This integer value can have 28 or fewer digits. The minimum value for this parameter is 2. For sequences that cycle, this value must be less than the number of values in the cycle. You cannot cache more values than will fit in a given cycle of sequence numbers; thus, the maximum value allowed for CACHE must be less than the value determined by the following formula:  
 
 
 (CEIL (MAXVALUE - MINVALUE)) / ABS (INCREMENT)

NOCACHE  
 specifies that values of the sequence are not preallocated.  
 
If you omit both the CACHE parameter and the NOCACHE option, Oracle caches 20 sequence numbers by default.  
 
ORDER  
 guarantees that sequence numbers are generated in order of request. You may want to use this option if you are using the sequence numbers as timestamps. Guaranteeing order is usually not important for sequences used to generate primary keys.  
 
NOORDER  
 does not guarantee sequence numbers are generated in order of request.  
 
If you omit both the ORDER and NOORDER options, Oracle chooses NOORDER by default. Note that the ORDER option is necessary only to guarantee ordered generation if you are using Oracle with the Parallel Server option in parallel mode. If you are using exclusive mode, sequence numbers are always generated in order
 

0
 
LVL 3

Author Comment

by:gmoriak
ID: 1087376
Thanks!
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

664 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question