AUTOINCREMENT and IDENTITY - Sybase SQL Anywhere to Oracle Migration

DEFAULT AUTOINCREMENT and IDENTITY properties allow you to automatically generate unique integer numbers (IDs, sequences) for a column in Sybase ASA.

Quick Example:

   -- Define a table with DEFAULT AUTOINCREMENT (id starts at 1)
   CREATE TABLE cities
   (
      id INTEGER DEFAULT AUTOINCREMENT, 
      name VARCHAR(90)
   );
 
   -- Insert a row, ID will be automatically generated
   INSERT INTO cities (name) VALUES ('San Francisco');
 
   -- Retrieve generated ID
   SELECT @@IDENTITY; 
   -- Returns: 1

DEFAULT AUTOINCREMENT and IDENTITY are identical in Sybase ASA.

For more information, see AUTOINCREMENT and IDENTITY in Sybase SQL Anywhere.

Oracle does not support identity (autoincrement) columns, but you can use a sequence object to generate IDs:

Oracle:

  -- Create a sequence in Oracle
  CREATE SEQUENCE cities_id_seq START WITH 1 INCREMENT BY 1;

Conversion summary:

	Sybase SQL Anywhere AUTOINCREMENT	Oracle SEQUENCE
Start Value	Always 1
Increment	Always 1
Defined in CREATE TABLE		Trigger or explicit insert required
Explicit ID Insert
Multiple ID per Table
Retrieve Last ID	@@IDENTITY	seq_name.CURRVAL

Last Update: Sybase SQL Anywhere 12.0 and Oracle 11g

But Oracle does not allow getting the next ID in the DEFAULT clause:

Oracle:

   -- Try to get next ID in DEFAULT clause in Oracle
   CREATE TABLE cities
   (
      id INTEGER DEFAULT cities_id_seq.NEXTVAL, 
      name VARCHAR2(90)
   );
   -- ERROR at line 3:
   -- ORA-00984: column not allowed here

So you have to define a trigger if you want to generate IDs by default:

Oracle:

   -- Create table without DEFAULT clause
   CREATE TABLE cities
   (
      id INTEGER, 
      name VARCHAR2(90)
   );
 
   -- Define a trigger to generate ID using sequence
   CREATE OR REPLACE TRIGGER cities_seq_tr BEFORE INSERT ON cities 
     FOR EACH ROW
   WHEN (NEW.id IS NULL)
   BEGIN
    SELECT cities_id_seq.NEXTVAL INTO :NEW.id FROM dual;
   END;
   /

Then if you omit the column in INSERT or specify DEFAULT keyword, an id value will be automatically assigned:

Oracle:

  -- Omit id column in INSERT
  INSERT INTO cities (name) VALUES ('San Francisco');
 
  -- Specify DEFAULT keyword
  INSERT INTO cities VALUES (DEFAULT, 'New York');

Without a trigger, you have to explicitly get the next ID value in INSERT statement using NEXTVAL function:

Oracle:

  -- Reference sequence object explicitly to get next ID
  INSERT INTO cities VALUES (cities_id_seq.NEXTVAL, 'Boston');

Table content:

To obtain the most recent ID assigned in the current session, you can use CURRVAL function:

Oracle:

  -- Get the last ID
  SELECT cities_id_seq.CURRVAL FROM dual;
  -- Result: 3

Converting AUTOINCREMENT and IDENTITY from Sybase SQL Anywhere to Oracle.

Sybase SQL Anywhere:

   -- Define a table
   CREATE TABLE cities
   (
      id INTEGER DEFAULT AUTOINCREMENT, 
      name VARCHAR(90)
   );
 
   -- Insert a row, ID will be automatically generated
   INSERT INTO cities (name) VALUES ('San Francisco');
 
   -- Retrieve generated ID
   SELECT @@IDENTITY; 
   -- Returns: 1

Oracle:

   -- Define a table
   CREATE TABLE cities
   (
      id INTEGER, 
      name VARCHAR2(90)
   );
 
   -- Implement auto-increment using sequence and trigger
   CREATE SEQUENCE cities_id_seq START WITH 1 INCREMENT BY 1;
 
   CREATE OR REPLACE TRIGGER cities_seq_tr BEFORE INSERT ON cities 
     FOR EACH ROW
   WHEN (NEW.id IS NULL)
   BEGIN
    SELECT cities_id_seq.NEXTVAL INTO :NEW.id FROM dual;
   END;
   /
 
   -- Insert a row, ID will be automatically generated
   INSERT INTO cities (name) VALUES ('San Francisco');
 
   -- Retrieve generated ID
   SELECT cities_id_seq.CURRVAL FROM dual; 
   -- Returns: 1

Sybase SQL Anywhere

• Reference and Articles
• AUTOINCREMENT and IDENTITY - Generate IDs

Oracle

• Reference and Articles