Constraint question

Question

Constraint question

TJ Heath

SSC Enthusiast

Points: 112
More actions
July 2, 2014 at 10:02 pm

#293050

I've added an IsActive bit field to a slowly changing dimension, and I want to enforce the rule that only one version can have a 1 in the IsActive field, but many versions can have a 0. Is there a way to enforce this constraint at the table level?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 1

One way would be to create a UDF to check the values and use that as constraint for the table.

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

pmadhavapeddi22 SSCertifiable Points: 5306 More actions · Answer 2

Try creating a filtered index

I have given the example as follows;

CREATE TABLE tbl(col1 tinyint);

create unique index tbl_idx on tbl(col1) where col1 = 1;

INSERT INTO tbl VALUES(1),(0),(0)

INSERT INTO tbl VALUES(0),(0),(0)

The above unique filtered index will prevent to enter 1 again

INSERT INTO tbl VALUES(1) -- this statement will throw error

drop table tbl

Hope this helps

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

I'd use a filtered unique index to enforce that.

CREATE TABLE SomeTable (

Version INT

IsActive BIT NOT NULL

)

CREATE UNIQUE INDEX idx_SomeTableVersion ON SomeTable (Version)

WHERE (IsActive = 1)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

TJ Heath SSC Enthusiast Points: 112 More actions · Answer 4

Thank you for your help. I wasn't aware filtered indexes existed. I love learning something new.