Sybase SQL Anywhere - AUTOINCREMENT and IDENTITY - Generate IDs

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

Sybase SQL Anywhere (Sybase ASA) DEFAULT AUTOINCREMENT and IDENTITY properties:

Syntax	DEFAULT AUTOINCREMENT	IDENTITY
Start Value		Always 1	Use a sequence to set start and increment
Increment		Always 1
How to Generate IDs	Omit the column in INSERT, or specify DEFAULT keyword
Explicit ID Insert
Multiple ID per Table		Use a sequence object
Constraints	NOT NULL	Not added automatically
	Primary or unique key	Not required and not added automatically
Retrieve Last ID	@@IDENTITY	Returns the last ID inserted in the current session
Gaps	If a larger value is explicitly inserted, the ID generator changed to this value + 1
Restart (Reset)	Use sa_reset_identity system procedure
Alternatives	Sequence object	DEFAULT (seq_name.NEXTVAL)
Synonyms	DEFAULT AUTOINCREMENT and IDENTITY are identical

Last Update: Sybase SQL Anywhere 12

DEFAULT AUTOINCREMENT and IDENTITY are identical. IDENTITY is supported for compatibility with Transact-SQL syntax of Sybase Adaptive Server Enterprise (Sybase ASE) and Microsoft SQL Server and implemented as DEFAULT AUTOINCREMENT:

   CREATE TABLE cities
   (
      id INTEGER DEFAULT AUTOINCREMENT, 
      name VARCHAR(90)
   );
 
   -- is equivalent to
   CREATE TABLE cities
   (
      id INTEGER IDENTITY, 
      name VARCHAR(90)
   );

Sybase SQL Anywhere does not automatically add NOT NULL or PRIMARY KEY/UNIQUE constraint, you have to specify them explicitly.

   -- Insert NULL value, ID will not be generated
   INSERT INTO cities VALUES (NULL, 'Null value');
   -- 1 row(s) inserted
 
   -- Insert duplicate ID
   INSERT INTO cities VALUES (1, 'Another value with ID 1');
   -- 1 row(s) inserted

To generate an ID value, you can omit the column in INSERT statement, or specify DEFAULT keyword:

   -- Omit the column in INSERT
   INSERT INTO cities (name) VALUES ('Denver');
 
   -- Specify DEFAULT
   INSERT INTO cities VALUES (DEFAULT, 'Kansas City');

Note that you can insert both NULL and 0 values. Specifying NULL or 0 does not force ID generation as in MySQL:

   INSERT INTO cities VALUES (0, 'Reserved');
   -- 1 row(s) inserted

Table content:

@@IDENTITY variable returns the most recent value inserted by the current connection into an IDENTITY or DEFAULT AUTOINCREMENT column:

   -- Insert a row (id 4 will be assigned)
   INSERT INTO cities (name) VALUES ('Portland');
 
   SELECT @@IDENTITY;
   -- Result: 4

If you insert an ID value explicitly, and this ID is larger than the largest ID value, the explicitly inserted value is used as a starting point for subsequent inserts:

   -- Insert ID 8 explicitly (make a gap)
   INSERT INTO cities VALUES (8, 'London');
 
   -- Continue using ID generator
   INSERT INTO cities (name) VALUES ('Liverpool');  -- ID 9 is assigned

Then if you fill the gap by explicitly inserting ID values smaller than the largest ID value, it has no affect on IDs generated for subsequent inserts:

   -- Fill the gap by inserting ID 5 explicitly
   INSERT INTO cities VALUES (5, 'San Jose');
 
   -- Continue using ID generator
   INSERT INTO cities (name) VALUES ('Manchester');  -- ID 10 is assigned

Table content:

When you delete rows from a table with DEFAULT AUTOINCREMENT or IDENTITY column, the ID generator is not automatically reset to 1:

   -- Delete all rows
   DELETE FROM cities;
 
   -- Insert a new row (ID 11 is assigned)
   INSERT INTO cities (name) VALUES ('Los Angeles');

To reset the identity to the specified value, you can use sa_reset_identity system procedure:

   -- Reset identity to 1 (3rd parameter value + 1)
   CALL sa_reset_identity('cities', 'DBA', 0);
 
   -- ID 1 will be assigned
   INSERT INTO cities (name) VALUES ('Walnut Creek');

Table content:

DEFAULT AUTOINCREMENT and IDENTITY columns in other databases:

Oracle:

Auto-increment or Identity

Can be emulated using a sequence and trigger. More...

SQL Server:

IDENTITY(start, increment)

Start and increment can be specified

IDENTITY(1,1) is default

MySQL:

AUTO_INCREMENT Column Option
Start Value
Increment		Always 1
Generate ID	NULL or 0 force ID generation
Last ID	LAST_INSERT_ID() function
Restrictions	UNIQUE or PRIMARY KEY constraint is required

PostgreSQL:

SERIAL Data Type
Start Value		Always 1	ALTER SEQUENCE to change
Increment		Always 1
NOT NULL	NOT NULL is added automatically
Generate ID	0 does not force ID generation
Last ID	LASTVAL(), CURRVAL('seq_name') and INSERT RETURNING

• Reference and Articles

Sybase SQL Anywhere 12.0 Manual

• CREATE TABLE Statement
• sa_reset_identity System Procedure