C# SQL Server 中主键和唯一索引的区别
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19020934/
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
Difference between Primary Key and Unique Index in SQL Server
提问by clifford.duke
My company is currently in the process of rewriting an application that we recently acquired. We chose to use ASP.net mvc4 to build this system as well as using the Entity Framework as our ORM. The previous owner of the company we acquired is very adamant that we use their old database and not change anything about it so that clients can use our product concurrently with the old system while we are developing the different modules.
我的公司目前正在重写我们最近获得的应用程序。我们选择使用 ASP.net mvc4 来构建这个系统，并使用实体框架作为我们的 ORM。我们收购的公司的前任所有者非常坚持我们使用他们的旧数据库而不对其进行任何更改，以便客户可以在我们开发不同模块的同时使用我们的产品和旧系统。
I found out that the old table structures does not have a Primary key, rather, it uses a Unique Index to serve as their primary key. Now when using Entity framework I have tried to match their tables in structure but have been unable to do so as the EF generates a Primary key instead of a unique index.
我发现旧的表结构没有主键，而是使用唯一索引作为主键。现在，当使用实体框架时，我尝试在结构上匹配他们的表，但由于 EF 生成主键而不是唯一索引，因此无法这样做。
When I contacted the previous owner, and explained it, he told me that "the Unique key in every table is the Primary Key. They are synonyms to each other."
当我联系之前的所有者并解释时，他告诉我“每个表中的唯一键是主键。它们是彼此的同义词。”
I am still relatively new to database systems so I am not sure if this is correct. Can anyone clarify this?
我对数据库系统还是比较陌生，所以我不确定这是否正确。任何人都可以澄清这一点吗？
his table when dumped to SQL generates:
当转储到 SQL 时，他的表会生成：
-- ----------------------------
-- Indexes structure for table AT_APSRANCD
-- ----------------------------
CREATE UNIQUE INDEX [ac_key] ON [dbo].[AT_APSRANCD]
([AC_Analysis_category] ASC, [AC_ANALYSI_CODE] ASC)
WITH (IGNORE_DUP_KEY = ON)
GO
however my system generates:
但是我的系统生成：
-- ----------------------------
-- Primary Key structure for table AT_APSRANCD
-- ----------------------------
ALTER TABLE [dbo].[AT_APSRANCD] ADD PRIMARY KEY ([AC_Analysis_category])
GO
EDIT:Follow up question to this is how would I go about designing the Models for this? I am only used to using the [Key] annotation which defines it as a primary key, and without it, EF will not generate that table. so something like this:
编辑：对此的后续问题是我将如何为此设计模型？我只习惯使用 [Key] 注释将其定义为主键，没有它，EF 将不会生成该表。所以像这样：
[Table("AT_APSRANCD")]
public class Analysis
{
[Key]
public string AnalysisCode { get; set; }
public string AnalysisCategory { get; set; }
public string ShortName { get; set; }
public string LongName { get; set; }
}
采纳答案by Adriaan Stander
来自SQL 唯一约束
The UNIQUE constraint uniquely identifies each record in a database table.
The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns.
A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.
Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.
UNIQUE 约束唯一标识数据库表中的每条记录。
UNIQUE 和 PRIMARY KEY 约束都为一列或一组列的唯一性提供了保证。
PRIMARY KEY 约束自动定义了一个 UNIQUE 约束。
请注意，每个表可以有多个 UNIQUE 约束，但每个表只能有一个 PRIMARY KEY 约束。
Also, from Create Unique Indexes
此外，从创建唯一索引
You cannot create a unique index on a single column if that column contains NULL in more than one row. Similarly, you cannot create a unique index on multiple columns if the combination of columns contains NULL in more than one row. These are treated as duplicate values for indexing purposes.
如果该列在多行中包含 NULL，则不能在该列上创建唯一索引。同样，如果列组合在多于一行中包含 NULL，则不能在多列上创建唯一索引。出于索引目的，这些被视为重复值。
Whereas from Create Primary Keys
而从创建主键
All columns defined within a PRIMARY KEY constraint must be defined as NOT NULL. If nullability is not specified, all columns participating in a PRIMARY KEY constraint have their nullability set to NOT NULL.
在 PRIMARY KEY 约束中定义的所有列都必须定义为 NOT NULL。如果未指定可空性，则参与 PRIMARY KEY 约束的所有列的可空性都设置为 NOT NULL。
回答by Thilina H
Well, they are very similar but here are the differences.
嗯，它们非常相似，但这里有不同之处。
Only one primary key is allowed on a table but multiple unique indexes can be added up to the maximum allowed number of indexes for the table (SQL Server = 250 (1 x clustered, 249 x non clustered) and SQL 2008 and SQL 2012 = 1000 (1 x clustered, 999 x non clustered)). Primary keys cannot contain nullable columns but unique indexes can. Note, that only one NULL is allowed. If the index is created across multiple columns, each combination of values and NULL's must be unique.
一个表上只允许有一个主键，但可以添加多个唯一索引，直至表的最大允许索引数（SQL Server = 250（1 x 聚集，249 x 非聚集）和 SQL 2008 和 SQL 2012 = 1000 （1 x 集群，999 x 非集群））。主键不能包含可为空的列，但唯一索引可以。请注意，只允许一个 NULL。如果索引是跨多个列创建的，则每个值和 NULL 的组合都必须是唯一的。
By default, unless you specify otherwise in the create statement and providing that a clustered index does not already exists, the primary key is created as a clustered index. Unique indexes however are created by default as non clustered indexes unless you specify otherwise and providing that a clustered index does not already exist.
默认情况下，除非您在 create 语句中另外指定并提供聚集索引尚不存在，否则主键将创建为聚集索引。但是，默认情况下，唯一索引创建为非聚集索引，除非您另外指定并提供聚集索引尚不存在。
Following link will really help you.just go with it
以下链接真的对你有帮助。就去吧
回答by paqogomez
Yes, a composite and unique key, like you have here, will give you an index very much like the primary key. One of the advantages of these are that the data is contained in the index, so it does not have to do a look up in the table if you are only querying for the fields in the key.
是的，一个复合的唯一键，就像你在这里一样，会给你一个非常像主键的索引。这些的优点之一是数据包含在索引中，因此如果您只查询键中的字段，则不必在表中查找。
This is also possible in Entity Framework. It would go something like this.
这在实体框架中也是可能的。它会像这样。
public class AT_APSRANCD
{
[Column(Order = 0), Key, ForeignKey("AC_Analysis_category")]
public int AC_Analysis_category{ get; set; }
[Column(Order = 1), Key, ForeignKey("AC_ANALYSI_CODE")]
public int AC_ANALYSI_CODE{ get; set; }
}
回答by SWeko
They are most certainly not the same thing.
它们肯定不是一回事。
A primary key must be unique, but that is just one of the its requirements. Another one would be that it cannot be null, which is not required of a unique constraint.
主键必须是唯一的，但这只是其要求之一。另一种是它不能为空，这不是唯一约束所必需的。
Also, while, in a way, unique constraints can be used as a poor man's primary keys, using them with IGNORE_DUP_KEY = ONis plainly wrong. That setting means that if you try to insert a duplicate, the insertion will fail silently.
此外，虽然在某种程度上，唯一约束可以用作穷人的主键，但将它们用于IGNORE_DUP_KEY = ON显然是错误的。该设置意味着如果您尝试插入重复项，插入将无声无息地失败。
回答by Dev
primary key not contain any null value.
主键不包含任何空值。
but in case of unique null value can insert in table.
但在唯一空值的情况下可以插入表中。
any number of nullvalue can be insert
可以插入任意数量的空值
definition of primary key PRIMARY_KEY=UNIQUE+NOT_NULL
主键定义PRIMARY_KEY=UNIQUE+NOT_NULL
回答by Szymon
They're definitely different. As mentioned in other answers:
他们肯定是不同的。正如其他答案中提到的：
- Unique key is used just to test uniqueness and nothing else
- Primary key acts as an identifier of the record.
- 唯一键仅用于测试唯一性，仅用于测试
- 主键充当记录的标识符。
Also, what's important is that the primary key is usually the clustered index. This means that the records are physically stored in the order defined by the primary key. This has a big consequences for performance.
此外，重要的是主键通常是聚集索引。这意味着记录按主键定义的顺序物理存储。这对性能有很大的影响。
Also, the clustered index key (which is most often also the primary key) is automatically included in all other indexes, so getting it doesn't require a record lookup, just reading the index is enough.
此外，聚集索引键（通常也是主键）会自动包含在所有其他索引中，因此获取它不需要查找记录，只需读取索引就足够了。
To sum up, always make sure you have a primary key on your tables. Indexes have a huge impact on performance and you want to make sure you get your indexes right.
总而言之，始终确保您的表上有一个主键。索引对性能有巨大影响，您希望确保索引正确。
