Constraint: Table xGUID and Deleted

Hi,
I have a database table which contains a column with a GUID. In addition I
have column that contains a Deleted flag. I now would like to create a
contraint that makes sure that there is only one undeleted GUID.
What is the expression for this?
Thanks for any advice
Pete
Pete SmithProbably best to maintain this via a insert/update trigger.
create trigger x on tbl for insert, update
as
if exists (select guid from tbl t where guid in (select guid from inserted i
where i.flag <> 'D') and t.flag <> 'D' group by GUID having count(*) > 1)
begin
rollback tran
raiserror ('multiple undeleted', 16, -1)
end
go
"Pete Smith" wrote:

> Hi,
> I have a database table which contains a column with a GUID. In addition I
> have column that contains a Deleted flag. I now would like to create a
> contraint that makes sure that there is only one undeleted GUID.
> What is the expression for this?
> Thanks for any advice
> Pete
> --
> Pete Smith
>
>|||You can do this using an indexed view
create view UndeletedGUIDs
with schemabinding
as
select GUID
from dbo.mytable
where flag<>'d'
create unique clustered index IX_UndeletedGUIDs on UndeletedGUIDs(GUID)