pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedMaking Sure Primary and Secondary Keys Alligns
4+ messages / 3 participants
[nested] [flat]
* Making Sure Primary and Secondary Keys Alligns
@ 2023-09-11 14:09 Anthony Apollis <anthony.apollis@gmail.com>
2023-09-11 15:33 ` Re: Making Sure Primary and Secondary Keys Alligns Alban Hertroys <haramrae@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Anthony Apollis @ 2023-09-11 14:09 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Making Sure Primary and Secondary Keys Alligns
2023-09-11 14:09 Making Sure Primary and Secondary Keys Alligns Anthony Apollis <anthony.apollis@gmail.com>
@ 2023-09-11 15:33 ` Alban Hertroys <haramrae@gmail.com>
2023-09-13 09:06 ` Re: Making Sure Primary and Secondary Keys Alligns Anthony Apollis <anthony.apollis@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Alban Hertroys @ 2023-09-11 15:33 UTC (permalink / raw)
To: Anthony Apollis <anthony.apollis@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
> 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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Making Sure Primary and Secondary Keys Alligns
2023-09-11 14:09 Making Sure Primary and Secondary Keys Alligns Anthony Apollis <anthony.apollis@gmail.com>
2023-09-11 15:33 ` Re: Making Sure Primary and Secondary Keys Alligns Alban Hertroys <haramrae@gmail.com>
@ 2023-09-13 09:06 ` Anthony Apollis <anthony.apollis@gmail.com>
2023-09-13 17:49 ` Re: Making Sure Primary and Secondary Keys Alligns Sándor Daku <daku.sandor@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Anthony Apollis @ 2023-09-13 09:06 UTC (permalink / raw)
To: Alban Hertroys <haramrae@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
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.
>
>
>
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Making Sure Primary and Secondary Keys Alligns
2023-09-11 14:09 Making Sure Primary and Secondary Keys Alligns Anthony Apollis <anthony.apollis@gmail.com>
2023-09-11 15:33 ` Re: Making Sure Primary and Secondary Keys Alligns Alban Hertroys <haramrae@gmail.com>
2023-09-13 09:06 ` Re: Making Sure Primary and Secondary Keys Alligns Anthony Apollis <anthony.apollis@gmail.com>
@ 2023-09-13 17:49 ` Sándor Daku <daku.sandor@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Sándor Daku @ 2023-09-13 17:49 UTC (permalink / raw)
To: Anthony Apollis <anthony.apollis@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2023-09-13 17:49 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-09-11 14:09 Making Sure Primary and Secondary Keys Alligns Anthony Apollis <anthony.apollis@gmail.com>
2023-09-11 15:33 ` Alban Hertroys <haramrae@gmail.com>
2023-09-13 09:06 ` Anthony Apollis <anthony.apollis@gmail.com>
2023-09-13 17:49 ` Sándor Daku <daku.sandor@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox