PRIMARY KEY Name - SQL Server to MySQL Migration

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

In MySQL, you can also specify a constraint name for a primary key, but it is ignored with no warnings, 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. */

MySQL:

  -- Table with named primary key (name ignored)
  CREATE TABLE colors 
  (
     id INT,
     name VARCHAR(10),
     CONSTRAINT pk_name PRIMARY KEY(id)
  );
  /* Query OK, 0 rows affected */

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

MySQL:

  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 3940 (HY000): Constraint 'pk_name' does not exist. */
 
   -- 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:

MySQL:

  ALTER TABLE colors ADD CONSTRAINT pk_name PRIMARY KEY(id);
  /* Query OK, 0 rows affected */
  /* Records: 0  Duplicates: 0  Warnings: 0 */
 
  -- Still cannot delete it by this name
  ALTER TABLE colors DROP CONSTRAINT pk_name;
  /* ERROR 3940 (HY000): Constraint 'pk_name' does not exist. */
 
  -- Use 'PRIMARY' name
  ALTER TABLE colors DROP CONSTRAINT `PRIMARY`;
  /* Query OK, 0 rows affected */

For more information, see SQL Server to MySQL Migration.