Constraints based on other columns

Question

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.

Answer 1

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

Answer 2

The first line of defence to guard against invalid data getting into your tables is the data types of the columns.

If a process tries to insert or update a column to a value that's out of the data type's range (or NULL if the column does not allow NULLs), the operation will fail immediately without you needing to do any extra work.

Data type selection is one of the most important aspects of table design.

So, since you didn't post a schema, I'm going to construct one based on the information you've provided:

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

Based on this design, the available combinations are already limited to the following:

Test_mode  Active
        0    NULL
        0       0
        0       1
        1    NULL
        1       0
        1       1

Anything other than that will cause an error to be raised. (Which is a good thing.)

---

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.

You've given 4 different ways to arrive at the allowed combination of values (well, sort of). These are very different strategies, with very different implementation behaviours.

I prefer to use what are called declarative constraints. In other words, the table schema and its associated objects limit the allowed values by explicitly declaring what is allowed (or sometimes, what is not allowed). In fact, the column data types themselves are a type of declarative constraint. The closer to the table data the values can be restricted, the easier and more reliably they can be restricted. (In contrast, a non-declarative or active constraint would be implemented by writing a piece of T-SQL, usually either a table trigger, or part of a stored procedure.)

The first 3 of the options can only be implemented by non-declarative means. The last one, however, is declarative, so let's focus on that:

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

This defines what you actually want, which is the allowed combinations of values in the table. Note that the valid combinations are only dependent on column values within the same row. This is important, because it determines which mechanism(s) can be used to implement the constraint.

In this case, we can use a CHECK constraint, which is a true/false test that determines whether a row is valid or invalid based on a row's column values¹. If the test fails, the operation that attempted to change the row will fail with an error.

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

You'll note that I've constructed the predicate such that it will continue to work even if Test_mode is actually a (non-nullable) integer type. The IS NOT NULL part is required because CHECK constraints permit rows where the predicate evaluates to undefined.

_{¹ They can be used to do checking outside of the current row, but this is a bad practice, and I'm not going to get into that here. Use a trigger instead.}

Answer 3

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