多个列上的SQL条件约束是唯一的

I would like to have a conditional constraint on my database:

我希望在我的数据库上有一个条件约束:

You can see that my CreatedBy column has a few entries missing due to legacy data problems.

您可以看到，由于遗留数据问题，我的CreatedBy列有一些条目丢失。

My new code ensures the CreatedBy is filled out.

我的新代码确保被填满了。

Question:

问题:

I would like to ensure that noone can enter duplicate entries into the db by adding a conditional constraint. that defines that a row should be unique by AnswerID, PollID and CreatedBy... only if CreatedBy is not empty

我希望通过添加条件约束来确保没有人可以在db中输入重复的条目。这就定义了一行应该是唯一的由AnswerID、PollID和CreatedBy…除非CreatedBy不是空的

It might look like this:

它可能是这样的:

CONSTRAINT UNIQUE BY AnswerID, PollID, CreatedBy WHERE CreatedBy IS NOT NULL

Any ideas if it's possible?

如果可能的话，有什么想法吗?

2 个解决方案

#1

Sure, use a filtered index:

当然，使用过滤索引:

CREATE UNIQUE NONCLUSTERED INDEX <indexName>
    ON <tableName> (AnswerID, PollID, CreatedBy)  
    WHERE CreatedBy IS NOT NULL;

Or, since you said "not empty" then something like this should do:

或者，既然你说“不是空的”，那么像这样的事情应该是这样的:

CREATE UNIQUE NONCLUSTERED INDEX <indexName>
    ON <tableName> (AnswerID, PollID, CreatedBy)  
    WHERE CreatedBy IS NOT NULL
      AND CreatedBy <> N'';

#2

If version is prior to 2008:

如版本在2008年之前:

CREATE VIEW dbo.vw_Tablename
WITH SCHEMABINDING AS
SELECT AnswerID, PollID, CreatedBy
FROM dbo.Tablename
WHERE CreatedBy IS NOT NULL;

GO

CREATE UNIQUE CLUSTERED INDEX UIX_vw_Tablename 
  ON dbo.vw_Tablename(AnswerID, PollID, CreatedBy)

#1