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

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Rajeshwaran.

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

Last updated: February 18, 2020 - 11:41 am UTC

Version: 12.2.0.1

Viewed 1000+ times

You Asked

Team,

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,9999))||'-'||
   round(dbms_random.value(1,999))||'-'||
   round(dbms_random.value(1,999)) as cust_phone_no,
   object_name||'@company.com' 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) )
as  
select mod(rownum,10000)+1 as prod_id,
  mod(rownum,1000)+1 as cust_id,
  to_date('01-Jan-1998','dd-mon-yyyy')
  +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  
as
select p.prod_id,p.prod_name,
    c.cust_id,c.cust_fname, s.quantity_sold,
  sum(s.quantity_sold), count(s.quantity_sold),
  count(*)
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');
begin
 dbms_sync_refresh.register_partition_operation(
 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) ;
end;
/ 

insert into sales_tmp( prod_id, cust_id,time_id,quantity_sold,amount_sold) 
 values(1,1,sysdate,7,62);
commit; 

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_INTERNAL", line 75
ORA-06512: at "SYS.DBMS_SYNC_REFRESH_INTERNAL", line 1379
ORA-06512: at "SYS.DBMS_SYNC_REFRESH", line 150
ORA-06512: at line 1

and Chris 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.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Design

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