MySQL：在 CREATE TABLE 语句中命名主键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3303798/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL: Name primary key in CREATE TABLE statement
提问by searbe
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?
例如在这里我试图创建一个名为“id”的主键，但这是无效的 SQL。你能告诉我正确的方法吗？
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.
我想指定主键的名称 - 而不是“PRIMARY”的默认名称，我希望它被称为“id”或“primary_id”，所以如果我稍后运行 SHOW INDEXES FROM default_test ，Key_name 将是我指定的内容。
回答by Jason McCreary
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`)
回答by Jorge Ferreira
http://dev.mysql.com/doc/refman/5.1/en/create-table.html
http://dev.mysql.com/doc/refman/5.1/en/create-table.html
[...] In MySQL, the name of a PRIMARY KEY is PRIMARY. [...]
[...] 在 MySQL 中，PRIMARY KEY 的名称是 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`)
)
回答by nos
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`)
);
回答by Amber
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.
您不必再次指定列名，因为您已经将其指定为当前字段定义的一部分 - 只需说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`)
)
