[SOLVED] Make database column value dependant on another column

Make database column value dependant on another column

Title might be misleading, so let me elaborate.

I have two columns in my table, A & B. Both are bit values.

What I need is to somehow make it so that if A = TRUE then B MUST also be TRUE. But if A = FALSE then B can be either TRUE or FALSE.

It does not need to happen on DB level, but I do require some sort of constraint to make sure it is always enforced, as sometimes rows will be entered manually.

I am working in t-sql.

I have tried to make a constraint, but I am not experienced enough to figure out a way that works how i need it to.

Solution

A CONSTRAINT is what you need. You can simply check that both are TRUE (1), or A is FALSE (0):

CREATE TABLE #YourTable (A bit NOT NULL,
                         B bit NOT NULL,
                         CONSTRAINT CK_AB_True CHECK ((A = 'True' AND B = 'True') OR A = 'false'));

Then we can easily test with the 4 different scenarios:

INSERT INTO #YourTable (A,
                        B)
VALUES(1,1); --Success
GO
INSERT INTO #YourTable (A,
                        B)
VALUES(0,1); --Success
GO
INSERT INTO #YourTable (A,
                        B)
VALUES(0,0); --Success
GO
INSERT INTO #YourTable (A,
                        B)
VALUES(1,0); --Failure
GO
SELECT *
FROM #YourTable;
GO
DROP TABLE #YourTable;

All INSERT apart from the row where A is 0 and B is 1.