IDENTITY to AUTO_INCREMENT Issues - Sybase ASE to MariaDB Migration

In Sybase ASE you can use IDENTITY column property to automatically generate unique values for the column, for example, to generate IDs.

In MariaDB you can use AUTO_INCREMENT column property but there are some issues that you have to take into account during the conversion from Sybase to MariaDB.

NUMERIC(n, 0) Data Type for Identity in Sybase ASE

Besides integer data types, Sybase ASE allows you to use the NUMERIC data type (note that DECIMAL is not allowed) with scale 0 as identity column:

Sybase ASE:

  CREATE TABLE sales_daily
   ( 
      sale_id NUMERIC(5,0) IDENTITY, 
      stor_id CHAR(4)
   )
   -- Ok

MariaDB allows using integer data types only:

MariaDB:

  CREATE TABLE sales_daily
   ( 
      sale_id NUMERIC(5,0) AUTO_INCREMENT, 
      stor_id CHAR(4)
   );
   -- ERROR 1063 (42000): Incorrect column specifier for column 'sale_id'

KEY is Required in MariaDB

When you change the data type from NUMERIC to an integer (INTEGER or BIGINTEGER i.e) you can get another error:

MariaDB:

  CREATE TABLE sales_daily
   ( 
      sale_id INTEGER AUTO_INCREMENT, 
      stor_id CHAR(4)
   );
   -- ERROR 1063 (42000): Incorrect column specifier for column 'sale_id'

MariaDB requires to specify a key in the CREATE TABLE statement explicitly:

MariaDB:

  CREATE TABLE sales_daily
   ( 
      sale_id INTEGER AUTO_INCREMENT PRIMARY KEY, 
      stor_id CHAR(4)
   );
   -- OK

For more information, see Sybase ASE to MariaDB Migration.