X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 1AB7C9DC9E6 for ; Tue, 21 Mar 2006 11:08:38 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 93946-03 for ; Tue, 21 Mar 2006 11:08:39 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by postgresql.org (Postfix) with ESMTP id 2FCC89DC808 for ; Tue, 21 Mar 2006 11:08:33 -0400 (AST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 1B248350E2; Tue, 21 Mar 2006 07:08:38 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 192A9350E1; Tue, 21 Mar 2006 07:08:38 -0800 (PST) Date: Tue, 21 Mar 2006 07:08:38 -0800 (PST) From: Stephan Szabo To: ogjunk-pgjedan@yahoo.com Cc: pgsql-sql@postgresql.org Subject: Re: Referential integrity broken (8.0.3), sub-select help In-Reply-To: <20060321145839.68211.qmail@web50301.mail.yahoo.com> Message-ID: <20060321070446.C70702@megazone.bigpanda.com> References: <20060321145839.68211.qmail@web50301.mail.yahoo.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.14 required=5 tests=[AWL=0.140] X-Spam-Score: 0.14 X-Spam-Level: X-Archive-Number: 200603/330 X-Sequence-Number: 24676 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)