一组记录中的唯一约束，其中一些值相同 [英] Unique constraint within a group of records where some value is the same

问题描述

DBMS ：MS Sql Server 2005，Standard

DBMS: MS Sql Server 2005, Standard

我想建立一个表约束，值（表中的行共享特定列中的值）。这是可能的吗？

I'd like to make a table constraint to have only one record have a particular value within a subset of the table (where the rows share a value in a particular column). Is this possible?

示例：
我在myTable中有一个非唯一的外键（fk1）一个名为isPrimary的位列，用于标记这个特殊的应用程序应该被特殊逻辑使用。

Example: I have records in myTable which have a non-unique foreign key (fk1), and a bit column called isPrimary to mark out that this particular one should be used by our app for special logic.

在抽象中，它看起来像这样：

in the abstract, it looks like this:

myTable
-------------
pk1       (int, not null)
name      (varchar(50), null)
fk1       (int, not null)
isPrimary (bit, not null)

对于fk1的每个唯一值，我想确保对于isPrimary标志设置为1，有一个且只有一个记录。

I want to ensure that there is one and only one record with the isPrimary flag set to 1, for each unique value of fk1.

数据示例：
这应该是合法的：

Data example: This should be legal:

pk1     name     fk1    isPrimary
----    -----    -----  ----------
1       Bill     111    1
2       Tom      111    0
3       Dick     222    1
4       Harry    222    0

但这应该 strong> be（多于一个，其中fk = 111）：

But this should not be (more than one where fk=111):

pk1     name     fk1    isPrimary
----    -----    -----  ----------
1       Bill     111    1
2       Tom      111    1
3       Dick     222    1
4       Harry    222    0

这两个都不应该（无其中fk = 222）：

And neither should this (none where fk=222):

pk1     name     fk1    isPrimary
----    -----    -----  ----------
1       Bill     111    1
2       Tom      111    0
3       Dick     222    0
4       Harry    222    0

有一种方法可以用表约束来做到这一点吗？

Is there a way to do this with a table constraint?

UPDATE
I'现在已经和马丁·史密斯的答案一起去了，虽然我会在即将到来的版本中推送JohnFx的重构，因为它是最好的长期解决方案。但是，我想根据Raze2dust的答案发布更新的UDF，以便未来的读者决定更适合他们的需求。

UPDATE I've gone with Martin Smith's answer for now, though I'll be pushing for JohnFx's refactor in an upcoming release, as it's the best long-term solution. However I wanted to post my updated UDF based on Raze2dust's answer, in case future readers decide that is a better fit for their needs.

CREATE FUNCTION [dbo].[OneIsPrimaryPerFK1](@fk1 INT, @dummyIsPrimary BIT)
RETURNS INT
AS 
BEGIN
    DECLARE @retval INT;
    DECLARE @primarySum INT;
    SET @retval = 0;
    DECLARE @TempTable TABLE (
    fk1 INT,
    PrimarySum INT)

INSERT INTO @TempTable
    SELECT fk1, SUM(CAST(isPrimary AS INT)) AS PrimarySum
    FROM FacAdmin
    WHERE fk1 = @fk1
    GROUP BY fk1;

    SELECT @primarySum = PrimarySum FROM @TempTable;
    IF(@primarySum=1)
        BEGIN
            SET @retval = 1
        END
    RETURN @retval
END;

更改：

1. 根据udf
的要求，使用@tempTable而不是



tempTable（在内存v。写入磁盘中）
< fk1作为参数，因此我可以选择一个
组fk1值中的唯一性。


棘手还必须通过isPrimary， t

函数的逻辑必需，否则当isPrimary为
更新时，SQL2005
优化器不会运行check
约束。

1. Used @tempTable rather than

   tempTable (in memory v. written to disk) as required by udf

2. passed @fk1 as a parameter so I can select for uniqueness within one group of fk1 values.
3. tricky had to also pass isPrimary even though it isn't necessary for the logic of the function, otherwise the SQL2005 optimizer will not run the check constraint when isPrimary is updated.

推荐答案

在检查约束中使用UDF可能会失败快照隔离或多行更新 。

Using UDFs in check constraints can fail under snapshot isolation or multirow updates.

假设你所有的fk1和pk1值都是正数，你可以用下面的定义创建一个计算列：

Assuming that all your fk1 and pk1 values are currently (and will always be) positive you could create a computed column with the following definition

CASE WHEN isPrimary = 1 THEN fk1 ELSE -pk1 END

然后为它添加一个唯一约束。或者如果不能做出该假设，那么也许

then add a unique constraint to that. Or if that assumption can't be made then maybe

CASE WHEN isPrimary = 0 THEN 1.0/pk1 ELSE fk1 END

这篇关于一组记录中的唯一约束，其中一些值相同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！