Re: Referential integrity broken (8.0.3), sub-select help - Mailing list pgsql-sql

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)