Single default value in a table

Question

I'm creating a table that'll have a single bit not null column IsDefault. I need to write a constraint that'll make sure there'll be only one default value per UserId (field in the same table). I can't use unique constraint on this because it is possible to have many non-default values.

What is the best approach to do this using MS SQL Server 2008?

Thanks.

Answer 1

The easiest way I see is a check constraint with a UDF (User Defined function).

Look at here, for example. http://sqljourney.wordpress.com/2010/06/25/check-constraint-with-user-defined-function-in-sql-server/

Untested example

CREATE FUNCTION dbo.CheckDefaultUnicity(@UserId int)
RETURNS int
AS 
BEGIN
   DECLARE @retval int
   SELECT @retval = COUNT(*) FROM <your table> where UserId = @UserId and <columnwithDefault> = 1-- or whatever is your default value
   RETURN @retval 
END;
GO

and alter your table

ALTER TABLE <yourTable> 
ADD CONSTRAINT Ck_UniqueDefaultForUser 
CHECK (dbo.CheckDefaultUnicity(UserId) <2)