SQL 在 Oracle 中引用视图的外键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3833150/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Foreign key referencing a view in Oracle
提问by echoblaze
I'm attempting to reference a view with a foreign key but I am getting this error:
我正在尝试使用外键引用视图，但出现此错误：
"Error: ORA-02270: no matching unique or primary key for this column-list"
“错误：ORA-02270：此列列表没有匹配的唯一键或主键”
However, I have created a primary key on this view and verified it in the Constraints tab in TOAD.
但是，我在此视图上创建了一个主键，并在 TOAD 的“约束”选项卡中对其进行了验证。
This is the table I'm attempting to create:
这是我试图创建的表：
CREATE TABLE QUESTION
(
QUESTION_ID INTEGER not null,
CREATED_USER_ID INTEGER not null,
CONSTRAINT PK_QUESTION PRIMARY KEY (QUESTION_ID),
CONSTRAINT FK_USER
FOREIGN KEY (CREATED_USER_ID)
REFERENCES SOME_VIEW(VIEW_ID)
);
SOME_VIEW is a view based on another view which points to the employee table in another schema.
SOME_VIEW 是基于另一个视图的视图，该视图指向另一个模式中的员工表。
采纳答案by Kirill Leontev
Regardless the possibility of creating foreign keys to views, it is indeed not the best idea to implement.
不管为视图创建外键的可能性如何，这确实不是实现的最佳想法。
Database views were designed to let user comfortably query some data he needs, but at the same time to serve as a security barrier, to conceal all database structure, including tables, data constraints in tables, and, yes, also table cross-references.
数据库视图旨在让用户轻松查询他需要的一些数据，但同时作为安全屏障，隐藏所有数据库结构，包括表、表中的数据约束以及表交叉引用。
So, a good practice to me would be to reference an existing table from a your new one, despite its residence in other scheme.
因此，对我来说，一个好的做法是从您的新表中引用现有表，尽管它位于其他方案中。
回答by AMG
The best you can do is to implement SOME_VIEW as a Materialized view, since you are pointing to another schema. Then you can alter the materialized view to add a primary key and even refer to this view from a foreing key.
您能做的最好的事情是将 SOME_VIEW 实现为物化视图，因为您指向另一个模式。然后，您可以更改物化视图以添加主键，甚至可以从前键引用此视图。
This is the documentation you should read: https://docs.oracle.com/cd/A97630_01/server.920/a96567/repmview.htm
这是您应该阅读的文档：https: //docs.oracle.com/cd/A97630_01/server.920/a96567/repmview.htm
回答by igelkott
The foreign key against a view is probably what's causing the trouble.
针对视图的外键可能是导致问题的原因。
