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

On Fri, Nov 20, 2009 at 6:44 PM, Alain Baeckeroot
<alain(dot)baeckeroot(at)laposte(dot)net> wrote:
>
> The following bug has been logged online:
>
> Bug reference:      5205
> Logged by:          Alain Baeckeroot
> Email address:      alain(dot)baeckeroot(at)laposte(dot)net
> PostgreSQL version: 8.4.1
> Operating system:   Linux (Ubuntu 9.10)
> Description:        Cannot ADD CONSTRAINT ... FOREIGN KEY...
> Details:
>
> I think i could add this constraint on an existing table:
> => select id from asset except (select asset.id from asset, base where
> asset.id_base = base.id);
>  id
> ----
> (0 rows)
>
> => ALTER TABLE ONLY asset ADD CONSTRAINT asset_id_base_fkey FOREIGN KEY
> (id_base) REFERENCES base(id);
> ERREUR:  une instruction insert ou update sur la table « asset » viole la
> contrainte de clé
> étrangère « asset_id_base_fkey »
> DETAIL:  La clé (id_base)=(196) n'est pas présente dans la table « base
> ».
>
> => select * from base where id = 196;
>  id  |         date_crea          |         date_modif         | auth_crea |
> auth_modif | ordre
> -----+----------------------------+----------------------------+-----------+
> ------------+-------
>  196 | 2009-03-16 11:57:44.391607 | 2009-03-16 11:57:44.391607 | sql       |
> sql        |     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