Unique Check constraint for a certain value

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

General SQL Server Forums

New to SQL Server Programming

Unique Check constraint for a certain value

Author

Topic

tech_1
Posting Yak Master

129 Posts

Posted - 2012-01-05 : 07:18:57

I have a column bit "IsDefault"

there can only ever one row which has this set to true.

how can I create a constraint to check this?

so if we have 4 rows where 3 of them are 0 and one of them is set to 1, then they decide to set row 2 to 1 also, it should be invalid as you can only ever have IsDefault set to true for 1 row only

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-01-05 : 07:25:33

I would use a trigger or better doing the check in my application.

No, you're never too old to Yak'n'Roll if you're too young to die.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-01-05 : 09:29:41

If you're using SQL Server 2008 take a look at filtered indexes. You could create a unique index like this:

CREATE UNIQUE INDEX OneDefaultOnly ON myTable(IsDefault) WHERE IsDefault=1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-05 : 09:31:53

if you really want to use a check constraint you need to use UDF for that

create a udf like


CREATE FUNCTION DefaultCount
(
@GrpValue ....
)
RETURNS int
AS
BEGIN 
DECLARE @Cnt int

SET @Cnt=COALESCE((SELECT COUNT(*)
FROM Table
WHERE GrpVal=@GrpVal),0)

RETURN @Cnt
END

@GrpVal represents group of columns for which you need to select single default record

then use udf in check constraint as


ALTER TABLE <tablename> ADD CONSTRAINT chk_cnteq1 CHECK (dbo.DefaultCount(YourCol) <=1)

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-01-05 : 13:02:33

Since you didn't provide your table structure it is hard to give you more options. But, if your table as a numeric key, you might be able to use a computed column and unique constraint.

CREATE TABLE dbo.Test
(
    ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    IsDefault BIT NOT NULL DEFAULT(0),
    Uniqifier AS (CASE WHEN IsDefault = 0 THEN -1 * ID ELSE IsDefault END)  PERSISTED NOT NULL
)

ALTER TABLE dbo.Test
	ADD CONSTRAINT UC_Test UNIQUE (Uniqifier ASC)
go
    
INSERT dbo.Test (IsDefault) VALUES (0)
INSERT dbo.Test (IsDefault) VALUES (1)
INSERT dbo.Test (IsDefault) VALUES (1)-- Fails
UPDATE dbo.Test SET IsDefault = 1 WHERE ID = 1 --Fails
UPDATE dbo.Test SET IsDefault = 0 WHERE ID = 2 
INSERT dbo.Test (IsDefault) VALUES (1)

EDIT: Forgive the spelling errors, I jsut ripped this out.

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-05 : 13:05:50

I would re-evaluate my data model

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

sqlgirlatty
Starting Member

5 Posts

Posted - 2012-01-05 : 13:51:21

Thanks Visakh - your response saved me from posting my own question

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources