Establishing Refential Integrity With Materialized Views In Oracle

RI(Refential Integrity )  is established with FK(foreign key) and PK(primary key) in Oracle. If you do not have a primary key on a column you are not allowed to create a FK. If your table  has a non-unique column, how can you create a FK? Example below shows how to establish RI with MVs: 
 

SQL> create table a(i number);

 

Table created

 

SQL> insert into a values(1);

 

1 row inserted

 

SQL> insert into a values(1);

 

1 row inserted

 

SQL> create table b(k number);

 

Table created

 

SQL> alter table b add constraint fk_ba foreign key(k) references a(i);

 

alter table b add constraint fk_ba foreign key(k) references a(i)

 

ORA-02270: no matching unique or primary key for this column-list

 

 

SQL> create materialized view mv_a

  2  parallel

  3  build immediate as

  4  select distinct i from a;

 

Materialized view created

 

SQL> alter materialized view mv_a add constraint pk_mv_a primary key(i);

 

Materialized view altered

 

SQL> alter table b add constraint fk_ba foreign key(k) references mv_a(i);

 

Table altered

 

SQL> insert into b values(1);

 

1 row inserted

 

SQL> insert into b values(2);

 

insert into b values(2)

 

ORA-02291: integrity constraint (HR.FK_BA) violated - parent key not found

 

SQL>