what is identity equivalent key word in oracle

I have .sql scripts generated from sybase for creating table structure and trying to run on oracle  client toad after changing the datatypes from datetime to date etc.Toad is not recognizing identity (auto increment number column ).It is giving error when i create table saying right parenthesis missing as in the screenshot.
identity.jpg
LVL 7
gudii9Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
hqassapConnect With a Mentor Commented:
Hi,
In oracle things get weird:

CREATE TABLE comparison_axis (ca_id NUMBER,      -- Remove the (identity) word
ca_vers CHAR(1) NOT NULL,
...
...
...
insert_userid VARCHAR(25),
CONSTRAINT pk_comparison_axis PRIMARY KEY (ca_id)    -- Remove the two hyphens (this is the replacement of the identity
)

-- Then create a sequence to auto generate the identifiers:

CREATE SEQUENCE seq_ca_id
     MINVALUE 1
     MAXVALUE 999999999999999999
     START WITH 1
     INCREMENT BY 1
     NOCACHE;
      
-- And Finally create a triggere before insert on the record level to force the sequencing:

CREATE OR REPLACE TRIGGER trg_ca_insert
   BEFORE INSERT
   ON comparison_axis
   FOR EACH ROW
BEGIN
   IF :NEW.ca_id IS NULL
   THEN
      SELECT seq_ca_id.NEXTVAL
        INTO :NEW.ca_id
        FROM DUAL;
   END IF;
END;
/
0
 
WikkardConnect With a Mentor Commented:
I don't think oracle has the same notion of identity (autonumber) columns as SQL server or sybase.  

It uses sequences which you need to create as a seperate (sequence) database object and then create a trigger on insert for the table which needs the identity column to update the id.

This article explains it http://www.orafaq.com/faq/how_does_one_implement_autonumber_and_identity_columns_in_oracle
0
 
gudii9Author Commented:
thaank you very much
0
All Courses

From novice to tech pro — start learning today.