Enforcing a unique constraint - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Enforcing a unique constraint

Author

Topic

MuadDBA

628 Posts

Posted - 2012-09-17 : 15:30:35

I have a table which has three related colums I would like to add a constraint to, and I am trying to figure out the least expensive way to enforce the constraint.

The columns are:
USERID
ITEMID
WORK_OUTSTANDING

For any given userid and itemid, there can be multiple rows with work_outstanding = 0, but only 1 with it = 1.

My thought was to use a filtered index to enforce this, but I feel like I am missing something else obvious. Please feel free to whack me over the head with it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-17 : 15:49:54

i would have done this with UDF and check constraint

add a UDF like


CREATE FUNCTION OutstandingWorkCnt
(
@USERID int,
@ITEMID int
)
RETURNS int
AS
BEGIN 
DECLARE @Cnt int

SELECT @Cnt= COALESCE(Cnt,0)
FROM (SELECT COUNT(*) AS Cnt
      FROM Table
      WHERE USERID=@USERID
      AND ITEMID = @ITEMID
      AND work_outstanding = 1
     )t

RETURN (@Cnt)
END


and use it for a check constraint like

ALTER TABLE tablename ADD CONSTRAINT Chk_OutstWrkCnt CHECK (dbo.OutstandingWorkCnt(UserID,ItemID) =1)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

MuadDBA

628 Posts

Posted - 2012-09-17 : 16:00:48

Seems like that UDF would require an index in order to be effective, and if I'm going to make an index, why not allow it to enforce the constraint I need?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-17 : 16:10:39

quote:
Originally posted by MuadDBA

Seems like that UDF would require an index in order to be effective, and if I'm going to make an index, why not allow it to enforce the constraint I need?

it requires only check constraint

also there's a small typo

the check constraint has to be like

ALTER TABLE tablename ADD CONSTRAINT Chk_OutstWrkCnt CHECK (dbo.OutstandingWorkCnt(UserID,ItemID) <=1)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-09-17 : 23:40:31

I'd go with the unique filtered index instead of the UDF. Multiple row insert/updates will explode the I/O using the UDF.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources