Need a UNIQUE constrain on multiple cols in a SQL Server table but one of the cols is by VALUE

Say I have this simple example table

username varchar(100),
active char(1),
date_deactivated datetime

I need to have a UNIQUE constraint on this table where there can only be one username that has active="y". There can be any number of the same username and "n" rows. Appreciate the help in advance.
CodeWranglerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
You can't do it directly, but it's fairly easy to "kludge" this.

1) Make sure the table has a unique value, such as an identity column; add an identity if you need to;
2) Add a persisted, computed column to use in the index.

ALTER TABLE dbo.tablename
ADD active_index AS CASE WHEN active = 'Y' THEN 0 ELSE ident END PERSISTED

CREATE UNIQUE {CLUSTERED|NONCLUSTERED} INDEX index_name ON dbo.tablename ( username, active_index ) WITH ( FILLFACTOR = ..., SORT_IN_TEMPDB = ON ) ON [your_filegroup_name]
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CodeWranglerAuthor Commented:
Thanks a ton!!
0
CodeWranglerAuthor Commented:
just curious, how much will this affect performance. I know you only have limited information about my table and it's design, but strictly from this approach of having a index use a computer column, which might be the performance impact.
0
Scott PletcherSenior DBACommented:
Virtually zilch.  There's some slight overhead to generating an identity column, but most people use one in a table anyway.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.