CODEWITHSUNDEEP
javamysqloracle-database
Milkmaid
Milkmaid

Reputation: 1754

Oracle DB relationship between table and view(materialized view)

How to create reference between table and view? I have view which consist of two tables created via UNION (ids of this tables are unique cause of same sequencer). I try to create primary key on view and make reference but its not working for me? Is it even possible to make it like that or with materialized view. Its legacy project with really big data model and a lot of views.

Upvotes: 0

Views: 151

Answers (2)

gabor.harsanyi
gabor.harsanyi

Reputation: 599

According to oracle docs. you can create PK (disabled, nonvalidated) on views but this PK cannot be referenced by a FK because it's not validated

Oracle Database does not enforce view constraints. However, you can enforce constraints on views through constraints on base tables.

You can specify only unique, primary key, and foreign key constraints on views, and they are supported only in DISABLE NOVALIDATE mode. You cannot define view constraints on attributes of an object column.

from http://docs.oracle.com/cd/B28359_01/server.111/b28286/clauses002.htm#SQLRF52163

Upvotes: 1

Lorenz Pfisterer
Lorenz Pfisterer

Reputation: 873

You can't use keys in views. That means

  • you can't set a primary key in the view
  • you can't reference a view in a foreign key

Upvotes: 1

Related Questions