I am unable to insert data into my fact table. I receive the follow error messag
ID: 3772085 • Letter: I
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.