Hello Experts,
I have referential integrity constraint between 2 tables like below:
CONSTRAINT "O_CLAIM_MESSAGE_F1" FOREIGN KEY ("CLAIM_CODE", "CLAIM_VERSION", "CLAIM_LINE_SEQUENCE")
REFERENCES "OHA_OIL"."O_CLAIM_LINE" ("CLAIM_CODE", "CLAIM_VERSION", "CLAIM_LINE_SEQUENCE") ENABLE
However I am still able to insert a new record into O_CLAIM_MESSAGE table without a respective entry at parent table O_CLAIM_LINE.
In fact my entire ETL load completed without FK failure.
However I have 300 records in O_CLAIM_MESSAGE which does not exist at O_CLAIM_LINE at all.
I did minus like this to find missing records at parent table:
select claim_code, claim_version, claim_line_sequence from oha_oil.O_CLAIM_MESSAGE
minus
select claim_code, claim_version, claim_line_sequence from oha_oil.O_CLAIM_line
It looks like referential integrity is disabled or deferred , though enabled at table DDL?
select constraint_name, table_name, r_constraint_name , delete_rule, status, deferrable, deferred, validated from all_constraints where table_name ='O_CLAIM_MESSAGE'
and constraint_name='O_CLAIM_MESSAGE_F1'
