Обсуждение: Making Sure Primary and Secondary Keys Alligns

Поиск
Список
Период
Сортировка

Making Sure Primary and Secondary Keys Alligns

От
Anthony Apollis
Дата:
Fact Table:
-- DROP TABLE IF EXISTS system."IMETA_ZTRB_MP$F_ZTBR_TA_BW";

CREATE TABLE IF NOT EXISTS system."IMETA_ZTRB_MP$F_ZTBR_TA_BW"
(
    "ZTBR_TransactionCode" integer NOT NULL DEFAULT nextval('system."IMETA_ZTRB_MP$F_ZTBR_TA_BW_ZTBR_TransactionCode_seq"'::regclass),
    "Company_Code" character varying(4) COLLATE pg_catalog."default",
    "Posting_Period" character varying(7) COLLATE pg_catalog."default",
    "Fiscal_Year" character varying(4) COLLATE pg_catalog."default",
    "Profit_Center" character varying(255) COLLATE pg_catalog."default",
    "Account_Number" character varying(255) COLLATE pg_catalog."default",
    "Business_Process" character varying(255) COLLATE pg_catalog."default",
    "Cost_Center" character varying(10) COLLATE pg_catalog."default",
    "Internal_Order" character varying(255) COLLATE pg_catalog."default",
    "Trading_Partner" character varying(255) COLLATE pg_catalog."default",
    "Amount_in_company_code_currency" numeric(17,2),
    "Company_code_currency" character varying(5) COLLATE pg_catalog."default",
    "BRACS_FA" character varying(255) COLLATE pg_catalog."default",
    "Expense_Type" character varying(255) COLLATE pg_catalog."default",
    "BRACS_ACCT_Key" character varying(255) COLLATE pg_catalog."default",
    "CC_Direct" character varying(255) COLLATE pg_catalog."default",
    "Segment_PC" character varying(255) COLLATE pg_catalog."default",
    "CC_Master_FA" character varying(255) COLLATE pg_catalog."default",
    "Master_BRACS_Secondary_Key" integer,
    "Source_Description_Secondary_Key" integer,
    "Direct_Indirect_Secondary_Key" integer,
    "Entity_Secondary_Key" integer,
    "Region_Secondary_Key" integer,
    "Staging_Secondary_Key" integer NOT NULL DEFAULT nextval('system."IMETA_ZTRB_MP$F_ZTBR_TA_BW_Staging_Secondary_Key_seq"'::regclass),
    CONSTRAINT "IMETA_ZTRB_MP$F_ZTBR_TA_BW_pkey" PRIMARY KEY ("ZTBR_TransactionCode"),
    CONSTRAINT "IMETA_ZTRB_MP$F_ZTBR_TA_BW_Direct_Indirect_Secondary_Key_key" UNIQUE ("Direct_Indirect_Secondary_Key"),
    CONSTRAINT "IMETA_ZTRB_MP$F_ZTBR_TA_BW_Entity_Secondary_Key_key" UNIQUE ("Entity_Secondary_Key"),
    CONSTRAINT "IMETA_ZTRB_MP$F_ZTBR_TA_BW_Master_BRACS_Secondary_Key_key" UNIQUE ("Master_BRACS_Secondary_Key"),
    CONSTRAINT "IMETA_ZTRB_MP$F_ZTBR_TA_BW_Region_Secondary_Key_key" UNIQUE ("Region_Secondary_Key"),
    CONSTRAINT "IMETA_ZTRB_MP$F_ZTBR_TA_BW_Source_Description_Secondary_Key_key" UNIQUE ("Source_Description_Secondary_Key"),
    CONSTRAINT "IMETA_ZTRB_MP$F_ZTBR_TA_BW_Staging_Secondary_Key_key" UNIQUE ("Staging_Secondary_Key")
)and Dimension:CREATE TABLE IF NOT EXISTS dim."IMETA_Region_Mapping"
(
    "CoCd" character varying(255) COLLATE pg_catalog."default",
    "Sub Region" character varying(255) COLLATE pg_catalog."default",
    "Region" character varying(255) COLLATE pg_catalog."default",
    "BRACS Entity" character varying(255) COLLATE pg_catalog."default",
    "Consul" character varying(255) COLLATE pg_catalog."default",
    "Report" character varying(255) COLLATE pg_catalog."default",
    "Region BRACS" character varying(255) COLLATE pg_catalog."default",
    "Group" character varying(255) COLLATE pg_catalog."default",
    "Group BRACS" character varying(255) COLLATE pg_catalog."default",
    "J" character varying(255) COLLATE pg_catalog."default",
    "K" character varying(255) COLLATE pg_catalog."default",
    "L" character varying(255) COLLATE pg_catalog."default",
    "M" character varying(255) COLLATE pg_catalog."default",
    "N" character varying(255) COLLATE pg_catalog."default",
    "Region_Primary_Key" integer NOT NULL DEFAULT nextval('dim."IMETA_Region_Mapping_Region_Secondary_Key_seq"'::regclass),
    CONSTRAINT "IMETA_Region_Mapping_pkey" PRIMARY KEY ("Region_Primary_Key")
)
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?

Re: Making Sure Primary and Secondary Keys Alligns

От
Alban Hertroys
Дата:
> On 11 Sep 2023, at 16:09, Anthony Apollis <anthony.apollis@gmail.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
1in 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.

Re: Making Sure Primary and Secondary Keys Alligns

От
Anthony Apollis
Дата:
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@gmail.com> wrote:

> On 11 Sep 2023, at 16:09, Anthony Apollis <anthony.apollis@gmail.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.




Re: Making Sure Primary and Secondary Keys Alligns

От
Sándor Daku
Дата:
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