Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is it possible to limit what values are allowed in a coulumn 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.

share|improve this 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. –  Archangel33 May 20 '13 at 14:44
add comment

2 Answers 2

up vote 3 down vote accepted

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

Is it possible to limit what values are allowed in a coulumn 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

share|improve this answer
    
Fiddle-2 –  ypercube May 18 '13 at 11:00
    
My bad ... corrected ... was late night ! –  Kin May 18 '13 at 12:59
    
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 :) –  Archangel33 May 20 '13 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. –  Kin May 20 '13 at 14:09
add comment

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

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.

share|improve this answer
    
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. –  Archangel33 May 20 '13 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. –  Jon Seigel May 20 '13 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. –  Archangel33 May 20 '13 at 15:13
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.