Re: SQL Question - Identity columns

Brian Tkatch wrote:
>
> On Mon, 20 Nov 2000 18:50:20 -0600, Frank LaRosa
> <frank_at_franklarosa.com> wrote:
>
> >Hello,
> >
> >I'm new to Oracle but I have several years of experience with Microsoft
> >SQL Server.
> >
> >In SQL Server there is a column type called IDENTITY. Basically this is
> >a numeric column that is automatically assigned the next highest number
> >each time a row is inserted into the table (i.e. first column gets a 1,
> >next column gets a 2, etc). I use it extensively for creating primary
> >keys.
> >
> >Does Oracle's SQL language have the equivalent? If not, what's the best
> >way to simulate the same behavior?
> >
> >Thanks.
> >
>
> Oralce does not have an equivalent. However, you can use a simple
> method.
>
> CREATE TABLE MyTable (
> Id NUMBER(8),
> CONSTRAINT MT1_Id_NOT_NULL CHECK (Id IS NOT NULL),
> CONSTRAINT MT1_Id_PK PRIMARY KEY (Id)
> );
>
> Oracle has an object known as a sequence. You can create one
>
> CREATE SEQUENCE MySEQ INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCACHE
> NOCYCLE;
>
> Then use a trigger to pull the next value from the sequence:
>
> CREATE OR REPLACE TRIGGER MyTableANR
> BEFORE INSERT ON MyTable
> FOR EACH ROW
> BEGIN
> SELECT MySEQ.NEXTVAL INTO :NEW.Id From Dual;
> ENDMyTableANR;
> /
>
> This will insert the current value from the sequence and move the
> sequence's pointer ahead by one on every insert. You can still update
> the column in the table and the sequence will not be triggered.
>
> Brian

...although I would strongly recommend against the NOCACHE option on performance grounds...

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk (mirrored at
http://www.oradba.freeserve.co.uk)

(Apologies for not replying to any emails - YAHOO have a dead server)

"Early to bed and early to rise, 
 makes a man healthy, wealthy and wise." - some dead guy