Why do I get SQL state:23503 on an inexistent foreign key

Question

When I try to delete a record from table1 I receive this error message:

ERROR:  update or delete on "table1" violates foreign key constraint "<unnamed>" on "table2"
DETAIL:  Key (idtb1)=(1569) is still referenced from table "table2".
********** Error **********

ERROR: update or delete on "table1" violates foreign key constraint "<unnamed>" on "table2"
SQL state: 23503
Detail: Key (idtb1)=(1569) is still referenced from table "table2".

The problem is that I don't have any foreign key on table2.

Here the scripts to create the tables

CREATE TABLE table1
(
  idtb1 integer NOT NULL DEFAULT nextval('"table1_idtb1_seq"'::text),
  nometb1 character varying(16),
  stato character varying(4),
  capit character(1),
  email character varying(2000), 
  impianti boolean NOT NULL DEFAULT true 
)
WITH OIDS;
ALTER TABLE table1
  OWNER TO postgres;

  CREATE TABLE table2
(
  idtb2 integer NOT NULL DEFAULT nextval('"table2_idtb2_seq"'::text),
  idn integer DEFAULT 0,
  dataarr date,
  oraarr time without time zone,
  arrinc character varying(1),
  orind character varying(15),
  pprovenienza character varying(30),
  flgaggiunte character(1),
  ocm text,
  flageliminato character varying(1),
  nomea character varying(50),
  nomeb character varying(50),
  pesc double precision
)
WITH OIDS;
ALTER TABLE table2
  OWNER TO postgres;

Is an old database

"PostgreSQL 8.0.26 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-4)"

How can I proceed?

WITH OIDS is deprecated and should not be used any more. Does this change if you remove that? Also: what is the exact Postgres version you are using? select version() — a_horse_with_no_name
Why are you using such an outdated and unsupported version? Your example works fine with any supported Postgres version. — a_horse_with_no_name
Yes, is an old outdated and unsupported version, but now is not possible to update. — Matteo Gorini
You should post that on the Postgres mailing list. Maybe somewhere there can help — a_horse_with_no_name

Laurenz Albe Laurenz Albe · Accepted Answer · 2016-11-11T11:32:09

That looks somewhat like data corruption.

Maybe you should install PostgreSQL 9.6, pg_dumpall the cluster and load it into a 9.6 database. Perhaps that will make the problem go away.

Why do I get SQL state:23503 on an inexistent foreign key

1 Answers