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

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


--
Adrian Klaver
adrian.klaver@aklaver.com