Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table that has the following column definitions:

ID (INT, PK)
Name (VarChar)
Active (Bit)
Bunch_of (Other_columns)

Question: I want to have a Constraint on Name/Active such that we can only have 1 record with a given Name that is Active (Active = 1), but we could have many records that have the same Name that are Inactive (Active = 0). Is this possible?

I've tried the normal Unique constraints, but they limit the dataset to only allow 1 record with a given Name to be Inactive.

share|improve this question

2 Answers 2

up vote 7 down vote accepted

To enforce this declaratively in 2005 you can use an indexed view.

CREATE VIEW dbo.ActiveNames
WITH SCHEMABINDING
AS
SELECT Name
FROM  dbo.YourTable
WHERE Active = 1

GO

CREATE UNIQUE CLUSTERED INDEX UQ_ActiveNames ON dbo.ActiveNames(Name)
share|improve this answer
    
Geez, how did I not think of this? This is definitely the best answer. –  Jon Seigel Jul 30 '12 at 21:46
    
+1 for above answer. –  njvds Jul 30 '12 at 22:02
    
+1, very concise. Also the comment about UDF reliability is very important. Thanks. –  Emmad Kareem Jul 30 '12 at 23:32
    
Luckily, I was able to upgrade to SQL 2008 and use a Filtered Index. However, if that was not an option, this would definitely be the approach I'd use. –  Onion-Knight Jul 31 '12 at 14:10

This can be done using CHECK constraint which execute function. Didn't test it completely but it works fine in my initial tests. Here is working eg.

CREATE function fn_checkNA(
@name varchar(6) )
 Returns smallint
 as 
  Begin
   Declare @c smallint
    if (select count(1) from dbo.chktest where Name = @name and Active = 1) > 1
     Set @c = 1
    else
     set @c = 0
   Return @c
  End

Create table :

create table chktest( ID INT primary key,Name VarChar(6) CONSTRAINT chkname CHECK(dbo.fn_checkNA(Name) = 0)
,Active Bit )

Now try to run below insert.

 insert into chktest values(1,'James', 0)
  insert into chktest values(2,'James',1)
  insert into chktest values(3,'James',0)
  insert into chktest values(4,'James',1)  --- This fails

Please test thoroughly before using this method.

NJ

share|improve this answer
4  
Using UDFs in check constraints can fail under snapshot isolation or multirow updates –  Martin Smith Jul 30 '12 at 21:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.