SERIAL Columns - Informix to Oracle Migration

In Informix, you can use the SERIAL data type to automatically generates IDs (auto-increment values) for columns.

Informix:

  CREATE TABLE teams
  (
     id SERIAL NOT NULL,
     name VARCHAR(70)
  );
 
  -- Let's insert a few rows
  INSERT INTO teams VALUES (0, 'AC Milan');
  INSERT INTO teams VALUES (0, 'Manchester City');

When 0 value is inserted into a SERIAL column, Informix inserts the next ID (a 32-bit integer value):

Table data

id name
1 AC Milan
2 Manchester City

Using Sequence and Trigger in Oracle

Oracle does not have SERIAL data type or IDENTITY (AUTO_INCREMENT) column property, but you can use a sequence and a trigger to implement this functionality.

Why trigger, not just DEFAULT? Oracle does not allow using a sequence in the column DEFAULT clause.

Oracle:

  CREATE TABLE teams
  (
     id NUMBER(10) NOT NULL,
     name VARCHAR(70)
  );
 
  CREATE SEQUENCE teams_seq START WITH 3 INCREMENT BY 1;
 
  // Trigger to automatically assign ID values
  CREATE OR REPLACE TRIGGER teams_seq_tr
    BEFORE INSERT ON teams FOR EACH ROW
  WHEN (NEW.id IS NULL OR NEW.id = 0)
  BEGIN
    SELECT teams_seq.NEXTVAL INTO :NEW.id FROM dual;
  END;
  /

Note that we started the Oracle sequence with 3 taking into account existing data migrated from Informix. See Get Next SERIAL Value to find out how to define the next ID in Informix.

Database and SQL Migration Tools

About SQLines

SQLines offers services and tools to help you migrate databases and applications. For more information, please contact us at support@sqlines.com.

Written by Dmitry Tolpeko, dmtolpeko@sqlines.com - February 2013.

You could leave a comment if you were logged in.