Way to explicitely have a not-null column in a view

nulloracleoracle-11g-r2view

If I create a view in Oracle, the column definition's not null characteristic is inherited from the base table:

create table tq84_t (
  col_1   number,
  col_2   varchar2(15) not null
);

create view tq84_v as select col_1, col_2 from tq84_t;

desc tq84_v;

will print

Name            Null?    Type
--------------- -------- -----------------------------------------
COL_1                    NUMBER
COL_2           NOT NULL VARCHAR2(15)

Now, if the view's select statement contains a union all, it seems as though that characteristic is not inherited anymore:

drop view tq84_v;

create view tq84_v as 
  select col_1, col_2 from tq84_t where col_1 < 10 union all 
  select col_1, col_2 from tq84_t where col_2 > 20;

desc tq84_v;

prints:

Name            Null?    Type
--------------- -------- -----------------------------------------
COL_1                    NUMBER
COL_2                    VARCHAR2(15)

This is a bit unfortunate for my purposes. So, is there a way to explicitely state that a column be not null for a view in Oracle?

Note: I obviously used a simplified view defintion to demonstrate the problem, and I could re-write the view definition without using union all. Yet, my actual view defintion is much more complicated, requiring the union all.

Best Answer

From the documentation:

You can specify only unique, primary key, and foreign key constraints on views. However, you can define the view using the WITH CHECK OPTION clause, which is equivalent to specifying a check constraint for the view.

So it seems that there is no way to hint to the RDBMS that the underlying data (after the UNION ALL inline view is created) is NOT NULL.

This all seems a bit silly, given the UNION/UNION ALL/INTERSECT/MINUS statements all require matching datatypes!

Related Question