Let's say I have some table T with some non-null field A. There is a clustered index on A. Consider the following two options:
假设我有一些表T带有一些非空字段A.在A上有一个聚簇索引。考虑以下两个选项:
- I make
Athe primary key. - I create a UNIQUE constraint for
A.
我把A作为主键。
我为A创建一个UNIQUE约束。
Where's the difference? I know what a primary key is, and I know that there is a conceptual difference w.r.t. database theory. But what is the actual difference in SQL Server? How does the SQL Server database engine behave differently? In both cases, the clustered index of the table is on A; and in both cases I can refer to A through foreign keys.
区别在哪里?我知道主键是什么,我知道w.r.t有一个概念上的区别。数据库理论。但是SQL Server的实际区别是什么? SQL Server数据库引擎的行为有何不同?在这两种情况下,表的聚簇索引都在A上;在这两种情况下,我都可以通过外键来引用A.
(Related question which inspired this question: Meaning of Primary Key to Microsoft SQL Server 2008)
(启发了这个问题的相关问题:Microsoft SQL Server 2008主键的含义)
2 个解决方案
#1
5
No. There's really no difference. The optimizer treats it the same way. There's even debate among certain circles about whether or not primary keys, per se, are really necessary. (Though nobody would argue that you should define at least one unique index/constraint per table).
没有。真的没什么区别。优化器以相同的方式处理它。某些圈子甚至还在争论主键本身是否真的有必要。 (虽然没有人会认为你应该为每个表定义至少一个唯一的索引/约束)。
An argument against the "primary key" concept might go like this: If you have two columns on a table, and they're both unique and the same amount of data, which becomes the PK? A fine question indeed.
反对“主键”概念的一个论点可能是这样的:如果一个表上有两列,它们既是唯一的又是相同数量的数据,那就变成了PK?确实是个好问题。
Anyway, I always use the PRIMARY KEY concept because it's pretty helpful from a documentation perspective, and it really helps folks understand your intent.
无论如何,我总是使用PRIMARY KEY概念,因为从文档的角度来看它非常有用,它确实有助于人们理解你的意图。
#2
3
I never get any real difference but conceptual. But i think about this much times too.
我从来没有得到任何真正的区别,但概念但我也想过这么多次。
#1
5
No. There's really no difference. The optimizer treats it the same way. There's even debate among certain circles about whether or not primary keys, per se, are really necessary. (Though nobody would argue that you should define at least one unique index/constraint per table).
没有。真的没什么区别。优化器以相同的方式处理它。某些圈子甚至还在争论主键本身是否真的有必要。 (虽然没有人会认为你应该为每个表定义至少一个唯一的索引/约束)。
An argument against the "primary key" concept might go like this: If you have two columns on a table, and they're both unique and the same amount of data, which becomes the PK? A fine question indeed.
反对“主键”概念的一个论点可能是这样的:如果一个表上有两列,它们既是唯一的又是相同数量的数据,那就变成了PK?确实是个好问题。
Anyway, I always use the PRIMARY KEY concept because it's pretty helpful from a documentation perspective, and it really helps folks understand your intent.
无论如何,我总是使用PRIMARY KEY概念,因为从文档的角度来看它非常有用,它确实有助于人们理解你的意图。
#2
3
I never get any real difference but conceptual. But i think about this much times too.
我从来没有得到任何真正的区别,但概念但我也想过这么多次。