How to create a not null column in a view

Question

Given a table like:

CREATE TABLE "MyTable" 
(
  "MyColumn" NUMBER NOT NULL
);

I want to create a view like:

CREATE VIEW "MyView" AS
SELECT
    CAST("MyColumn" AS BINARY_DOUBLE) AS "MyColumn"
FROM "MyTable";

Only where the column "MyColumn" is "NOT NULL".

In SQL Server this is pretty straight forward:

CREATE VIEW [MyView] AS
SELECT
    ISNULL(CAST([MyColumn] AS Float), 0.0) AS [MyColumn]
FROM [MyTable];

However the Oracle equivalent results in a "NULL" column:

CREATE VIEW "MyView" AS
SELECT
    NVL(CAST("MyColumn" AS BINARY_DOUBLE), 0.0) AS "MyColumn"
FROM "MyTable";

Is there anyway to force Oracle to mark the view's column as "NOT NULL" in the metadata?

Answer 1

You can't add a not null or check constraint to a view; see this and on the same page 'Restrictions on NOT NULL Constraints' and 'Restrictions on Check Constraints'. You can add a with check option (against a redundant where clause) to the view but that won't be marked as not null in the data dictionary.

The only way I can think to get this effect is, if you're on 11g, to add the cast value as a virtual column on the table, and (if it's still needed) create the view against that:

ALTER TABLE "MyTable" ADD "MyBDColumn" AS
    (CAST("MyColumn" AS BINARY_DOUBLE)) NOT NULL;

CREATE OR REPLACE VIEW "MyView" AS
SELECT
    "MyBDColumn" AS "MyColumn"
FROM "MyTable";

desc "MyView"

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 MyColumn                                  NOT NULL BINARY_DOUBLE

---

Since you said in a comment on dba.se that this is for mocking something up, you could use a normal column and a trigger to simulate the virtual column:

CREATE TABLE "MyTable" 
(
  "MyColumn" NUMBER NOT NULL,
  "MyBDColumn" BINARY_DOUBLE NOT NULL
);

CREATE TRIGGER "MyTrigger" before update or insert on "MyTable"
FOR EACH ROW
BEGIN
    :new."MyBDColumn" := :new."MyColumn";
END;
/

CREATE VIEW "MyView" AS
SELECT
    "MyBDColumn" AS "MyColumn"
FROM "MyTable";

INSERT INTO "MyTable" ("MyColumn") values (2);

SELECT * FROM "MyView";

  MyColumn
----------
  2.0E+000

And desc "MyView" still gives:

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 MyColumn                                  NOT NULL BINARY_DOUBLE

As Leigh mentioned (also on dba.se), if you did want to insert/update the view you could use an instead of trigger, with the VC or fake version.

Answer 2

If you could have a NOT NULL constraint on a view column I believe that a SELECT from the view would then fail if the column in question was NULL. If this is the intent then the following might give you what you're looking for:

CREATE OR REPLACE VIEW some_view AS
  SELECT some_field,
         some_other_field,
         CASE
           WHEN field_of_interest IS NOT NULL
             THEN CAST(field_of_interest AS BINARY_DOUBLE)
             ELSE 1 / 0
         END AS field_of_interest_not_null
     FROM some_table;

Not very attractive, and you get an ugly "ORA-01476: division is equal to zero" message if the ELSE branch of the CASE is taken, but perhaps it's a step on the road to "better".

Share and enjoy.

---

EDIT: If the objective is to only pick up rows where your target column is not null perhaps you could add a WHERE clause to your view, as in:

CREATE OR REPLACE VIEW some_view AS
  SELECT some_field,
         some_other_field,
         CAST(field_of_interest AS BINARY_DOUBLE) AS field_of_interest
    FROM some_table
    WHERE field_of_interest IS NOT NULL;

YMMV.

---

EDIT2: Looking at the SQL Server example, it appears that the ISNULL function is being used to ensure that the column is never NULL. If this is acceptable you could do the following:

CREATE OR REPLACE VIEW some_view AS
  SELECT some_field,
         some_other_field,
         CAST(NVL(field_of_interest, 0.0) AS BINARY_DOUBLE) AS field_of_interest
    FROM some_table
    WHERE field_of_interest IS NOT NULL;

To quote Bullwinkle, "This time fer sure!!!" :-)