CASE Statement in Table Check constraint?
-
Hello...
Is there a way to put a CASE Statement in a table check constraint?
For example, on the table:
CREATE TABLE dbo.Claims
( ClaimID int IDENTITY,
ClaimStatusID int NULL,
DateOpened datetime NOT NULL ,
DateClosed datetime NULL ,
CONSTRAINT FK_ClaimStatus
FOREIGN KEY (ClaimStatusID)
REFERENCES dbo.TableClaimStatus (ClaimStatusID)
)
I would like to do something like this:
ALTER TABLE Claims
ADD CONSTRAINT CKTbl_Claims_DateClosed
CHECK (CASE WHEN ClaimStatusID = 2 THEN DateClosed IS NOT NULL)
The intended effect being that you cannot have a Null DateClosed if ClaimStatusID = 2.
Thanks
Tracie
-
You can just use some ANDs and ORs. E.g.
ALTER TABLE Claims
ADD CONSTRAINT CKTbl_Claims_DateClosed
CHECK (ClaimStatusID IN (0, 1) OR (ClaimStatusID = 2 AND DateClosed IS NOT NULL))
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Thanks... I'm not crazy about the fact that we have to update this constraint if we add any new status id's, but this works now, so we'll give it a try! Thanks again...
-
That was just an example. Use your imagination!
ALTER TABLE Claims
ADD CONSTRAINT CKTbl_Claims_DateClosed
CHECK (ClaimStatusID <> 2 OR (ClaimStatusID = 2 AND DateClosed IS NOT NULL))
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Thanks again...
-
Just to simplify Ryan's solution:
ALTER TABLE Claims
ADD CONSTRAINT CKTbl_Claims_DateClosed
CHECK (ClaimStatusID <> 2 OR DateClosed IS NOT NULL)
_____________
Code for TallyGenerator -
Just in case it could be a problem: the constraint as defined will also disallow nulls in ClaimStatusID.
- declare
@t table(ClaimStatusID int, DateClosed datetime)
- insert
@t values(null,null)
- insert
@t values(2,null)
- insert
@t values(3,null)
- insert
@t values(null,3)
- insert
@t values(5,4)
- select
'all' filter, * from @t
- select
'P and Q' filter, * from @t where ClaimStatusID = 2 and DateClosed is null
- select
'not(P and Q)' filter, * from @t where not(ClaimStatusID = 2 and DateClosed is null)
- select
'not(P) or not(Q)' filter, * from @t where ClaimStatusID <> 2 or DateClosed is not null
- select 'not_or_unknown(P and Q)' filter, * from @t where case when ClaimStatusID = 2 and DateClosed is null then 1 else 0 end = 0
- select
'not(P) or unknown(P) or not(Q)' filter, * from @t where ClaimStatusID <> 2 or ClaimStatusID is null or DateClosed is not null
- --to ilustrate null handling:
- select
'coalesce: exclude null id' filter, * from @t where coalesce(DateClosed-DateClosed, ClaimStatusID) <> 2
- select
'coalesce/cast: include null id' filter, * from @t where coalesce(cast(DateClosed as bit),ClaimStatusID,1) <> 2
- select
'coalesce/nullif: exclude null id' filter, * from @t where coalesce(DateClosed, nullif(ClaimStatusID,2)) is not null
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Well, if we're simplifying...
ALTER TABLE Claims
ADD CONSTRAINT CKTbl_Claims_DateClosed
CHECK (NOT (ClaimStatusID = 2 AND DateClosed IS NULL))
(okay, it's probably not much simpler - if at all!
)Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
select
'not(P and Q)' filter, * from @t where not(ClaimStatusID = 2 and DateClosed is null)
select
'not(P) or not(Q)' filter, * from @t where ClaimStatusID <> 2 or DateClosed is not null
Thought i would point this out even though i sure you are aware. These wwo line are identical.
-
Yes I believe fervently in teaching propositional logic via the means of SQL.
quiz:
In the context of te above batch, are these two logically equivalent? Could they give different results? Discuss.
- select 'not_or_unknown(P and Q)' filter, * from @t where case when ClaimStatusID = 2 and DateClosed is null then 1 else 0 end = 0
- select
'not(P) or unknown(P) or not(Q)' filter, * from @t where ClaimStatusID <> 2 or ClaimStatusID is null or DateClosed is not nullTim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant - select 'not_or_unknown(P and Q)' filter, * from @t where case when ClaimStatusID = 2 and DateClosed is null then 1 else 0 end = 0
-
Yes, I believe De Morgan felt the same.
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Both can be transalated fairly strighforwardly into 'where not(ClaimStatusID = 2 and DateClosed is null)'
--------------------------------------------------------------------------------------------------
select
'not_or_unknown(P and Q)' filter, * from @t where case when ClaimStatusID = 2 and DateClosed is null then 1 else 0 end = 0
select
'not_or_unknown(P and Q)' filter, * from @t where not(ClaimStatusID = 2 and DateClosed is null)--
--------------------------------------------------------------------------------------------------
select
'not(P) or unknown(P) or not(Q)' filter, * from @t where ClaimStatusID <> 2 or ClaimStatusID is null or DateClosed is not null
select
'not(P) or unknown(P) or not(Q)' filter, * from @t where ClaimStatusID <> 2 or DateClosed is not null
select
'not(P) or unknown(P) or not(Q)' filter, * from @t where not(ClaimStatusID = 2 and DateClosed is null)--the or requires at least one to be false which is the same as not both conditions being true
The hard/interesting bit about propositional logic in SQL is of course that its three valued....
-
- select 'not_or_unknown(P and Q)' filter, * from @t where case when ClaimStatusID = 2 and DateClosed is null then 1 else 0 end = 0
- select
'not(P) or unknown(P) or not(Q)' filter, * from @t where ClaimStatusID <> 2 or ClaimStatusID is null or DateClosed is not nullThey are arguably not logically equivalent, but they will always give the same results because is null can never evaluate to unknown, just as you can write a CASE expression which never evaluates to unknown. If DateClosed is null could have an unknown value, then such records would always be excluded by the second query, but would be included in the first query where ClaimStatusID = 2.
Some functions like isnull, coalesce, case with a non-null else value, can be used as 'null-eliminators', in such a way that the a resulting boolean expression never has an unknown value; i.e. it always returns a two-valued type, even if some input has a nullable type.
Other functions can be 'null-introducers', most obviously nullif or case without else. But also:
- select
stuff('a',2,0,'b')
- --
- -- ------
- -- NULL
- select
sum(i) from (select top 0 i from (select 1 i) V) V2
- --
- -- ------
- -- NULL
I think the fact that conversion functions are often '*' -introducers:
select
cast(123 as varchar(2))
- --
- -- ------
- -- *
arises from a messy and underdocumented compromise designed to avoid null-introduction.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant - select 'not_or_unknown(P and Q)' filter, * from @t where case when ClaimStatusID = 2 and DateClosed is null then 1 else 0 end = 0
-
'If DateClosed is null could have an unknown value'
What do you mean by 'could'?
Is what your suggesting is that
where null is null
could/should (?) return as unknown.
The logic of this is unknown = unknown
the condition being tested is 'is unknown unkown'. (if we bracket variaties of the meaning of null i.e missing, not aplicable)This should surely be true.
Or perhaps we cant even say if a null is a null. Not even its nullness is an identifiable quality?
Or while we can say that a null is a null we cant compare it with any logical operator to another null and return true or false. So null cant even be compared with other null.
Insist on Boyce-codd normal form or above and ban isnull and nullif?
should obviously return true
-
- >What do you mean by 'could'? Is what your suggesting that
- >where null is null
- >could/should (?) return as unknown?
Yes, I am imagining that, say, a malicious hacker changed the isnull() function so that it would evaluate to unknown if the input = 5.
>the condition being tested is 'is unknown unkown'. (if we bracket variaties of the meaning of null i.e missing, not aplicable)This should surely be true.
The value of null is null is unknown, yes. But null has no value. The operator is null returns the 3-valued boolean value unknown if its input has no value (is null). Only a sql data type can be null. A boolean SQL expression (like a SQL 'sentence') evaluates to one of true, false, unknown.
>while we can say that a null is a null we cant compare it with any logical operator to another null and return true or false. So null cant even be compared with other null.
We can't compare it using a logical operator because null can only occur inside a SQL datatype, not at the level of logical operations on Boolean expressions. We can compare it directly to another null in an expression like null = null, but of course it evaluates to unknown. The difference between a Boolean expression and a SQL data value is illustrated by these invalid SQL statements:
select isnull(null), null = null --Boolean expression can't be used as a data value
select 1 where (1=1) or null --data value null can't be used as a Boolean expression
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply