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
5
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
1
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
5
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
1
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)