PK和唯一约束

Question

我有一个如下所示的表格来保存用户声明（1）：

CREATE TABLE [dbo].[UserClaims] (
  [UserAccountID] [int] NOT NULL,
  [Type] [nvarchar](150) NOT NULL,
  [Value] [nvarchar](150) NOT NULL,
  CONSTRAINT [PK_dbo.UserClaims] PRIMARY KEY ([UserAccountID], [Type], [Value])
)

我有另一种选项来处理同一个表格 (2)：

CREATE TABLE [dbo].[UserClaims] (
  [UserAccountID] [int] NOT NULL,
    CONSTRAINT PK_UserClaims_UserAccountId PRIMARY KEY CLUSTERED (UserAccountId),
  [Type] [nvarchar](150) NOT NULL,
  [Value] [nvarchar](150) NOT NULL,
    CONSTRAINT UQ_UserClaims_Value_Type_UserAccountID unique (Value, [Type], UserAccountID)
)

我有一张表：

CREATE TABLE [dbo].[UsersAccounts] (
  [UserAccountID] [int] IDENTITY NOT NULL,
    CONSTRAINT PK_UsersAccounts_UserAccountId PRIMARY KEY CLUSTERED (UserAccountId)
)

当我得到一个用户时，我总是需要从UserClaims中获取所有它的声明。

UserAccountID足以标识所有用户声明...

在选项（2）中，UserAccountID既是主键又是外键。

我需要能够向UserClaims添加以下行：

UserAccountID = 2，Type = Role，Value = Editor UserAccountID = 1，Type = Role，Value = Editor UserAccountID = 1，Type = Role，Value = Member UserAccountID = 1，Type = Name，Value = John

但我不能添加以下声明：

UserAccountID = 1，Type = Role，Value = Editor UserAccountID = 1，Type = Role，Value = Editor 我不能有两行完全相同。其他所有组合都是可能的。

我的想法是使用（2）。 它更接近我所习惯的做法... 你怎么看？

谢谢！

- Miguel Moura

---

在这种情况下，UserClaims UserAccountID 与 Users 表的 UserAccountID 相同，即标识。因此，当我获取用户时，我还需要获取其声明。我永远不会单独获取声明。这就是为什么我使用了 UserAccountId...那么我需要将其索引并使其成为 PK 吗？还是唯一约束已足够？ - Miguel Moura

@twoleggedhorse：在我看来，你在混淆理论和实现。约束是一个抽象的数据建模概念，可以通过索引（这是一个具体的物理事物）来实现/强制执行（而且似乎MS和mysql文档也混淆了它们）。 - wildplasser

Answer 1

一切取决于你的主键应该是什么 :) 但最有可能的是[UserAccountID]应该是一个主键，所以第一种解决方案是错误的。这会使你能够插入具有相同[UserAccountID]（其他两列不同）的多行，这将在与其他表连接时生成重复记录。

- AdamL

---

哦，我错过了表的名称。在这种情况下，两种方法都是错误的。第二个暗示用户只能有一个索赔，而第一个则表示用户只能拥有相同类型和金额的一个索赔，这在任何业务中也不合法，我想。 所以，我猜你实际上不需要在表上设置唯一/PK约束（除非你的业务逻辑说明其他事情-那会是什么原因呢？）。 我只会添加一个索引到[UserAccountId]来加快查询速度，也许[ClaimId] int identity(1,1)带有主键。 - AdamL

一个用户只能拥有一个相同类型和值的声明。一个用户只能拥有类型为“角色”，值为“编辑器”的声明。这有意义吗？这就是为什么我对这三列设置了唯一约束。我刚刚更新了我的问题以提供更多信息。 - Miguel Moura

Answer 2

基于这两种选择，我会选择第一个，因为它包含了所有你的设计需求：

• 确保唯一性。
• 性能。

第二个表格设计也可行，但需要维护两个索引，增加了额外的开销。

作为另一种选择，我实际上会对UserClaims表进一步规范化，采用以下结构：

create table dbo.UserAccount
(
    UserAccountID   int not null primary key
)

create table dbo.Claim
(
    ClaimID     int             not null primary key
    , Type      nvarchar(150)   not null
    , Value     nvarchar(150)   not null
    , unique
    (
        Type, Value
    )
)

create table dbo.UserClaims 
(
    UserAccountID   int not null
    , ClaimID       int not null
    , primary key 
    (
        UserAccountID
        , ClaimID
    )
)

更新 - 更具体地回答你的问题，如果你需要搜索给定值的所有用户声明，你可能会想使用第二种方法。

例如，显示所有声称超过$200,000的用户。

正如你所看到的，表格设计实际上取决于你的数据模型和你所提出的业务问题。

更新 - 考虑到提供的其他信息，现在更清楚你想问什么了。以下是我修订后的回答。

基本上，这归结为从选项(1)中得到什么，而从选项(2)中得不到什么？就我所看到的，两者都没有区别。

你绝对可以在选项(1)中创建一个外键UserAccountID:

alter table dbo.UserClaims
add constraint fk_UserClaims_UsersAccounts foreign key
(
    UserAccountID
)
references UsersAccounts
(
    UserAccountID
)

如果您按UserAccountID搜索，两个选项中的聚集主键都可以高效地获得结果。

如果您想强制唯一性，选项1中的主键和选项2中的唯一约束/索引都能够完成工作。

- Louie Bao

---

你好，规范化索赔是一个好主意，但在这种情况下不可能...我将没有办法对其进行规范化。 - Miguel Moura

Answer 3

第二个更有用，因为您可能会在很多WHERE和JOIN中使用UserAccountID，因此在其上创建一个索引将使您的查询更加高效。

- Bartosz Marcinkowski

---

但是这两个约束条件不会做同样的事情。 - user330315

第一个也适用于在WHERE和JOIN中使用UserAccountID，因为它是以UserAccountID开头的聚集索引。 - Louie Bao

每个DBMS都是这样吗？ - Bartosz Marcinkowski

我不会有太多的WHERE和JOINS查询。基本上，当我获取一个用户时，我会获取所有的索赔（不超过10个）。 - Miguel Moura

Answer 4

尽管在执行约束方面，“PK”和“UNIQUE”都强制列（或一组列）具有唯一值。但从逻辑上讲，“主键”是唯一标识记录的，而不仅仅是维护列上的唯一约束。一张表中只能有一个主键，但可以有多个唯一键。

更多取决于使用环境：

例如，您有一个表“person（varchar ssn，varchar vehiclenumber，.....）”，虽然SSN和车辆号码都应该是唯一的，但由于该表包含有关人员的信息，因此您希望SSN成为主键，vehiclenumber成为唯一键。

另一方面，您有一个表“VehicleInfo（varchar owner_SSN，varchar vehiclenumber，.....）”。即使在这种情况下，SSN和车辆号码也应该是唯一的，但由于在这种情况下表是车辆，因此您希望将vehiclenumber作为主键，SSN作为唯一键。

- Ankit

---

通常，我喜欢每个表都有一个PK...然后使用约束。我更倾向于使用选项（2）而不是（1）。我刚刚用更多信息更新了我的代码。 - Miguel Moura

Answer 5

PK 不能包含 NULL 值，而 UNIQUE 可以。在这种情况下，您有 NOT NULL 字段，但请记住在所有一般情况下这个实质性差异。第二个区别：每个表最多只有一个 PK（并允许 n 个外键），而您可以设置任意数量的 UNIQUE。

示例：您的表使用以下字段存储有关美国卫生系统卫生卡的数据：

- 卡代码 - 用户社会保障号码（SSN） - 用户名/姓氏

在这种情况下，您的卡代码可能是系统的 PK，因为您的上下文是卫生系统，但即使不在 PK 约束中，您也需要保持唯一的 SSN。实际上，两个字段（SSN 和 Card Code）的 PK 允许相同的 SSN 用于不同的 Card Code，相同的 Card Code 用于不同的 SSN，而我们需要保持两者都唯一。通常，我使用标识我的记录在上下文中作为 PK，其他记录作为 UNIQUE 的字段。

如果UserAccountID标识您的记录，则必须将该字段设置为PK，将其他字段（VALUE，TYPE）设置为UNIQUE。提供一个表格示例，我们可以更具体地说明。

- Francesco De Lisi

Answer 6

我不会选择选项1或选项2，而是引入一个新字段作为主键。我不确定你的业务逻辑，但我认为这三种情况都排除了选项1：1.我们记录了错误的用户索赔，需要更正！（需要更改UserAccountID）2.我们记录了错误类型的索赔，需要更正（需要更改类型）3.索赔价值已经改变（需要更改价值金额）。这个非常合理的情况排除了选项2：用户提交了第二份索赔。如果这些数据中的任何一个在任何时候都可能发生变化，那么它们就不适合用作您的主键。相反，只需添加一个新的自增整数字段作为ClaimID。它占用了一点更多的空间（小缺点），但好处是你的自然数据值永远不会受到成为主键的人为限制。此外，我不会在这三个字段上添加唯一性约束；出于类似的原因，你的自然数据值可能不是唯一的。例如，如果用户提出了第二项索赔，类型和价值相同（可能是在不同的日期）。

- Vince Bowdren