除了复合唯一约束之外，还有一个身份主键是多余的吗? [英] Is having an identity primary key in addition to a composite unique constraint redundant?

问题描述

我有一个表，它有一个标识列作为主键，并且在标识列和第二列上都有唯一约束:

I have a table that has an identity column as the primary key as well as unique constraint on both the identity column + a second column:

CREATE TABLE Variable
(
    VariableId BIGINT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
    CalcId BIGINT NOT NULL,
)
CREATE UNIQUE CLUSTERED INDEX CL_CalcId_VariableId ON Variable(CalcId,VariableId)

然后我有第二个表，它也有一个身份主键，以及作为外键的两个与第一个表相同的字段:

I then have a second table that also has an identity primary key, as well as two of the same fields as the first table that act as foreign keys:

CREATE TABLE Value
(
    ValueId BIGINT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
    CalcId BIGINT NOT NULL,
    VariableId BIGINT NOT NULL,
    FOREIGN KEY (CalcId,VariableId) REFERENCES Variable(CalcId, VariableId)
)
CREATE CLUSTERED INDEX CL_CalcId_VariableId_ValueId ON Value(CalcId,VariableId,ValueId)

这个设计是多余的吗?

由于 VariableId 是第一个表中的一个身份，我真的不需要我的外键来在第二个表中包含 CalcId 和 VariableId.我正在考虑以这种方式构建表，因为 CalcId+VariableId 的组合是描述唯一记录的有意义"，但具有标识列可以更轻松地编写更新/删除单行的查询 - 只是不确定如果这会使设计过于复杂.

Since VariableId is an identity in the first table, I don't really need my foreign key to have CalcId and VariableId in the second table. I am thinking of building the tables this way though because the combination of CalcId+VariableId is what "makes sense" to describe a unique record but having an identity column makes it easier to write queries that update/delete a single row - just not sure if this over-complicating the design.

如有任何想法，将不胜感激，谢谢.

Any thoughts would be appreciated, thanks.

一些示例数据:

VariableId  | CalcId
---------------------
1           | 1
2           | 1
3           | 1
4           | 2
5           | 2

ValueId | VariableId    | CalcId
---------------------------------
1       | 1             | 1
2       | 1             | 1
3       | 2             | 1
4       | 3             | 1
5       | 4             | 2
6       | 4             | 2
7       | 5             | 2 

推荐答案

在大多数情况下，答案是视情况而定.

As in most cases, the answer is it depends.

当您将表的键用作其他表的外键时，将标识列作为代理键与复合唯一索引一起使用是一个好主意.它简化了您的数据库连接并使您的连接更易于编写、阅读和维护.

Keeping an identity column as a surrogate key along side with a composite unique index is a good idea when you are using the key of the table as a foreign key to other tables. it simplifies your database connections and make your joins easier to write, read and maintain.

但是，如果您的表没有被其他表引用，那么添加代理键确实没有多大意义.

However, if your table is not referenced by other tables, then there really is not much sense in adding a surrogate key.

此外，正如 dnoeth 在他的评论中所写的那样，当复合唯一索引的哪些部分已经是唯一的时，没有必要创建复合唯一索引.

Also, as dnoeth wrote in his comment, there is no point of making a composite unique index when what of it's parts is already unique.

这篇关于除了复合唯一约束之外，还有一个身份主键是多余的吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！