0

Let's say I have one table defined as this

CREATE TABLE sales (
    agent_id   integer references agents(agent_id),
    sale_date  date,
    amout      numeric(10,2)
);

Then an ETL process fills this table with data

INSERT INTO sales VALUES
    (1, '2019-01-01', 1031.11),
    (1, '2019-01-02', 525.44),
    (1, '2019-01-03', 323.99);

But later, I need to add a table with actual processed dates, such as

CREATE TABLE dates (
    date_idx   date primary key,
    year       integer not null,
    month      integer not null,
    day        integer not null
);

and want to add a foreign key constraint on the old table:

ALTER TABLE sales
    ADD CONSTRAINT sales_date_fk FOREIGN KEY (sale_date)
    REFERENCES dates (date_idx);

Naturally, I get the following error:

ERROR: insert or update on table "sales" violates foreign key constraint "sales_date_fk" Detail: Key (sale_date)=(2019-01-01) is not present in table "dates".

I know I can work around this by deleting/truncating all data in sales or prefilling dates before adding the constraint, but I would prefer not to if I can avoid it.

Is there any way to accomplish this?

I'm using Postgres 11, but this fiddle running 9.6 shows what I've tried to explain here.

0

Select all distinct date from sales tables and insert data into the dates tables. After populating data into that table try to create the foreign key constraint.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.