From: ogjunk-pgjedan@yahoo.com
To: pgsql-sql@postgresql.org
Subject: Re: Referential integrity broken (8.0.3), sub-select help
Date: Tue, 21 Mar 2006 10:11:40 -0800 (PST)
Message-ID: <20060321181140.35234.qmail@web50303.mail.yahoo.com> (raw)
In-Reply-To: <20060321070446.C70702@megazone.bigpanda.com>
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@megazone.bigpanda.com>
To: ogjunk-pgjedan@yahoo.com
Cc: pgsql-sql@postgresql.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@yahoo.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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ogjunk-pgjedan@yahoo.com
Subject: Re: Referential integrity broken (8.0.3), sub-select help
In-Reply-To: <20060321181140.35234.qmail@web50303.mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox