Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2014-07-30
4
Medium Priority
?
376 Views
Last Modified: 2014-08-04
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.
0
Comment
Question by:CodeWrangler
  • 2
  • 2
4 Comments
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 40230097
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
 

Author Comment

by:CodeWrangler
ID: 40239513
Thanks a ton!!
0
 

Author Comment

by:CodeWrangler
ID: 40239518
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40239570
Virtually zilch.  There's some slight overhead to generating an identity column, but most people use one in a table anyway.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question