On Wed, 13 Sept 2023 at 17:30, Anthony Apollis <anthony.apol...@gmail.com>
wrote:
> Yes in deed.
> I am trying to make sure that the keys are aligned, but it doesnt update
> or it simply shows NULL in Fact table, meaning its secondary keys.
>
> "-- Step 1: Drop existing foreign key constraint for Entity
> ALTER TABLE system."IMETA_ZTRB_MP$F_ZTBR_TA_BW" DROP CONSTRAINT IF EXISTS
> fk_entity;
>
> -- Step 2: Drop and recreate secondary key for Entity, setting it to null
> by default
> ALTER TABLE system."IMETA_ZTRB_MP$F_ZTBR_TA_BW"
> DROP COLUMN IF EXISTS "Entity_Secondary_Key",
> ADD COLUMN "Entity_Secondary_Key" INTEGER;
>
> -- Step 3: Update secondary key for Entity based on primary key from the
> dimension table
> UPDATE system."IMETA_ZTRB_MP$F_ZTBR_TA_BW" AS fact
> SET "Entity_Secondary_Key" = dim2."Entity_ID"
> FROM dim."IMETA_Entity_Mapping" AS dim2
> WHERE fact."Entity_Secondary_Key" = dim2."Entity_ID";
>
> -- Step 4: Re-add foreign key constraint for Entity
> ALTER TABLE system."IMETA_ZTRB_MP$F_ZTBR_TA_BW"
> ADD CONSTRAINT fk_entity FOREIGN KEY ("Entity_Secondary_Key") REFERENCES
> dim."IMETA_Entity_Mapping"("Entity_ID");
> "
> Thank you!
>
>>
>>
I think you get two things wrong in this case:
Foreign key ensures that you can't put any value in the
Entity_Secondary_Key field which doesn't exists in the IMETA_Entity_Mapping
table's Entity_ID column. (Null is still acceptable.)
Removing the foreign key constraint and then later adding again kind of
countering that purpose.
Your step 3 doesn't make sense: Your SET expression is the same as the
WHERE clause. It would change the value of Entity_Secondary_Key to the same
value it already has. Except you removed that field and added again, and
because this newly added Entity_Secondary_Key field contains null in all
record the WHERE fact."Entity_Secondary_Key" = dim2."Entity_ID" clause
won't find any matching records in the IMETA_Entity_Mapping table.
Regards,
Sándor
