# 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.

• I Just wanted to explain why i accepted the answer I did. Jon and Kin's answers were both extremely helpful and informative. Kins answer links to another page that has a very good explanation of constraints. May 20, 2013 at 14:44

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

• Thank you for your Reply. I had no Idea about Check Constraints. the Links were very helpful. The update though, Would have to be explicitly defined every time I wanted to insert a value? I need to set this up so that whenever one of our developers writes a Stored Procedure they wont have (Although they should!) to know that this is the way to update the table. They should just be able to `Update myTable Set Active = 1` and the `test_mode` be updated as well. If this is the only way though, then so be it! Thank you :) May 20, 2013 at 13:57
• @Archangel33 Yes .. that is your best bet to do an update. You can wrap that in the SP, so you dont have to do it everytime. Also check the TRY/CATCH method for error generating and reporting back to the user. Also, if you feel that I have answered your question, please mark it as an answer. May 20, 2013 at 14:09
• `Active int [...] CHECK (active IS NULL OR active IN (1, 0))` - this seems like pointlessly bad design, as it amounts to saying 'please emulate a boolean `bit`, but store it in a less efficient type and require me to remember that I must manually constrain its allowed values` Apr 18, 2017 at 13:23

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 `NULL`s), 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 values1. If the test fails, the operation that attempted to change the row will fail with an error.

``````ALTER TABLE [dbo].[Tests] WITH CHECK
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`.

1 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.

• Sorry About schema I will remember to post those in future Questions. Using a `Check Constraint` seems like the most appropriate solution. Are constraints checked after an update statement? What I mean is, Will an `UPDATE` like `UPDATE [dbo].[Tests] SET Active=1,test_mode=0 WHERE test_mode=1` fail a constraint or is the state of the row only checked after the `UPDATE` completes. I will do some testing on this to make sure but I have a feeling that this will work out. May 20, 2013 at 14:33
• @Archangel33: Check constraints only validate the final state of the row. They do not validate whether or not you're allowed to change from one state to another. The `UPDATE` statement you mention will succeed, as the final state is allowed. If you want to disallow that change in state, you have to use a trigger to do it. (Your initial requirements were somewhat unclear.) Note: I would recommend using both a check constraint and a trigger if those are your requirements. May 20, 2013 at 14:40
• Thank you. Yes I will define my requirements better next time. Nope that Is perfect I'm glad to hear that that `UPDATE` will work. I was thinking that it might cause an invalid state while transitioning from current state to desired state. May 20, 2013 at 15:13
``````CREATE UNIQUE INDEX [UNQ_IndexName]
ON [dbo].[Table]([Column])
WHERE   ([Status] in( 'A','D' ) );
``````
• While this doesn't match the example presented by the OP, it does indeed represent an example of values in one column limiting values in another column. If `Status` is anything but `A` or `D`, then `Column` would not have to be unique. Aug 8, 2017 at 17:04