Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Foreign key referencing a view in Oracle

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.

like image 857
echoblaze Avatar asked Sep 30 '10 17:09

echoblaze


People also ask

Can a foreign key reference a view?

You can't reference a view in a foreign key.

How can I find out what foreign key constraint references a table in Oracle?

You can check for foreign key constraint errors by looking at the dba_constraints and the dba_cons_columns views. Using the cascade constraints clause in a drop table will force a cascade delete to occur in all child tables. Oracle also has the drop table if exists feature.

Can a foreign key reference a table?

A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table. The table with the foreign key is called the child table, and the table with the primary key is called the referenced or parent table.

Can a foreign key reference multiple tables Oracle?

You can't.


1 Answers

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.

like image 126
Kirill Leontev Avatar answered Oct 08 '22 22:10

Kirill Leontev