Re: 8.4.0 bug - failure to enforce a foreign key constraint

On Thu, Aug 13, 2009 at 12:36 PM, Radoslaw Zielinski<radek(at)pld-linux(dot)org> wrote:
> Hello,
>
> I have reported this yesterday via WWW as bug 4979, but I can't see it
> in the -bugs archive.  Has it been lost or are the bug reports being
> moderated...?
>
> Anyway.  Pg 8.4.0 from yum.postgresql.org running on 64bit RHEL 5.3.
>
>  radek=# \d kandydaci
>             Table "public.kandydaci"
>        Column       |       Type       | Modifiers
>  -------------------+------------------+-----------
>   id_rekordu        | bigint           | not null
>   id_osoby          | integer          | not null
>   id_rodzaju_adresu | smallint         |
>   score             | double precision | not null
>  Indexes:
>      "kandydaci_pkey" PRIMARY KEY, btree (id_rekordu, id_osoby)
>  Check constraints:
>      "c_kandydaci_score" CHECK (score >= 0::double precision AND score <= 1::double precision)
>  Foreign-key constraints:
>      "kandydaci_fk_id_rekordu" FOREIGN KEY (id_rekordu) REFERENCES rekordy(id) ON DELETE CASCADE
>
>  radek=# select count(*), sum((r.id is null)::int) as orphans from kandydaci k left join rekordy r on r.id=k.id_rekordu;
>   count | orphans
>  -------+---------
>    1472 |     152
>  (1 row)
since you do LEFT JOIN, indeed you can get r.id to be null.

--
GJ