sql - 如果不为空，则对另一列进行唯一约束

Question

我想创建具有以下约束的表，是否可以使用 SQL Server Management Studio 来实现？

• 身份证
• ProductId [可为空的列]
• CompanyId [可为空的列]
• 用户名[对于每个 productId 都是唯一的 - 如果 DeletedAt 不为 NULL]
• 标识符 [可为 Null 的列] [对于每个 companyId 都是唯一的 - 如果 DeletedAt 不为 NULL]
• 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

Answer 1

您需要使用唯一过滤索引。给你 table :

/****** 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

其他列的索引逻辑相同，但我不想通过发布更多内容来扰乱这个答案!我不会把逻辑放在触发器或存储过程中，坏数据仍然可能潜入。如果你保持良好的约束，没有人可以弄乱你的数据:)