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

Question

DBMS: MS Sql Server 2005, Standard

I would like to make a table constraint so that only one record has a specific value in a subset of the table (where the rows have a value in a specific column). Is it possible?

Example: I have entries in myTable that have a unique foreign key (fk1), and a column bit called isPrimary to indicate that this particular should be used by our application for special logic.

in an abstract expression is as follows:

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

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

Sample data: This must 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 not one of them (fk = 222 anywhere):

 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 using a table constraint?

UPDATE So far, I have gone with Martin Smith's answer, although I will insist on the JohnFx refactor in the upcoming release, as this is the best long-term solution. However, I wanted to publish an updated UDF based on Raze2dust's answer in case future readers decide which is best suited to 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:

• Used by @tempTable, not

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

• passed @ fk1 as a parameter, so I can choose uniqueness for one group of fk1 values.
• it was also difficult to pass isPrimary, although this is not necessary for logic, otherwise the SQL2005 optimizer will not run a check constraint when isPrimary is updated.

Answer 1

Using UDF in control restrictions may fail with snapshot isolation or multirow updates .

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

 CASE WHEN isPrimary = 1 THEN fk1 ELSE -pk1 END 

then add to that a unique constraint. Or if this assumption cannot be fulfilled, perhaps

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

Answer 2

I started a new answer, because I was looking badly for the first one.

It looks like you could solve the problem by rethinking your table design a bit so as not to make you brute force to implement your business rule.

What about removing the IsPrimary column from MyTable and adding the PrimaryPersonID column to another table that refers to the primary user?

Thus, the structure itself will ensure that 1, and only 1 entry in the FK table is primary for each person.

Answer 3

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

• Create an indexed view that filters for IsPrimary = 1 and adds to this unique index.
• Create a trigger in which you guarantee that only one can be primary.
• Encapsulate your logic in a saved process and force users to go through the saved process to insert or update it from the table.

Answer 4

You can try to create a function and then use a control 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 this works. Not very elegant though ..