Re: BUG #5205: Cannot ADD CONSTRAINT ... FOREIGN KEY...

On Fri, Nov 20, 2009 at 6:44 PM, Alain Baeckeroot
<alain.baeckeroot@laposte.net> wrote:
>
> The following bug has been logged online:
>
> Bug reference: =A0 =A0 =A05205
> Logged by: =A0 =A0 =A0 =A0 =A0Alain Baeckeroot
> Email address: =A0 =A0 =A0alain.baeckeroot@laposte.net
> PostgreSQL version: 8.4.1
> Operating system: =A0 Linux (Ubuntu 9.10)
> Description: =A0 =A0 =A0 =A0Cannot ADD CONSTRAINT ... FOREIGN KEY...
> Details:
>
> I think i could add this constraint on an existing table:
> =3D> select id from asset except (select asset.id from asset, base where
> asset.id_base =3D base.id);
> =A0id
> ----
> (0 rows)
>
> =3D> ALTER TABLE ONLY asset ADD CONSTRAINT asset_id_base_fkey FOREIGN KEY
> (id_base) REFERENCES base(id);
> ERREUR: =A0une instruction insert ou update sur la table =AB asset =BB vi=
ole la
> contrainte de cl=E9
> =E9trang=E8re =AB asset_id_base_fkey =BB
> DETAIL: =A0La cl=E9 (id_base)=3D(196) n'est pas pr=E9sente dans la table =
=AB base
> =BB.
>
> =3D> select * from base where id =3D 196;
> =A0id =A0| =A0 =A0 =A0 =A0 date_crea =A0 =A0 =A0 =A0 =A0| =A0 =A0 =A0 =A0=
 date_modif =A0 =A0 =A0 =A0 | auth_crea |
> auth_modif | ordre
> -----+----------------------------+----------------------------+---------=
--+
> ------------+-------
> =A0196 | 2009-03-16 11:57:44.391607 | 2009-03-16 11:57:44.391607 | sql =
=A0 =A0 =A0 |
> sql =A0 =A0 =A0 =A0| =A0 =A0 7
> (1 row)
>
> What is wrong ?
> The table asset inherits from base, but it seems to be allowed and to work
> (at least on the dumb test i did)

I am guessing that the row may be in an inherited child of base rather
than in base itself?

...Robert