From: | Anthony Apollis <anthony(dot)apollis(at)gmail(dot)com> |
---|---|
To: | Alban Hertroys <haramrae(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 09:06:56 |
Message-ID: | CAJyMCYLYkBA0DBgFifmrnw=k7tja7xms82Ar8opfn2X6DiQuwQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)gmail(dot)com> wrote:
>
> > On 11 Sep 2023, at 16:09, Anthony Apollis <anthony(dot)apollis(at)gmail(dot)com>
> 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.
>
>
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Pete O'Such | 2023-09-13 17:41:34 | Re: Unqualified relations in views |
Previous Message | Laurenz Albe | 2023-09-13 06:11:03 | Re: Unqualified relations in views |