Foreign key referencing a view in Oracle

Question

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.

user_10106568 · Answer

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.

This is the documentation you should read: https://docs.oracle.com/cd/A97630_01/server.920/a96567/repmview.htm

Foreign key referencing a view in Oracle

3 Answers