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

Hi,

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?

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"?

Thanks,
Otis

----- Original Message ----
From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: ogjunk-pgjedan(at)yahoo(dot)com
Cc: pgsql-sql(at)postgresql(dot)org
Sent: Tuesday, March 21, 2006 10:08:38 AM
Subject: Re: [SQL] Referential integrity broken (8.0.3), sub-select help

On Tue, 21 Mar 2006 ogjunk-pgjedan(at)yahoo(dot)com wrote:

> I've got 2 tables, "url" (U), and "bookmark" (B), with "bookmark" pointing to "url" via FK.

That's not what your schema below has. Your fragment below has URL
pointing to bookmark.

> Somehow I ended up with some rows in B referencing non-existent rows in U.

With the below, this is entirely possible, since you're only guaranteeing
that URLs have valid bookmarks not the other way around. Are you sure the
below is actually what you have?

> This sounds super strange and dangerous to me, and it's not clear to me how/why PG let this happen.
> I'm using 8.0.3.
>
> Here are the table references I just mentioned:
>
> Table "bookmark":
> id SERIAL
> CONSTRAINT pk_bookmark_id PRIMARY KEY
>
> Table "url":
> url_id INTEGER
> CONSTRAINT fk_bookmark_id REFERENCES bookmark(id)