IDENTITY Columns - SQL Server to MariaDB Migration

In SQL Server, the IDENTITY column property allows you to automatically assign incremental integer values to a column. In MariaDB, you can use the AUTO_INCREMENT property:

SQL Server:

  -- Table with identity values starting at 1 and incrementing by 1 by default
  CREATE TABLE colors 
  (
     id INT IDENTITY,
     name VARCHAR(10)
  );
 
  -- Insert a few sample rows
  INSERT INTO colors (name) VALUES ('Blue');
  INSERT INTO colors (name) VALUES ('White');
  INSERT INTO colors (name) VALUES ('Green');
 
  -- id values were automatically assigned
  SELECT * FROM colors;  
  #  1    Blue    
  #  2    White  
  #  3    Green

MariaDB:

  -- Table with auto-increment values starting at 1 and incrementing by 1
  CREATE TABLE colors 
  (
     id INT AUTO_INCREMENT PRIMARY KEY,
     name VARCHAR(10)
  );
 
  -- Insert a few sample rows
  INSERT INTO colors (name) VALUES ('Blue');
  INSERT INTO colors (name) VALUES ('White');
  INSERT INTO colors (name) VALUES ('Green');
 
  -- id values were automatically assigned
  SELECT * FROM colors;  
  #  1    Blue    
  #  2    White  
  #  3    Green

Note that MariaDB requires the auto-increment column to be defined as a key.

SQL Server allows you to define the start value and increment for an IDENTITY column.

SQL Server:

  -- Table with identity values starting at 7 and incrementing by 3
  CREATE TABLE colors 
  (
     id INT IDENTITY(7,3),
     name VARCHAR(10)
  );
 
  -- Insert a few sample rows
  INSERT INTO colors (name) VALUES ('Blue');
  INSERT INTO colors (name) VALUES ('White');
  INSERT INTO colors (name) VALUES ('Green');
 
  -- id values were automatically assigned
  SELECT * FROM colors;  
  #  7      Blue    
  #  10    White  
  #  13    Green

MariaDB allows you to define the start value only while increment is always 1 for an AUTO_INCREMENT column.

MariaDB:

  -- Table with identity values starting at 7 and incrementing by 1 (!)
  CREATE TABLE colors 
  (
     id INT AUTO_INCREMENT PRIMARY KEY,
     name VARCHAR(10)
  ) AUTO_INCREMENT = 7;
 
  -- Insert a few sample rows
  INSERT INTO colors (name) VALUES ('Blue');
  INSERT INTO colors (name) VALUES ('White');
  INSERT INTO colors (name) VALUES ('Green');
 
  -- id values were automatically assigned
  SELECT * FROM colors;  
  #  7     Blue    
  #  8     White  
  #  9     Green

SQL Server allows you to define the IDENTITY property for columns with NUMERIC(n, 0) and DECIMAL(n,0) data types.

SQL Server:

  -- Table with identity values for NUMERIC column
  CREATE TABLE colors 
  (
     id NUMERIC(5,0) IDENTITY,
     name VARCHAR(10)
  );
  /* Commands completed successfully. */

MariaDB allows you to define the AUTO_INCREMENT property for integer columns only.

MariaDB:

  -- Trying to define auto-increment for NUMERIC column
  CREATE TABLE colors 
  (
     id NUMERIC(5,0) AUTO_INCREMENT PRIMARY KEY,
     name VARCHAR(10)
  );
  /* ERROR 1063 (42000): Incorrect column specifier for column 'id' */
 
    -- Trying to define auto-increment for DECIMAL column
  CREATE TABLE colors 
  (
     id DECIMAL(5,0) AUTO_INCREMENT PRIMARY KEY,
     name VARCHAR(10)
  );
  /* ERROR 1063 (42000): Incorrect column specifier for column 'id' */

MariaDB requires that the AUTO_INCREMENT property is defined for a key column.

MariaDB:

  -- Trying to define auto-increment on a non-key column
  CREATE TABLE colors 
  (
     id INT AUTO_INCREMENT,
     name VARCHAR(10)
  );
  /* ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key */

For more information, see SQL Server to MariaDB Migration.