一组记录中的唯一约束，其中某些值相同

DBMS: MS Sql Server 2005, Standard

DBMS：MS Sql Server 2005，标准版

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?

我想创建一个表约束，只有一个记录在表的一个子集中具有特定值（其中行共享特定列中的值）。这可能吗？

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.

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

in the abstract, it looks like this:

在摘要中，它看起来像这样：

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

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.

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

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

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

但这不应该是（超过一个fk = 111）：

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

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

这也不应该（没有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'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.

更新我现在已经接受了Martin Smith的回答，尽管我将在即将发布的版本中推动JohnFx的重构，因为它是最好的长期解决方案。但是我想根据Raze2dust的答案发布我更新的UDF，以防未来读者认为更适合他们的需求。

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;

Changes:

变化：

Used @tempTable rather than
tempTable (in memory v. written to disk) as required by udf
根据udf的要求，使用@tempTable而不是tempTable（在内存中，写入磁盘）
passed @fk1 as a parameter so I can select for uniqueness within one group of fk1 values.
传递@ fk1作为参数，因此我可以在一组fk1值中选择唯一性。
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.
即使对于函数的逻辑没有必要，棘手也必须传递isPrimary，否则SQL2005优化器在更新isPrimary时不会运行检查约束。

4 个解决方案

#1

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

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

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

假设您的所有fk1和pk1值当前（并且将始终为）为正，您可以使用以下定义创建计算列

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

#2

Started a new answer since I mangled the first one badly.

自从我严重破坏第一个问题以来，开始了一个新的答案。

It sounds like you could address the issue by rethinking your table design a little to avoid making you brute force a constraint to implement your business rule.

听起来你可以通过重新思考你的表设计来解决这个问题，以避免让你蛮力成为实施业务规则的约束。

How about dropping the IsPrimary Column from MyTable and adding a PrimaryPersonID column to the other table that references primary person?

如何从MyTable中删除IsPrimary列并将PrimaryPersonID列添加到引用主要人员的另一个表中？

That way the structure itself would enforce that 1 and only 1 entry in the FK table was primary for each person.

这样，结构本身就会强制执行1，并且FK表中只有1个条目是每个人的主要条目。

#3

SQL 2005 does not provide the ability to apply a Where clause to a unique index like SQL 2008. However, there are a few ways to solve the problem in SQL 2005:

SQL 2005不提供将Where子句应用于SQL 2008等唯一索引的功能。但是，有几种方法可以解决SQL 2005中的问题：

Create an Indexed view that filters for IsPrimary = 1 and add a unique index to that view.
创建一个过滤IsPrimary = 1的索引视图，并为该视图添加唯一索引。
Create a trigger where you ensure only one can be primary.
创建一个触发器，确保只有一个可以是主要的。
Encapsulate your logic into a stored proc and force users to go through the stored proc to insert or update from your table.
将您的逻辑封装到存储过程中并强制用户通过存储过程从您的表中插入或更新。

#4

You could try creating a function and then using a check constraint:

您可以尝试创建一个函数，然后使用检查约束：

CREATE FUNCTION ChkFn()
RETURNS INT
AS 
BEGIN
   DECLARE @retval INT
   DECLARE @distinct INT
   DECLARE @top INT
   SET @retval = 0
   SELECT fk1 AS ForeignKey, SUM(isPrimary) AS PrimarySum
    INTO #TempTable 
    FROM myTable
    GROUP BY fk1
   SELECT @distinct = COUNT(DISTINCT(PrimarySum)) FROM #TempTable
   SELECT @top = top PrimarySum FROM #TempTable
   IF(@distinct=1 AND @top=1)
    BEGIN
    @retval = 1
    END
   RETURN @retval
END;
GO

ALTER TABLE myTable
ADD CONSTRAINT chkFkPk CHECK (dbo.ChekFn() = 1 );
GO

Try it and let me know if it worked. Not very elegant though..

尝试一下，让我知道它是否有效。虽然不是很优雅..

#1