Re: Really strange foreign key constraint problem blocking delete - Mailing list pgsql-general

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@bigfoot.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@postgresql.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
likethat. 

-Andy