Database Administrators Asked by Edgar Ramírez Mondragón on December 28, 2021
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.
You can use the NOT VALID
modifier when creating a foreign key to stop it verifying existing data. For instance:
ALTER TABLE sales
ADD CONSTRAINT sales_date_fk FOREIGN KEY (sale_date)
REFERENCES dates (date_idx) NOT VALID;
I would not recommend this though, unless something prevents you from populating the new child table so you have no choice.
This is postgres specific syntax, for instance the equivalent in SQL Server is WITH NOCHECK
, and some others may not support the option at all, so take care if trying to support multiple database backends.
Answered by David Spillett on December 28, 2021
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.
Answered by Vinod Narwal on December 28, 2021
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP