Foreign key referencing a view in Oracle

Question

I try to reference the view using a foreign key, but I get this error:

"Error: ORA-02270: there is no corresponding unique or primary key for this column list"

However, I created a primary key on this view and checked it on the "Constraints" tab in TOAD.

This is the table I'm trying 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 that points to an employee table in a different schema.

Answer 1

Regardless of the ability to create foreign keys for viewing, this is really not the best idea to implement.

The database views were designed in such a way as to allow the user to comfortably request some data that he needs, but at the same time serve as a security barrier, hide the entire database structure, including tables, data restrictions in tables and, yes, recommendations.

So, it would be good practice for me to refer to an existing table from your new one, despite its place of residence in another scheme.

Answer 2

It seems that the foreign key to the view can cause problems.