Mohamed Houri’s Oracle Notes

October 20, 2017

DDL(constraint), DML(predicate) and SQL CASE

Filed under: SQL — hourim @ 7:36 pm

Check and integrity constraints are DDL (Data Definition Language) operations. They consider TRUE and NULL alike. Query predicates are DML (Data Manipulation Language) operations. They consider FALSE and NULL alike.

Did you spot the difference?

In this article Jonathan Lewis turns this to:

  • DDL predicate allows a row if it does not evalute to FALSE – which means it is allowed to evaluate to TRUE or NULL.
  • DML predicate returns a row if it evaluates to TRUE – which means it will not return a row if it evaluates to FALSE or NULL.

In this article I am going to show that using the SQL CASE statement we can make DDL and DML predicates behave identically.

The standard boolean logic difference between DDL and DML predicates having been already explained (see this and this excellent book) I am not going to repeat that demonstration here. But, for simplicity’s sake, I am going to reuse a model from one of those articles as shown below:

create table t1 (v1 varchar2(1));
alter table t1 add constraint t1_ck_v1 check (v1 in ('a','b','c'));
insert into t1 values ('a');
insert into t1 values ('b');
insert into t1 values ('c');

The t1_ck_v1 DDL check constraint is supposed to prevent insertions into t1 table of any value that doesn’t match ‘a’ or ‘b’ or ‘c’. But since DDL check and integrity constraint considers TRUE and NULL alike, we are allowed to insert a null value in t1 as the following demonstrates:

SQL> insert into t1 values (null);
1 row created.

It suffices to change the check constraint t1_ck_v1 definition and nulls will definitively be banned from entering into t1 table as shown below:

truncate table t1;
alter table t1 drop constraint t1_ck_v1;

alter table t1 add constraint t1_ck_v1 check
    (case when v1 in ('a','b','c') then 1 else 0 end = 1);

insert into t1 values ('a');
insert into t1 values ('b');
insert into t1 values ('c');

SQL> insert into t1 values (null);
insert into t1 values (null)
*
ERROR at line 1:
ORA-02290: check constraint (C##MHOURI.T1_CK_V1) violated

In contrast to the first created check constraint the new refactored one has successfully pre-empted a null value from being inserted into t1 table (we will see shortly why).

The above demonstrates that using the CASE statement we made a DDL predicates considering NULL and FALSE alike while they are originally implemented to treat TRUE and NULL alike. Let’s now check how the CASE statement works with a DML predicate:

SQL> select count(1) from t1;

  COUNT(1)
----------
         3

SQL> select count(1) from t1
     where
        ( case when v1 in ('a','b','c') then 1 else 0 end = 1);

  COUNT(1)
----------
         3

Indeed the case statement works the same way in both DML and DDL predicates.

And now the legitimate question is why does the CASE statement make the check constraint, which is a DDL predicate, consider FALSE and NULL alike?
The answer can be found in the Oracle official documentation:

“In a searched CASE expression, Oracle searches from left to right until it finds an occurrence of condition that is true, and then returns return_expr. If no condition is found to be true, and an ELSE clause exists, Oracle returns else_expr. Otherwise, Oracle returns null”

Thanks to the ELSE clause of the CASE-WHEN combination we transformed a NULL possibility into a NOT NULL value (0 in the current situation). This is why we should always forsee an ELSE into a CASE statement. Should we have ommited the ELSE clause in the last t1_ck_v1 we would have then allowed null values to be inserted into t1 table as shown below:

truncate table t1;
alter table t1 drop constraint t1_ck_v1;

alter table t1 add constraint t1_ck_v1 check
    (case when v1 in ('a','b','c') then 1 end = 1);

SQL> insert into t1 values(null);
1 row created.

Furthermore ommitting the ELSE in the CASE allows not null values not in the triplet (‘a’,’b’,’c’) to be inserted into t1 table as shown below:

SQL> insert into t1 values ('z');
1 row created.

SUMMARY
Protect yourself against the always threatening NULL by setting your column not null whenever possible. Bear in mind that the CASE statement, when used with its ELSE part, can not only make DDL predicate treats FALSE and NULL alike, but it behaves the same way during DML and DDL predicate.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

%d bloggers like this: