ADD FOREIGN KEY fails, but the records exist
От | Ron |
---|---|
Тема | ADD FOREIGN KEY fails, but the records exist |
Дата | |
Msg-id | e8a458ad-2f92-6f8d-c485-8f72e3d4f72a@gmail.com обсуждение исходный текст |
Ответы |
Re: ADD FOREIGN KEY fails, but the records exist
Re: ADD FOREIGN KEY fails, but the records exist |
Список | pgsql-general |
Postgresql 12.5 It's a self-referential FK on a single (but partitioned) table. The ALTER TABLE command fails, but I queried it, and the record that it fails on exists. I modified the original INITIALLY IMMEDIATE clause to INITIALLY DEFERRED but that did not help. What am I doing wrong? (We're migrating from Oracle to PostgreSQL, and this is working in Oracle.) sides=> ALTER TABLE employer_response ADD CONSTRAINT amended_response_fk FOREIGN KEY (amended_response_id, part_date) REFERENCES employer_response(employer_response_id, part_date) ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED ; ERROR: insert or update on table "employer_response_p2021_01" violates foreign key constraint "amended_response_fk" DETAIL: Key (amended_response_id, part_date)=(103309154, 2021-01-06 00:00:00) is not present in table "employer_response". sides=> sides=> select employer_response_id, part_date sides-> from strans.employer_response sides-> where amended_response_id = 103309154; employer_response_id | part_date ----------------------+--------------------- 103309156 | 2021-01-06 00:00:00 (1 row) sides=> select employer_response_id, part_date from strans.employer_response_p2021_01 where amended_response_id = 103309154; employer_response_id | part_date ----------------------+--------------------- 103309156 | 2021-01-06 00:00:00 (1 row) -- Angular momentum makes the world go 'round.
В списке pgsql-general по дате отправления: