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.

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.

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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!

share|improve this answer
    
Yes, union etc require matching datatypes, but they don't require "matching" nullability. –  Colin 't Hart Aug 15 '13 at 8:36

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.