?
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
?
359 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 69

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 69

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

Major Serverless Shift

Comparison of major players like AWS, Microsoft Azure, IBM Bluemix, and Google Cloud Platform

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

765 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