Constraints based on other columns

Is it possible to limit what values are allowed in a column based off of other values in the row?

For example, my table:

ID  Test_mode  Active
--  ---------  ------
1   1          Null
2   0          1
3   1          0

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

OR

If Test_mode is 1 not allow insertion/update of Active

OR

Throw some kind of error if Test_mode is 1 and an insertion/update of Active is attempted.

Active can only be NULL, 1, 0, AND only 1 with Test_mode as 0.

I hope this makes sense, if not let me know and I'll update the question.

First of all, Welcome to dba.stackexchange.com and thanks for your post !!

Is it possible to limit what values are allowed in a column based off of other values in the row.

Yes using CHECK CONSTRAINTS as described here

Example :

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

If Test_mode is 1 not allow insertion/update of Active --OR--
Throw some kind of error if Test_mode is 1 and an insertion/update of Active is attempted.

Use TRY/CATCH as described here

StackExchange Database Administrators Q#42469, answer score: 9