如果不为空，则在另一列上设置唯一约束。

Question

我想用以下约束条件创建这个表，是否可以在SQL Server Management Studio中实现？

• Id
• ProductId [可空列]
• CompanyId [可空列]
• Username [对于每个ProductId唯一 - 如果DeletedAt不为空]
• Identifier [可空列] [对于每个CompanyId唯一 - 如果DeletedAt不为空]
• DeletedAt [可空列]

更新： 我的表创建查询如下：

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[User](
    [Id] [int] IDENTITY(1,1) NOT NULL, 
    [ProductId] [int] NULL,
    [CompanyId] [int] NULL,
    [Username] [nvarchar](max) NOT NULL,
    [Identifier] [nvarchar](max) NULL,
    [DeletedAt] [datetime2](7) NULL,
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

- Coding Academy

---

我猜你会使用触发器来强制执行业务规则约束，而不是其他方式。 - JohnyL

只要两个JohnSmith用户名都有DeletedAt值，你可以在productID 1中拥有2个JohnSmith用户名。 - dfundako

@dfundako 是的，完全正确。 - Coding Academy

“DeletedAt” 是一个日期时间类型吗？当我阅读需求时，我的第一反应是“这是一个缓慢变化的维度，他正在使用‘DeletedAt’作为逻辑删除标志。”如果我是正确的，您可以将 DeletedAt 添加到唯一约束中。或者，您可以创建一个归档表，每当您从主表中删除记录时，就在归档表中复制它。 - Brian

@Brian 好的，谢谢你的帮助。我正在研究被提出作为答案的过滤索引。 - Coding Academy

Answer 1

您需要使用一个“唯一过滤索引”。下面是针对表格的内容：

/****** Object:  Table [dbo].[the_table]    Script Date: 7/26/2018 4:04:00 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[the_table](
    [id] [int] NOT NULL,
    [productid] [varchar](50) NULL,
    [companyid] [varchar](50) NULL,
    [username] [varchar](50) NULL,
    [identifier] [varchar](50) NULL,
    [deleteat] [varchar](50) NULL,
 CONSTRAINT [PK_the_table] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

用户名的索引：

SET ANSI_PADDING ON
GO

/****** Object:  Index [UIDX_USERNAME]    Script Date: 7/26/2018 4:03:31 PM ******/
CREATE NONCLUSTERED INDEX [UIDX_USERNAME] ON [dbo].[the_table]
(
    [username] ASC
)
WHERE ([DELETEAT] IS NOT NULL)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

对于其他列，索引逻辑将是相同的，但我不想通过发布更多内容来使答案杂乱无章！请勿尝试将逻辑放在触发器或存储过程中，因为不良数据仍然可能会出现。如果您保持良好的约束条件，就不会有人混淆您的数据 :)

- sniperd

---

嗨。当我尝试进行索引时，我遇到了以下错误： 表'dbo.User'中的列'Username'是一种无法用作索引键列的类型。 - Coding Academy

我刚刚更新了我的问题，并附上了创建表语句。也许我做了一些意外的更改。 - Coding Academy

@CodingAcademy 我认为你不能在 VARCHAR(MAX) 数据类型上创建索引。你可以将其更改为 VARCHAR(8000) 或者更合理的长度，比如100。我怀疑你不会有一个真正那么长的用户名 :) - sniperd

Answer 2

你可以使用过滤索引。语法如下所示：

create unique index unq_thetable_username on the_table(username)
    where deleteAt is not null;

我不知道你是否可以通过点-and-click的方式来实现这个解决方案。我会按照上述逻辑编写代码。

- Gordon Linoff

---

我在您的语法中发现了一个错误：表 'dbo.User' 中的列 'Username' 具有无效类型，不能作为索引中的键列使用。这是因为它不可为空吗？ - Coding Academy

@CodingAcademy……这在SQL Fiddle上运行良好：http://www.sqlfiddle.com/#!18/1f357。 - Gordon Linoff

Answer 3

在ProductId和Username上创建一个唯一索引，并使用where子句将该索引限制为“WHERE DeletedAt IS NOT NULL”。显示唯一筛选索引示例的答案。

您可能还需要使用“AND ProductId IS NOT NULL”子句，尽管未说明此列为必需，但由于该列可为空，因此应考虑这一点。

对于标识符类似。

您可能还想考虑一个表的价值，在其中有效地具有所有列均为null的行...

- Richardissimo