Re: Really strange foreign key constraint problem blocking delete

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(at)bigfoot(dot)com> 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(at)postgresql(dot)org)
>>> 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