如何在视图中创建非空列 [英] How to create a not null column in a view

问题描述

给出一个表格：

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

我想创建一个视图：

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

仅在MyColumn列为NOT NULL时。

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

在SQL Server中，这是很直接的：

In SQL Server this is pretty straight forward:

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

但是，等效的Oracle会产生一个NULL列：

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";

是否还有强制Oracle在元数据中将视图的列标记为NOT NULL / p>

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

推荐答案

您不能向视图添加非空或检查约束;请参阅此并在同一页面上的限制on NOT NULL约束和检查约束的限制。您可以向视图添加一个带有检查选项（对于冗余where子句）的，但不会标记为 not null 在数据字典。

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.

我可以想到得到这种效果的唯一方法是，如果你在11g，添加铸造值作为一个虚拟列和表（如果仍然需要）创建视图：

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

---

因为你在dba.se的评论中说这是为了嘲笑什么，使用正常列和触发器来模拟虚拟列：

---

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

并且 descMyView仍然提供：

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

正如Leigh提到的（也在dba.se上），如果你想插入/更新视图，你可以使用而不是触发器，使用VC或伪版。

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.

这篇关于如何在视图中创建非空列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！