Re: Auto Increment Primary key?

From: Peter Wilk <PeterWilk_at_compuserve.com>
Date: Sun, 2 Jan 2000 20:33:05 +0100
Message-ID: <84o8rl$hj3$1@ssauraab-i-1.production.compuserve.com>

hello,
  I want to simulate an AUTOINC field in Oracle with a sequence number using the following code - however I always get a compile-error for the trigger. can anybody help me with that ?
thanks a lot - peter

DROP TABLE SALEADMIN.TEST CASCADE CONSTRAINTS; CREATE TABLE SALEADMIN.TEST
   (

   TESTID               NUMBER(8,0)       NOT NULL,
      CONSTRAINT PK_SUBNUMBERROAD PRIMARY KEY(TESTID),
   TEXT             VARCHAR2(25)      NOT NULL

   )
   TABLESPACE SALEDATA; DROP SEQUENCE SALEADMIN.SEQ_TEST;
CREATE SEQUENCE SALEADMIN.SEQ_TEST

       INCREMENT BY 1 START WITH 1
       MAXVALUE 99999999 MINVALUE 1
       CYCLE CACHE 20 ORDER;

CREATE OR REPLACE TRIGGER SALEADMIN.TRIG_TESTID    BEFORE INSERT ON SALEADMIN.TEST
   BEGIN
      SALEADMIN.TEST.TESTID = SALEADMIN.SEQ_TEST.NEXTVAL;    END; rspeaker_at_my-deja.com schrieb in Nachricht <83o40t$qhe$1_at_nnrp1.deja.com>...
>you can use the CREATE SEQUENCE command to create a sequence. Once
>created, you can reference the pseudo-columns NEXTVAL and CURRVAL in
>your select/insert statements. Then, make your sequence column the
>PK for the table. For example, to insert a record using the next
>available number you would do something like
>INSERT INTO TABLE T VALUES (sequence.NEXTVAL, val2, val3, ...);
>
>Keep in mind that dropped values in the sequence do not automatically
>get reassigned so you may have holes in your sequence. There are
>additional parameters to the CREATE SEQUENCE command to handle things
>such as the starting value, the increment value, the max value, and
>whether or not to recycle the sequence when you reach the max value.
>
>HTH,
>Roy
>
>
>In article
><Pine.LNX.4.10.9912171604300.29563-100000_at_alfred.laffeycomputer.com>,
> John Shaft <shaft_at_meanmutha.com> wrote:
>> Hi,
>>
>> Does Oracle has a way to define a table such that the primary key gets
>> auto-incremented with each new insertion? If not, how do you keep your
>> primary keys unique? (With a store value read and incremented in a
>> transaction?)
>>
>> I have a databse devloped orignally for MySQL and want to port it to
>> Oracle 8. I wondered about this issue.
>>
>> Thanks!
>>
>> shaft_at_meanmutha.com
>> http://www.meanmutha.com
>>
>>
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
Received on Sun Jan 02 2000 - 13:33:05 CST