Restrições baseadas em outras colunas

Em primeiro lugar, bem-vindo ao dba.stackexchange.com e obrigado por sua postagem !!

É possível limitar quais valores são permitidos em uma coluna com base em outros valores na linha.

Sim, usando CHECK CONSTRAINTS conforme descrito aqui

Exemplo :

create table myTable (ID int identity(1,1)
                        , Test_mode int
                        , Active int 
                        )
go

-- Active can only be NULL, 1, 0, AND only 1 with Test_mode as 0.
ALTER TABLE myTable WITH CHECK ADD 
   CONSTRAINT ck_active CHECK (active IS NULL OR active IN (1, 0)) 
   go

-- some test data
insert into myTable (test_mode, Active) values (1, null)
insert into myTable (test_mode, Active) values (0, null)
insert into myTable (test_mode, Active) values (1, 0)
insert into myTable (test_mode, Active) values (0, 1)
insert into myTable (test_mode, Active) values (1, 1)

select * from myTable

-- Is there a way to either change the value of Test_mode to 0 if a 1 is inserted into Active

update myTable
set Test_mode = case when Active = 1 then  0
        else Test_mode 
        end
where Active = 1

Se Test_modefor 1 não permite inserção/atualização de Active --OU-- Lança algum tipo de erro se Test_mode for 1 e uma inserção/atualização de Active é tentada.

Use TRY/CATCH conforme descrito aqui

A primeira linha de defesa para evitar que dados inválidos entrem em suas tabelas são os tipos de dados das colunas.

Se um processo tentar inserir ou atualizar uma coluna para um valor que esteja fora do intervalo do tipo de dados (ou NULLse a coluna não permitir NULLs), a operação falhará imediatamente sem que você precise fazer nenhum trabalho extra.

A seleção do tipo de dados é um dos aspectos mais importantes do design da tabela.

Então, como você não postou um esquema, vou construir um com base nas informações que você forneceu:

CREATE TABLE [dbo].[Tests]
(
    ID int IDENTITY(1, 1) PRIMARY KEY,
    Test_mode bit NOT NULL, /* Based on only seeing 0/1. Maybe tinyint? */
    Active bit NULL
);

Com base neste design, as combinações disponíveis já se limitam ao seguinte:

Test_mode ativo
        0 NULO
        0 0
        0 1
        1 NULO
        1 0
        1 1

Qualquer coisa diferente disso fará com que um erro seja gerado. (O que é uma boa coisa.)

Existe uma maneira de alterar o valor de Test_mode para 0 se um 1 for inserido em Active

OU

Se Test_mode for 1 não permite inserção/atualização de Active

OU

Lance algum tipo de erro se Test_mode for 1 e uma inserção/atualização de Active for tentada.

Ativo só pode ser NULL, 1, 0 e apenas 1 com Test_mode como 0.

Você deu 4 maneiras diferentes de chegar à combinação permitida de valores (bem, mais ou menos). Estas são estratégias muito diferentes , com comportamentos de implementação muito diferentes.

Prefiro usar o que chamamos de restrições declarativas . Em outras palavras, o esquema da tabela e seus objetos associados limitam os valores permitidos declarando explicitamente o que é permitido (ou às vezes, o que não é permitido). Na verdade, os próprios tipos de dados da coluna são um tipo de restrição declarativa. Quanto mais próximos dos dados da tabela os valores puderem ser restritos, mais fácil e confiável eles poderão ser restritos. (Em contraste, uma restrição não declarativa ou ativa seria implementada escrevendo uma parte do T-SQL, geralmente um gatilho de tabela ou parte de um procedimento armazenado.)

As 3 primeiras opções só podem ser implementadas por meios não declarativos. O último, no entanto, é declarativo, então vamos nos concentrar nisso:

Activesó pode ser NULL, 1, 0, E apenas 1 com Test_mode0.

Isso define o que você realmente deseja, que são as combinações de valores permitidas na tabela. Observe que as combinações válidas dependem apenas dos valores das colunas dentro da mesma linha . Isso é importante porque determina qual(is) mecanismo(s) pode(m) ser usado(s) para implementar a restrição.

Nesse caso, podemos usar uma CHECKrestrição , que é um teste verdadeiro/falso que determina se uma linha é válida ou inválida com base nos valores ¹ da coluna de uma linha . Se o teste falhar, a operação que tentou alterar a linha falhará com um erro.

ALTER TABLE [dbo].[Tests] WITH CHECK
    ADD CONSTRAINT CC_Tests_TestMode_Active
        CHECK ((Test_mode != 0) OR ((Active IS NOT NULL) AND (Active = 1)));

Você notará que construí o predicado de forma que ele continue a funcionar mesmo se Test_modefor realmente um tipo inteiro (não anulável). A IS NOT NULLparte é necessária porque as CHECKrestrições permitem linhas em que o predicado é avaliado como undefined.

_{¹ Eles podem ser usados ​​para verificar fora da linha atual, mas essa é uma prática ruim e não vou entrar nisso aqui. Em vez disso, use um gatilho.}

CREATE UNIQUE INDEX [UNQ_IndexName]
  ON [dbo].[Table]([Column])
  WHERE   ([Status] in( 'A','D' ) );