un-versed
6/14/2018 - 1:57 PM

Conditional Unique Constraint

Creates an unique constraint with a condition.

uniqueConditional
content_copyfile_download
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));