3

I created the Oracle Materialized View below:

CREATE MATERIALIZED VIEW MyMV
REFRESH COMPLETE ON DEMAND
AS

SELECT t1.*
  FROM table1 t1, table2 t2 where t1.id=t2.id;

The table1 has a primary key and the MV was created succesfully but the primary key was not created in the materialized view table.

Is there any other way to create MVs with primary keys?

Ernesto Rodriguez
  • 257
  • 2
  • 9
  • 26

1 Answers1

7

it's because your materialized view is based on two tables, if you create your view based on a single table with a primary key, then the primary key is created on you Materialized view. You can still create the index afterwards if you need one:

SQL> create table t1(id number);

Table created.

SQL> create table t2(id number);

Table created.

SQL> alter table t1 add primary key (id);

Table altered.

SQL> alter table t2 add primary key (id);

Table altered.

SQL> CREATE MATERIALIZED VIEW MyMV
REFRESH COMPLETE ON DEMAND
AS
SELECT t1.*
  FROM t1, t2 where t1.id=t2.id;  2    3    4    5

Materialized view created.

SQL> create unique index myindex on MyMV(id);

Index created.

EDIT

create a primary key instead of the unique index:

SQL> alter materialized view MyMV add constraint PK_ID primary key (id);

Materialized view altered.

SQL> alter table t3 add constraint FK_TABLE3_MyMV foreign key (id) references MyMV (id);

Table altered.
Cyrille MODIANO
  • 2,246
  • 2
  • 21
  • 33
  • But I need a primary key because I would like to add a new foreign key to this MV table. In that case I need to create the primary key instead of index – Ernesto Rodriguez Sep 19 '17 at 13:22
  • This is a primary key. When you use the primary key syntaxe when creating a table it create a unique index in the background, test it. – Cyrille MODIANO Sep 19 '17 at 13:51
  • When I try to create the FK `"alter table table3 add constraint FK_TABLE3_MyMV foreign key (id) references MyMV (id);"` I got the error `"ORA-02270: no matching unique or primary key for this column-list"`. If I alter the table creating the primary key works fine – Ernesto Rodriguez Sep 19 '17 at 14:06
  • Is there any way to "clone" all constraints from original table? – Sergio A. Jan 29 '20 at 13:10