oracle添加表字段非空,oracle – 如何在视图中创建非空列

您不能向视图添加非null或检查约束;请参阅

this并在同一页面上“限制NOT NULL约束”和“限制检查约束”.您可以向视图添加with check选项(针对冗余where子句),但在数据字典中不会将其标记为非null.

我想到这个效果的唯一方法就是,如果你在11g上,将转换值作为虚拟列添加到表中,并且(如果仍然需要)创建视图：

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的评论中说这是用来模拟某些东西,你可以使用普通列和触发器来模拟虚拟列：

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

并且desc“MyView”仍然给出：

Name Null? Type

----------------------------------------- -------- ----------------------------

MyColumn NOT NULL BINARY_DOUBLE

正如Leigh所提到的(同样在dba.se上),如果你确实想要插入/更新视图,你可以使用VC或假版本而不是触发器.