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.