MySQL: Name primary key in CREATE TABLE statement

Question

How do I set the name of a primary key when creating a table?

For example here I'm trying to create a primary key with the name 'id', but this is invalid SQL. Can you tell me the correct way to do this?

CREATE TABLE IF NOT EXISTS `default_test`
(
    `default_test`.`id` SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY `id`,
    `default_test`.`name` LONGTEXT NOT NULL
)

Clarification

I'd like to specify the name of the primary key - rather than the default name of "PRIMARY" I'd like it to be called "id" or perhaps "primary_id", so if I were to later run SHOW INDEXES FROM default_test, the Key_name will be something I have specified.

Answer 1

You don't have to specify the column name again, because you already specified it as part of the current field definition - just say PRIMARY KEY.

CREATE TABLE IF NOT EXISTS `default_test` (
    `id` SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `name` LONGTEXT NOT NULL
)

Alternatively, you can specify it separately later:

CREATE TABLE IF NOT EXISTS `default_test` (
    `id` SMALLINT NOT NULL AUTO_INCREMENT,
    `name` LONGTEXT NOT NULL,
    PRIMARY KEY(`id`)
)

Answer 2

Alternatively and more widely supported:

CREATE TABLE IF NOT EXISTS `default_test` (
 `default_test`.`id` SMALLINT NOT NULL AUTO_INCREMENT,
 `default_test`.`name` LONGTEXT NOT NULL,
 PRIMARY KEY (`id`)
)

UPDATE

Based on the clarification, you could replace the last definition above with the following if you are to specify the index name:

CONSTRAINT `pk_id` PRIMARY KEY (`id`)

Answer 3

You shouldn't specify the column name when you specify the primary key column name directly inline with the column definition, so:

CREATE TABLE IF NOT EXISTS `default_test` ( 
 `default_test`.`id` SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY  , 
 `default_test`.`name` LONGTEXT NOT NULL 
 );

Alternativly you could do:

  CREATE TABLE IF NOT EXISTS `default_test` ( 
   `default_test`.`id` SMALLINT NOT NULL AUTO_INCREMENT , 
   `default_test`.`name` LONGTEXT NOT NULL ,
    PRIMARY KEY `default_test_id_pkey` (`id`)
   );

Answer 4

http://dev.mysql.com/doc/refman/5.1/en/create-table.html

[...] In MySQL, the name of a PRIMARY KEY is PRIMARY. [...]

CREATE TABLE IF NOT EXISTS `default_test` (
    `default_test`.`id` SMALLINT NOT NULL AUTO_INCREMENT,
    `default_test`.`name` LONGTEXT NOT NULL,
    PRIMARY KEY (`id`)
)

Answer 5

You can see the primary key in MySQL as a special type of an UNIQUE index. You can't change the name of the PRIMARY index however.