Unique Contraint/Index Based On Values

Question

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.

Answer 1

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)

Answer 2

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