6

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.

1

2 Answers 2

8

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)
3
  • Geez, how did I not think of this? This is definitely the best answer.
    – Jon Seigel
    Jul 30, 2012 at 21:46
  • +1, very concise. Also the comment about UDF reliability is very important. Thanks.
    – NoChance
    Jul 30, 2012 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. Jul 31, 2012 at 14:10
0

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

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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