Re: SQL Question - Identity columns

On Tue, 21 Nov 2000 22:16:31 +0800, Connor McDonald <connor_mcdonald_at_yahoo.com> wrote:

>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
>--

I guess I haven't gotten enough transactions at once to really notice the difference.

Brian Received on Tue Nov 21 2000 - 18:07:26 CST