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 = 1), but we could have many records that have the same
Name that are
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
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)
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.