在Oracle中引用一个视图的外键 [英] Foreign key referencing a view in Oracle

问题描述

错误：ORA-02270：没有匹配的唯一键或主键对于这个列表

但是，我在这个视图上创建了一个主键，并在TOAD的Constraints选项卡中进行了验证。

这是我试图创建的表：

  CREATE TABLE QUESTION 
（
 QUESTION_ID INTEGER不为空，
 CREATED_USER_ID INTEGER不为空，
 CONSTRAINT PK_QUESTION PRIMARY KEY（QUESTION_ID），
 CONSTRAINT FK_USER 
 FOREIGN KEY （CREATED_USER_ID）
 REFERENCES SOME_VIEW（VIEW_ID）
）; 
  

SOME_VIEW是基于另一个视图指向另一个架构中的employee表的视图。无论创建外键到视图的可能性，它的确不是最好的实现方法。数据库视图旨在让用户舒适地查询他所需要的一些数据，同时作为安全屏障隐藏所有的数据库结构，包括表，数据限制在表中，是的，也是表交叉引用。

因此，对我来说，一个好的做法是从现有的表中引用新表，尽管它位于其他方案中。

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"

However, I have created a primary key on this view and verified it in the Constraints tab in 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.

解决方案

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.

这篇关于在Oracle中引用一个视图的外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！