Re: SQL Question - Identity columns

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 Received on Mon Nov 20 2000 - 19:10:54 CST