Re: Referential integrity broken (8.0.3), sub-select help

On Tue, 21 Mar 2006 ogjunk-pgjedan@yahoo.com wrote:

> I mistakenly swapped the tables in my email.  Here they are, corrected:
>
>  Table "url":
>      id                  SERIAL
>                           CONSTRAINT pk_url_id PRIMARY KEY
>
> Table "bookmark":
>      url_id              INTEGER
>                            CONSTRAINT fk_url_id REFERENCES url(id)
>
> I see my questions got chopped off from this email below, so let me restate them:
>
>
> Problem #1: Strange that PG allowed this to happen.  Maybe my DDL above
> allows this to happen and needs to be tightened?  I thought the above
> would ensure referential integrity, but maybe I need to specify
> something else?

That seems like it should have worked. I don't know of any cases that'd
fail without referential actions (there are some cases with actions and
before triggers or rules), so if you have any leads, that'd be useful.

> Problem #2: I'd like to find all rows in B that point to non-existent
> rows in U.  I can do it with the following sub-select, I believe, but
> it's rather inefficient (EXPLAIN shows both tables would be sequentially
> scanned):
>
>   SELECT * FROM bookmark WHERE id NOT IN (SELECT b.id FROM bookmark b, url u WHERE b.url_id=u.id);
>
> Is there a more efficient way to get the rows from "bookmark"?

I think something like the following would work

SELECT * FROM bookmark WHERE url_id NOT IN (SELECT id FROM url u);

Raising work_mem may help get a better plan as well.