PostgreSQL Foreign Key Constraint cannot Find Existing Key

Question

I have this table:

CREATE TABLE operation_history
(
  id bigserial NOT NULL,
  task_history bigint NOT NULL,
  operation smallint NOT NULL,
  CONSTRAINT operation_history_pkey PRIMARY KEY (id),
  CONSTRAINT operation_history_operation_fkey FOREIGN KEY (operation)
             REFERENCES desktop_operation (id) MATCH SIMPLE
             ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT operation_history_task_history FOREIGN KEY (task_history)
             REFERENCES task_history (id) MATCH SIMPLE
             ON UPDATE NO ACTION ON DELETE NO ACTION
)

and I have a another table task_history that has three rows in it, with the ids: 1, 2, 3. For some reason, though, when I insert on the operation_history table, I get an error saying that the task_history row doesn't exist with the id I gave it. Here is the query to insert:

INSERT INTO operation_history (task_history, operation) VALUES
(1, 2);

And here is the error:

ERROR:  insert or update on table "operation_history" violates foreign key constraint "operation_history_task_history"
DETAIL:  Key (task_history)=(1) is not present in table "task_history".

I know for sure, though, that there are three rows in the task_history table with ids 1, 2, and 3.

What could be causing this and how can I remedy it?

Highly impossible .. see this fiddle sqlfiddle.com/#!15/999e7/1 — Rahul
– Rahul, Commented Jul 17, 2014 at 1:18

Craig Ringer · Accepted Answer · 2014-07-17 01:19:59Z

1

Try:

SET enable_seqscan = off;
SELECT t.id FROM task_history t WHERE t.id = 1;    

SET enable_seqscan = on;
SET enable_indexscan = off;
SET enable_indexonlyscan = off;
SELECT t.id FROM task_history t WHERE t.id = 1;

If the results differ, then you likely have a damaged index. That shouldn't happen. If you're not on a current PostgreSQL patch release, upgrade immediately and REINDEX. If you are, I'd be checking for memory issues on the machine, recent MCEs (machine check exceptions), looking for storage error messages, checking the disks, etc.

If the queries produce the same results, then your table contents or definitions aren't what you think they are.

answered Jul 17, 2014 at 1:19

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL Foreign Key Constraint cannot Find Existing Key

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related