PRIMARY KEY Name - SQL Server to MariaDB Migration

In SQL Server, you can specify a name for a primary key constraint in both CREATE TABLE and ALTER TABLE statements.

In MariaDBL, you can also specify a constraint name for a primary key, but it is ignored with a warning, and the name 'PRIMARY' is used instead.

SQL Server:

  -- Table with named primary key
  CREATE TABLE colors 
  (
     id INT,
     name VARCHAR(10),
     CONSTRAINT pk_name PRIMARY KEY(id)
  );
  /* Commands completed successfully. */

MariaDB:

  -- Table with named primary key (name ignored with a warning)
  CREATE TABLE colors 
  (
     id INT,
     name VARCHAR(10),
     CONSTRAINT pk_name PRIMARY KEY(id)
  );
  /* Query OK, 0 rows affected, 1 warning */
 
  SHOW WARNINGS;
  /* Warning | 1280 | Name 'pk_name' ignored for PRIMARY key. */

Note that MariaDB accepts the constraint name but it is ignored:

MariaDB:

  SHOW CREATE TABLE colors;
  /*  
    CREATE TABLE `colors` (
    `id` int NOT NULL,
    `name` varchar(10) DEFAULT NULL,
    PRIMARY KEY (`id`)
   ) ENGINE=InnoDB */
 
   -- Trying to delete the constraint by name
   ALTER TABLE colors DROP CONSTRAINT pk_name;
   /* ERROR 1091 (42000): Can't DROP CONSTRAINT `pk_name`; check that it exists */
 
   -- Use 'PRIMARY' name
   ALTER TABLE colors DROP CONSTRAINT `PRIMARY`;
   /* Query OK, 0 rows affected */
 
  -- No PRIMARY KEY anymore 
   SHOW CREATE TABLE colors;
    /*  
    CREATE TABLE `colors` (
    `id` int NOT NULL,
    `name` varchar(10) DEFAULT NULL
   ) ENGINE=InnoDB */

Then if we add the named constraint using ALTER TABLE statement, its name is still ignored:

MariaDB:

  ALTER TABLE colors ADD CONSTRAINT pk_name PRIMARY KEY(id);
  /* Query OK, 0 rows affected, 1 warning */
  /* Records: 0  Duplicates: 0  Warnings: 1 */
 
  -- Still cannot delete it by this name
  ALTER TABLE colors DROP CONSTRAINT pk_name;
  /* ERROR 1091 (42000): Can't DROP CONSTRAINT `pk_name`; check that it exists */
 
  -- Use 'PRIMARY' name
  ALTER TABLE colors DROP CONSTRAINT `PRIMARY`;
  /* Query OK, 0 rows affected */

For more information, see SQL Server to MariaDB Migration.