Re: Making Sure Primary and Secondary Keys Alligns - Mailing list pgsql-general

From Sándor Daku
Subject Re: Making Sure Primary and Secondary Keys Alligns
Date
Msg-id CAKyoTga0e8Et_QMFNTqThVPUore=uHPFe5tAhfq_ft4yuAGSog@mail.gmail.com
Whole thread Raw
In response to Re: Making Sure Primary and Secondary Keys Alligns  (Anthony Apollis <anthony.apollis@gmail.com>)
List pgsql-general
On Wed, 13 Sept 2023 at 17:30, Anthony Apollis <anthony.apollis@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

pgsql-general by date:

Previous
From: "Pete O'Such"
Date:
Subject: Re: Unqualified relations in views
Next
From: "Dirschel, Steve"
Date:
Subject: RE: [EXT] Re: Query performance going from Oracle to Postgres