Re: Making Sure Primary and Secondary Keys Alligns

[Anthony Apollis <anthony.apollis@xxxxxxxxx>]

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! 

On Mon, 11 Sept 2023 at 17:34, Alban Hertroys <haramrae@xxxxxxxxx> wrote:

> On 11 Sep 2023, at 16:09, Anthony Apollis <anthony.apollis@xxxxxxxxx> wrote:
>
> Fact Table:
> CREATE TABLE IF NOT EXISTS system."IMETA_ZTRB_MP$F_ZTBR_TA_BW"
> (

(…)

> )

> and Dimension:CREATE TABLE IF NOT EXISTS dim."IMETA_Region_Mapping"
> (

(…)

> )

> How do i get that all these columns that are joined are aligned, meaning if it starts with 1 in one column it must be 1 in the other columns. Or how would you assign unique keys in Postgres?

Are you perhaps asking how to define FOREIGN KEY CONSTRAINTs?

https://www.postgresql.org/docs/15/ddl-constraints.html#DDL-CONSTRAINTS-FK


Regards,
Alban Hertroys
--
There is always an exception to always.