您不能向視圖添加非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或假版本而不是觸發器.