Creates an unique constraint with a condition.
CREATE FUNCTION checkCpfExistIsActive(
@cpfCnpj VARCHAR(20)
) RETURNS INT AS BEGIN
DECLARE @ret INT;
SELECT @ret = COUNT(*) FROM clienteFichas WHERE cpfCnpj = @cpfCnpj AND ativo = 1;
RETURN @ret;
END;
GO
ALTER TABLE clienteFichas
ADD CONSTRAINT checkCpfAndStatus CHECK (NOT (dbo.checkCpfExistIsActive(cpfCnpj) > 1 AND ativo = 1));