Thread

1. Making Sure Primary and Secondary Keys Alligns

   Anthony Apollis <anthony.apollis@gmail.com> — 2023-09-11T14:09:46Z

   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?
   

2. Re: Making Sure Primary and Secondary Keys Alligns

   Alban Hertroys <haramrae@gmail.com> — 2023-09-11T15:33:56Z

   > 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.
   
   
   
   
   
   
   
   

3. Re: Making Sure Primary and Secondary Keys Alligns

   Anthony Apollis <anthony.apollis@gmail.com> — 2023-09-13T09:06:56Z

   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.
   >
   >
   >
   >
   >
   

4. Re: Making Sure Primary and Secondary Keys Alligns

   Sándor Daku <daku.sandor@gmail.com> — 2023-09-13T17:49:29Z

   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