Skip to Main Content
  • Questions
  • Synchronous refresh in mview ORA-31922: Foreign key must contain partition key in table



Question and Answer

Chris Saxon

Thanks for the question, Rajeshwaran.

Asked: February 13, 2020 - 3:51 pm UTC

Answered by: Chris Saxon - Last updated: February 18, 2020 - 11:41 am UTC

Category: Database Development - Version:

Viewed 100+ times

You Asked


Here is my testcase which got failed during Synchronous refresh in mview.

create table products as
select rownum as prod_id,
 object_name as prod_name,
 object_type as prod_category,
 object_id as prod_category_id,
 data_object_id as prod_subcategory_id,
 round(dbms_random.value(1,2000)) as prod_min_price,
 round(dbms_random.value(1,4000)) as prod_max_price,
 'Total' as prod_total,
 to_date('01/01/2010','mm/dd/yyyy')+rownum as effective_from,
 add_months(to_date('01/01/2010','mm/dd/yyyy')+rownum,3) as effective_to,
 status as prod_status
from all_objects ;
alter table products
add constraint prod_pk
primary key(prod_id);

create table customers as
select rownum as cust_id,
   object_name as cust_fname,
   subobject_name as cust_lname,
   decode(mod(rownum,2),0,'M','F') gender,
   to_char(created,'YYYY') as cust_birth_year,
   'Single' as cust_marital_status,
   dbms_random.string('A',30) as cust_street_address,
   dbms_random.value(1,10000) as cust_postal_code,
   dbms_random.string('E',5) as cust_city,
   dbms_random.string('C',5) as cust_state_province,
   round(dbms_random.value(1,999)) as cust_phone_no,
   object_name||'' as cust_email,
   'CustomerTotal' as cust_total,
   to_date('01/01/2010','mm/dd/yyyy')+rownum as cust_effective_from,
   add_months(to_date('01/01/2010','mm/dd/yyyy')+rownum,3) as cust_effective_to
from all_objects ;

alter table customers
add constraint cust_pk
primary key(cust_id);

create table sales 
partition by range( quantity_sold )
( partition p1 values less than (6),
  partition p2 values less than (11) )
select mod(rownum,10000)+1 as prod_id,
  mod(rownum,1000)+1 as cust_id,
  +mod(rownum,4000) as time_id,
  mod(rownum,10) as quantity_sold,
  round(dbms_random.value(1,5000)) as amount_sold
from all_objects ;

alter table sales
add constraint fk_sales_prod
foreign key(prod_id)
references products
modify prod_id not null;

alter table sales
add constraint fk_sales_cust
foreign key(cust_id)
references customers
modify cust_id not null;

create materialized view sales_mv
partition by range( quantity_sold )
( partition p1 values less than (6),
  partition p2 values less than (11) )
build immediate 
refresh on demand using trusted constraint  
select p.prod_id,p.prod_name,
    c.cust_id,c.cust_fname, s.quantity_sold,
  sum(s.quantity_sold), count(s.quantity_sold),
from products p, sales s,
   customers c
where p.prod_id = s.prod_id
and c.cust_id = s.cust_id
group by p.prod_id,p.prod_name,
    c.cust_id,c.cust_fname, s.quantity_sold;
create materialized view log on products for synchronous refresh using products_st  ;
create materialized view log on customers for synchronous refresh using customers_st;  
create materialized view log on sales for synchronous refresh using sales_st;

create table sales_tmp (
prod_id        number not null references products(prod_id),
cust_id        number  not null references customers(cust_id),
time_id        date ,
quantity_sold  number ,
amount_sold    number );

exec dbms_sync_refresh.register_mviews('sales_mv');
 partition_op                =>'EXCHANGE',
 schema_name                  =>user,
 base_table_name             =>'SALES',
 partition_name              =>'P2',
 outside_partn_table_schema  =>USER,
 outside_partn_table_name    =>'SALES_TMP', 
 validation                  =>TRUE) ;

insert into sales_tmp( prod_id, cust_id,time_id,quantity_sold,amount_sold) 

exec dbms_sync_refresh.PREPARE_STAGING_LOG(user,'products');
exec dbms_sync_refresh.PREPARE_STAGING_LOG(user,'customers');
exec dbms_sync_refresh.PREPARE_STAGING_LOG(user,'sales');

while preparing the contents on SALES table got this error - have included both FK and partition key in the definition, but still error kindly advice.

demo@PDB1> exec dbms_sync_refresh.PREPARE_STAGING_LOG(user,'sales');
BEGIN dbms_sync_refresh.PREPARE_STAGING_LOG(user,'sales'); END;

ERROR at line 1:
ORA-31922: Foreign key must contain partition key in table "DEMO"."SALES".
ORA-06512: at "SYS.DBMS_SYNC_REFRESH", line 150
ORA-06512: at line 1

and we said...

The error seems straightforward to me: none of the foreign keys include columns from the partition key!

As the docs say about this error:

The foreign key did not contain a partition key. If a table does not have a primary key, the foreign key must contain a partition key.

So either:

- Change the partition scheme so includes foreign key columns
- Add a primary key to SALES

For example, immediately after creating SALES, I ran:

create sequence sale_seq;
alter table sales 
  add sale_id integer default sale_seq.nextval;
alter table sales
  add primary key ( sale_id );

And everything else succeeded without error.

We're not taking reviews currently, so please try again later if you want to add a review.

More to Explore


New to good database design? Check out Chris Saxon's full fundamentals class.