From: | Sándor Daku <daku(dot)sandor(at)gmail(dot)com> |
---|---|
To: | Anthony Apollis <anthony(dot)apollis(at)gmail(dot)com> |
Cc: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Making Sure Primary and Secondary Keys Alligns |
Date: | 2023-09-13 17:49:29 |
Message-ID: | CAKyoTga0e8Et_QMFNTqThVPUore=uHPFe5tAhfq_ft4yuAGSog@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, 13 Sept 2023 at 17:30, Anthony Apollis <anthony(dot)apollis(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Dirschel, Steve | 2023-09-13 17:58:00 | RE: [EXT] Re: Query performance going from Oracle to Postgres |
Previous Message | Pete O'Such | 2023-09-13 17:41:34 | Re: Unqualified relations in views |