Re: Really strange foreign key constraint problem blocking delete

On 10/03/2014 10:45 PM, 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

So channel_mapping references autenticazionesocial. The error message
you gave previously indicates that there is more than one entry in
channel_mapping referencing idautenticazionesocial=1622 in
autenticazionesocial.

So are you positive channel_mapping has no entries left that have
idaut=1622?

Also, to help with troubleshooting, what version of Postgres are you using?

>
> 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
>>
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com