MySQL - 在创建表时一起使用“PRIMARY KEY”、“UNIQUE KEY”和“KEY”的含义
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10908561/
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 - Meaning of "PRIMARY KEY", "UNIQUE KEY" and "KEY" when used together while creating a table
提问by sura2k
Can anyone explain about the purpose of PRIMARY KEY, UNIQUE KEYand KEY, if it is put together in a single CREATE TABLEstatement in MySQL?
任何人都可以解释PRIMARY KEY, UNIQUE KEYand的目的KEY，如果它CREATE TABLE在 MySQL 中放在一个单独的语句中？
CREATE TABLE IF NOT EXISTS `tmp` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`uid` varchar(255) NOT NULL,
`name` varchar(255) NOT NULL,
`tag` int(1) NOT NULL DEFAULT '0',
`description` varchar(255),
PRIMARY KEY (`id`),
UNIQUE KEY `uid` (`uid`),
KEY `name` (`name`),
KEY `tag` (`tag`)
) ENGINE=InnoDB AUTO_INCREMENT=1 ;
How do I convert this query to MSSQL?
如何将此查询转换为 MSSQL？
回答by Corbin
A key is just a normal index. A way over simplification is to think of it like a card catalog at a library. It points MySQL in the right direction.
键只是一个普通的索引。一种过度简化的方法是将其视为图书馆中的卡片目录。它为 MySQL 指明了正确的方向。
A unique key is also used for improved searching speed, but it has the constraint that there can be no duplicated items (there are no two x and y where x is not y and x == y).
唯一键也用于提高搜索速度，但它具有不能有重复项的限制（没有两个 x 和 y，其中 x 不是 y 且 x == y）。
The manualexplains it as follows:
该手册如下解释它：
A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. This constraint does not apply to NULL values except for the BDB storage engine. For other engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL. If you specify a prefix value for a column in a UNIQUE index, the column values must be unique within the prefix.
UNIQUE 索引创建一个约束，使得索引中的所有值都必须是不同的。如果您尝试添加具有与现有行匹配的键值的新行，则会发生错误。此约束不适用于 NULL 值，BDB 存储引擎除外。对于其他引擎，UNIQUE 索引允许包含 NULL 的列有多个 NULL 值。如果为 UNIQUE 索引中的列指定前缀值，则列值在前缀内必须是唯一的。
A primary key is a 'special' unique key. It basically is a unique key, except that it's used to identify something.
主键是一个“特殊”的唯一键。它基本上是一个唯一的键，只是它用于识别某些东西。
The manual explains how indexes are used in general: here.
该手册解释了索引的一般使用方式：here。
In MSSQL, the concepts are similar. There are indexes, unique constraints and primary keys.
在 MSSQL 中，概念是相似的。有索引、唯一约束和主键。
Untested, but I believe the MSSQL equivalent is:
未经测试，但我相信 MSSQL 等效项是：
CREATE TABLE tmp (
id int NOT NULL PRIMARY KEY IDENTITY,
uid varchar(255) NOT NULL CONSTRAINT uid_unique UNIQUE,
name varchar(255) NOT NULL,
tag int NOT NULL DEFAULT 0,
description varchar(255),
);
CREATE INDEX idx_name ON tmp (name);
CREATE INDEX idx_tag ON tmp (tag);
Edit: the code above is tested to be correct; however, I suspect that there's a much better syntax for doing it. Been a while since I've used SQL server, and apparently I've forgotten quite a bit :).
编辑：上面的代码经过测试是正确的；但是，我怀疑这样做有更好的语法。自从我使用 SQL 服务器以来已经有一段时间了，显然我已经忘记了很多 :)。
回答by Paul Bellora
Just to add to the other answers, the documentationgives this explanation:
只是为了添加其他答案，文档给出了以下解释：
KEYis normally a synonym forINDEX. The key attributePRIMARY KEYcan also be specified as justKEYwhen given in a column definition. This was implemented for compatibility with other database systems.A
UNIQUEindex creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines, aUNIQUEindex permits multipleNULLvalues for columns that can containNULL.A
PRIMARY KEYis a unique index where all key columns must be defined asNOT NULL. If they are not explicitly declared asNOT NULL, MySQL declares them so implicitly (and silently). A table can have only onePRIMARY KEY. The name of aPRIMARY KEYis alwaysPRIMARY, which thus cannot be used as the name for any other kind of index.
KEY通常是 的同义词INDEX。key 属性PRIMARY KEY也可以KEY在列定义中指定时指定。这是为了与其他数据库系统兼容而实施的。一个
UNIQUE索引创建的约束，使得该指数的所有值必须是不同的。如果您尝试添加具有与现有行匹配的键值的新行，则会发生错误。对于所有引擎，UNIQUE索引允许NULL列的多个值可以包含NULL.A
PRIMARY KEY是唯一索引，其中所有键列都必须定义为NOT NULL。如果它们没有显式声明为NOT NULL，MySQL 会隐式地（并且默默地）声明它们。一张桌子只能有一个PRIMARY KEY。a 的名称PRIMARY KEY始终为PRIMARY，因此不能用作任何其他类型索引的名称。
回答by Wazan
MySQL unique and primary keys serve to identify rows. There can be only one Primary key in a table but one or more unique keys. Key is just index.
MySQL 唯一键和主键用于标识行。一张表中只能有一个主键，但有一个或多个唯一键。键只是索引。
for more details you can check http://www.geeksww.com/tutorials/database_management_systems/mysql/tips_and_tricks/mysql_primary_key_vs_unique_key_constraints.php
to convert mysql to mssql try this and see http://gathadams.com/2008/02/07/convert-mysql-to-ms-sql-server/
将 mysql 转换为 mssql 试试这个，看看http://gathadams.com/2008/02/07/convert-mysql-to-ms-sql-server/
