Re: Really strange foreign key constraint problem blocking delete

Andy Colson <andy@xxxxxxxxxxxxxxx> · Sat, 04 Oct 2014 09:06:13 -0500

On 10/04/2014 12:45 AM, Tim Mickelson wrote:
But I don't think so, but here are the table defenitions:

CREATE TABLE bulldog.channel_mapping
(
   idchannel integer NOT NULL,
   idaut integer NOT NULL,
   CONSTRAINT channel_mapping_pk PRIMARY KEY (idchannel, idaut),
   CONSTRAINT channel_mapping_idaut_fkey FOREIGN KEY (idaut)
       REFERENCES cubesocialnetwork.tmp_autenticazionesocial
(idautenticazionesocial) MATCH SIMPLE
       ON UPDATE NO ACTION ON DELETE NO ACTION,
   CONSTRAINT channel_mapping_idchannel_fkey FOREIGN KEY (idchannel)
       REFERENCES bulldog.social_channel (idchannel) MATCH SIMPLE
       ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
   OIDS=FALSE
);
ALTER TABLE bulldog.channel_mapping
   OWNER TO postgres;

-- Table: cubesocialnetwork.tmp_autenticazionesocial

-- DROP TABLE cubesocialnetwork.tmp_autenticazionesocial;

CREATE TABLE cubesocialnetwork.tmp_autenticazionesocial
(
   idautenticazionesocial serial NOT NULL,
   contratto text NOT NULL,
   idlocation numeric NOT NULL,
   textuser text,
   textpassword text,
   datacrea timestamp without time zone NOT NULL DEFAULT now(),
   idsocial numeric NOT NULL,
   location text,
   username text,
   link_foto text,
   valid text,
   link_profilo text,
   tweetmonitored boolean DEFAULT false,
   idutente text,
   tipologia text,
   api_key text,
   api_secret text,
   CONSTRAINT tmp_autenticazionesocial_pkey PRIMARY KEY
(idautenticazionesocial),
   CONSTRAINT tipo_social_fk FOREIGN KEY (idsocial)
       REFERENCES cubesocialnetwork.tipo_social (cd_social) MATCH FULL
       ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
   OIDS=FALSE
);
ALTER TABLE cubesocialnetwork.tmp_autenticazionesocial
   OWNER TO postgres;

-- Index: cubesocialnetwork.indice_tmp_autenticazione

-- DROP INDEX cubesocialnetwork.indice_tmp_autenticazione;

CREATE INDEX indice_tmp_autenticazione
   ON cubesocialnetwork.tmp_autenticazionesocial
   USING btree
   (textpassword COLLATE pg_catalog."default");

-- Index: cubesocialnetwork.indicetextuser

-- DROP INDEX cubesocialnetwork.indicetextuser;

CREATE INDEX indicetextuser
   ON cubesocialnetwork.tmp_autenticazionesocial
   USING btree
   (textuser COLLATE pg_catalog."default");

-- Index: cubesocialnetwork.indicidentificativosocial

-- DROP INDEX cubesocialnetwork.indicidentificativosocial;

CREATE INDEX indicidentificativosocial
   ON cubesocialnetwork.tmp_autenticazionesocial
   USING btree
   (idsocial);

-- Index: cubesocialnetwork.tmpautenticazione

-- DROP INDEX cubesocialnetwork.tmpautenticazione;

CREATE INDEX tmpautenticazione
   ON cubesocialnetwork.tmp_autenticazionesocial
   USING btree
   (idautenticazionesocial);

-- Index: cubesocialnetwork.tmpautenticazionecontrattoidlocation

-- DROP INDEX cubesocialnetwork.tmpautenticazionecontrattoidlocation;

CREATE INDEX tmpautenticazionecontrattoidlocation
   ON cubesocialnetwork.tmp_autenticazionesocial
   USING btree
   (contratto COLLATE pg_catalog."default", idlocation);

-- Index: cubesocialnetwork.tmpauteticazionesocial

-- DROP INDEX cubesocialnetwork.tmpauteticazionesocial;

CREATE INDEX tmpauteticazionesocial
   ON cubesocialnetwork.tmp_autenticazionesocial
   USING btree
   (username COLLATE pg_catalog."default");

On 03/10/2014 20:38, Vick Khera wrote:
Using my magick powers of mind reading, I will guess you made circular
dependencies.

On Fri, Oct 3, 2014 at 12:17 PM, Tim Mickelson
<tim_mickelson@xxxxxxxxxxx> wrote:
Even if I try to delete the data entry in the table channel_mapping with
idaut 1622, it is not possible to delete from the table
tmp_autenticazionesocial with the error below. How is this even possible
since there is no channel_mapping with idaut 1622 any more?? I tried
this in Java under a transaction, but also in pgAdmin III.

ERROR:  update or delete on table "tmp_autenticazionesocial" violates
foreign key constraint "channel_mapping_idaut_fkey" on table
"channel_mapping"
DETAIL:  Key (idautenticazionesocial)=(1622) is still referenced from
table "channel_mapping".

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

What version of PG are you on?  I kinda recall one of the updates really wanting you to rebuild indexes or something like that.

-Andy

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general