SQL Server中主键和唯一索引之间的区别[重复]

This question already has an answer here:

这个问题在这里已有答案:

What is the difference between Primary Key and unique key constraint? 6 answers

主键和唯一键约束之间有什么区别? 6个答案

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构建此系统以及使用Entity Framework作为我们的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.

我发现旧表结构没有主键,而是使用唯一索引作为主键。现在,在使用Entity框架时,我尝试在结构中匹配它们的表但由于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; }
}

6 个解决方案

#1

From SQL UNIQUE Constraint

从SQL UNIQUE约束

The UNIQUE constraint uniquely identifies each record in a database table.

UNIQUE约束唯一标识数据库表中的每条记录。

The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns.

UNIQUE和PRIMARY KEY约束都为一列或一组列的唯一性提供了保证。

A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.

PRIMARY KEY约束自动在其上定义UNIQUE约束。

Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

请注意,每个表可以有许多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。

#2

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.

总而言之,请始终确保您的表上有主键。索引会对性能产生巨大影响,您希望确保索引正确。

#3

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 = ON is plainly wrong. That setting means that if you try to insert a duplicate, the insertion will fail silently.

此外,虽然在某种程度上,唯一约束可以用作穷人的主键,但使用IGNORE_DUP_KEY = ON显然是错误的。该设置意味着如果您尝试插入重复项,则插入将无提示失败。

#4

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 clustered,249 x non clustered)和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

以下链接将真正帮助你。只需要它

HERE

#5

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; }
}

#6

primary key not contain any null value.

主键不包含任何空值。

but in case of unique null value can insert in table.

但是在唯一空值的情况下可以在表中插入。

any number of null value can be insert

可以插入任意数量的空值

definition of primary key PRIMARY_KEY=UNIQUE+NOT_NULL

主键PRIMARY_KEY = UNIQUE + NOT_NULL的定义

#1