Unique constraint within a group of records where some value is the same

Question

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?

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)

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

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

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'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;

Changes:

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.

Complice

Answer 1

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

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

Profitsharing

Though JohnFx's solution is the best long-term fix, this is the solution for me right now. – Complice

Answer 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?

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

Gourmand

You're totally right, this should be the data model, and I wish we could implement this. For now, we have a few different apps pointing at this and we can't change them all to access the updated model right now. I'll push to make this change in the future. – Complice

Answer 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:

1. Create an Indexed view that filters for IsPrimary = 1 and add a unique index to that view.
2. Create a trigger where you ensure only one can be primary.
3. Encapsulate your logic into a stored proc and force users to go through the stored proc to insert or update from your table.

Torr

4. Create a stored function that you use in a check constraint – Eugenioeugenius

Warning: A udf in a constraint can work, but it also can cause serious performance issues if it is doing selects. – Gourmand

The performance penalty would only be on insert and update, right? I can't imagine a check constraint firing when just selecting data. If that's true, I'm okay taking the hit, as the table is not written to very often. – Complice

Answer 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..

Blastocoel

This was very close and got me going in the right direction, so I'll mark it as accepted. I'll post the completed function on Monday. First problem was you can't use #TempTables in UDF, you must use @TempTable. The other's I'll address in the updated post. – Complice

ah yea forgot about UDF... No idea about multirow updates/snapshot isolation. Thx Martin for the article.. I'll try to understand from it. I'm a newbie :-) – Blastocoel