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.