Re: SQL Question - Identity columns

Hi,

Further to the last post, you can see sequence characteristics using the dba_sequences,user_sequences or all_sequences view because a sequence is a distinct object and is recorded in the data dictionary.

Also, similar to the @@IDENTITY variable in SQL Server, a sequence can be shared among multiple tables..

Hth,

Steve

In article <3a19ca7d.1740640_at_news.alt.net>,   SPAMBLOCK.Maxwell_Smart_at_ThePentagon.com.SPAMBLOCK (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
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Nov 21 2000 - 07:24:40 CST