I am unable to insert data into my fact table. I receive the follow error messag

Question

I am unable to insert data into my fact table. I receive the follow error message: Can someone please help?????

ORA-02291: integrity constraint (CM420F01.FACTS_TABLE_FK1) violated - parent key not found
ORA-06512: at "CM420F01.MOVIE_FACT_ADD", line 38
ORA-06512: at line 1
02291. 00000 - "integrity constraint (%s.%s) violated - parent key not found"
*Cause: A foreign key value has no matching primary key value.
*Action: Delete the foreign key or add a matching primary key.

CREATE TABLE "FACTS_TABLE"
(   "FACT_ID" NUMBER(6,0),
   "ITEM_SUB_TOTAL" NUMBER(12,0) NOT NULL ENABLE,
   "TOTAL_ITEMS" NUMBER(6,0) NOT NULL ENABLE,
   "NUMBER_OF_ITEMS" NUMBER(10,0) NOT NULL ENABLE,
   "MOVIE_ID" NUMBER(6,0) NOT NULL ENABLE,
   "STORE_ID" NUMBER(4,0) NOT NULL ENABLE,
   "DISTRIBUTOR_ID" NUMBER(3,0) NOT NULL ENABLE,
   "TIME_ID" NUMBER(7,0) NOT NULL ENABLE,
   "PROFIT" NUMBER(13,0) NOT NULL ENABLE,
   PRIMARY KEY ("FACT_ID"),

   CONSTRAINT "FACTS_TABLE_FK1" FOREIGN KEY ("DISTRIBUTOR_ID")
   REFERENCES "DIM_DISTRIBUTORS" ("DISTRIBUTOR_ID") ENABLE,
   CONSTRAINT "FACTS_TABLE_FK2" FOREIGN KEY ("STORE_ID")
   REFERENCES "DIM_STORES" ("STORE_ID") ENABLE,
   CONSTRAINT "FACTS_TABLE_FK3" FOREIGN KEY ("TIME_ID")
   REFERENCES "DIM_TIME" ("TIME_ID") ENABLE,
   CONSTRAINT "FACTS_TABLE_FK4" FOREIGN KEY ("MOVIE_ID")
   REFERENCES "DIM_MOVIES" ("MOVIE_ID") ENABLE

);

Create or replace procedure movie_fact_Add as

cursor get_facts is
Select distinct
movie_id,
sum(number_of_items),
store_id,
distributor_id,
year,
quarter,
month,
Item_Id,
sum(number_of_items) as Total_Items,
sum(item_sub_total)* 0.12 profits ,
sum(item_sub_total) as Total_Payment
from Sales_Facts
group by movie_id,
store_id,
distributor_id,
month,
quarter, Item_Id,
Year;

v_time_dim_id number(5);
v_count number(6) := 0;   

  

BEGIN

for v_facts in get_facts loop
select time_id into v_time_dim_id from dim_time
where month = v_facts.month
-- and quarter = v_facts.quarter
AND year = v_facts.year;

Insert into Facts_Table Values (
  
v_facts.Total_Payment,
FACT_ID_SEQ.NEXTVAL,
v_facts.Total_Items,
v_facts.movie_id,
v_facts.store_id,
v_facts.distributor_id,
v_time_dim_id,
v_facts.store_id,
v_facts.profits,
v_facts.Item_Id
);
v_count:= v_count + 1;
  

end loop;
commit;

DBMS_OUTPUT.PUT_LINE('Total records inserted: '|| v_count);

End movie_fact_Add ;
/

Explanation / Answer

Answer:

First, need to know about the referential integrity constraint.

A referential integrity constraint specifies that the value of a foreign key should match with value of the primary key in the primary table.

·   It ensures that it is not possible to add a value to a foreign key which does not exist in the primary key of the primary table.

·   It ensures that it is not possible to delete a record from the primary table if there is any matching record in the referencing table.

So, according to the data provided and the error information, there must be a mismatch of the Foreign key with the Primary key of the original data table.

So, from the error information it is pointing out at foreign key DISTRIBUTOR_ID, or any other foreign key that is used in the CONSTRAINT section.

Try to provide the matching foreign key its respective table’s primary key.

Hope this must solve the problem.

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.