Foreign key returns null

iam working on my first database in postgresql and I am stuck with a problem I cannot solve. After implementing the database, I started inserting dummy data via data.csv files. After doing i realized that my foreign keys always return null. For example the foreign key from the table fact_werbepartner references to the primary key dim_ansprechpartner.

My desired is that the foreign key inherits the primary key numbers so I can JOIN tables!

When selecting dim_ansprechpartner get the following information: [return values dim_ansprechpartner]

When selecting fact_werbepartner I get these null references: [return values fact_werbepartner]

FYI: When I add the NOT NULL Constraint, I get the following error message: column “wp_ansprechpartner_fk” of relation “fact_werbepartner” contains null values ( which is kinda obvious..)

Stuck with this problem for hours! How can I solve it?

CSV Files

[ERD Diagramm two tables]

[ERD DIAGRAMM complete database]

CREATE TABLE IF NOT EXISTS dim_ansprechpartner(
    ap_partner_id_pk serial NOT NULL,
    ap_partnername varchar(30) NOT NULL,
    ap_Telefonnummer  varchar(30),
    PRIMARY KEY (ap_partner_id_pk)
);
CREATE TABLE IF NOT EXISTS fact_werbepartner(
    wp_firmen_id_pk  serial NOT NULL,
    wp_firmenname varchar (50) NOT NULL,
    wp_firmengroesse int,
    wp_ansprechpartner_fk int NOT NULL,
    PRIMARY KEY (wp_firmen_id_pk),
    FOREIGN KEY (wp_ansprechpartner_fk)
        REFERENCES dim_ansprechpartner(ap_partner_id_pk)
    
);
COPY dim_ansprechpartner(ap_Partnername, ap_Telefonnummer)
FROM 'C:UsersxxxxDesktopmock datadim_ansprechpartner.csv' 
DELIMITER ',' 
CSV HEADER;

COPY fact_werbepartner(wp_firmenname,wp_firmengroesse)
FROM 'C:UsersxxxxDesktopmock datafact_werbepartner.csv' 
DELIMITER ',' 
CSV HEADER;

​x
 
CREATE TABLE IF NOT EXISTS dim_ansprechpartner(    ap_partner_id_pk serial NOT NULL,    ap_partnername varchar(30) NOT NULL,    ap_Telefonnummer  varchar(30),    PRIMARY KEY (ap_partner_id_pk));CREATE TABLE IF NOT EXISTS fact_werbepartner(    wp_firmen_id_pk  serial NOT NULL,    wp_firmenname varchar (50) NOT NULL,    wp_firmengroesse int,    wp_ansprechpartner_fk int NOT NULL,    PRIMARY KEY (wp_firmen_id_pk),    FOREIGN KEY (wp_ansprechpartner_fk)        REFERENCES dim_ansprechpartner(ap_partner_id_pk)    );COPY dim_ansprechpartner(ap_Partnername, ap_Telefonnummer)FROM 'C:UsersxxxxDesktopmock datadim_ansprechpartner.csv' DELIMITER ',' CSV HEADER;​COPY fact_werbepartner(wp_firmenname,wp_firmengroesse)FROM 'C:UsersxxxxDesktopmock datafact_werbepartner.csv' DELIMITER ',' CSV HEADER;​

Answer

A FOREIGN KEY establishes a relationship between two tables and you don’t have that. When you do COPY dim_ansprechpartner(ap_Partnername, ap_Telefonnummer) the ap_partner_id_pk is going to be automatically filled with integers. Each time you do that it will be an incremented set of numbers unless you reset the sequence behind the serial type. In any case when you do COPY fact_werbepartner(wp_firmenname,wp_firmengroesse) the wp_ansprechpartner_fk field will not be filled in, hence the NULLs you see. Since that is the FK to dim_ansprechpartner there will be no relationship. You at some point need to make that relationship explicit. Either by adding ap_partner_id_pk in dim_ansprechpartner.csv and wp_ansprechpartner_fk in fact_werbepartner.csv or by some other means. Postgres has no way of establishing that otherwise.

Advertisement

Answer