How can I achieve a unique constraint with two fields?
Assuming the following table definition

 CREATE TABLE T
 (
 Name VARCHAR(50) NOT NULL,
 IsDeleted BIT NOT NULL
 )


Then you can achieve this with a unique index filtered to only include those Names that you wish to apply the constraint on.

CREATE UNIQUE INDEX ix ON T(Name) WHERE IsDeleted = 'false'