Date: Sat, 9 Aug 2003 22:30:20 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: eVl One <evl@my-mail.com.ua>
Cc: Bruno Wolff III <bruno@wolff.to>, <pgsql-sql@postgresql.org>
Subject: Re: How to check: is some key referenced from sometable
In-Reply-To: <18618711976.20030810004432@my-mail.com.ua>
Message-ID: <20030809222523.K28657-100000@megazone.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Sun, 10 Aug 2003, eVl One wrote:

> Hello, Bruno.
>
> You wrote 9 08 2003, 18:08:09:
>
> BWI> On Mon, Aug 04, 2003 at 11:17:56 +0300,
> BWI>   evl@my-mail.com.ua wrote:
> >> Please help.
> >>
> >> Need a boolean function which returns true if given key in table is
> >> referensed from another table(s).
>
> BWI> Use "exists" with a subselect. Something like:
> BWI> select exists(select 1 from table where table.key = 'value');
>
> Thanx for advice, but way I know and it can't be used.
>  That's why I've got too many and/or too big tables from which
> such key is referenced. I mean (in some simplified way):
>
>  table A (             -- main table A
>   id SERIAL,
>   PRIMARY KEY(id)
>  );
>
>  tableBxx (            -- a lot of tables which got a.id as FOREIGN KEY
>   ...                  -- too much to check 'em all with EXISTS queries
>   a_id int4 REFERENCES a(id),      -- without significant perfomance loss
>   ...
>  );
>
> So I need:
>    "silent delete" - i.e. when trying to DELETE row from A I'll not fall
>   out with "$1 referential integrity violation - key in A still
>   referenced from Bxx", but silently doesn't delete row ('cause run
>   it from function and need function to executes farther after delete);
>    "something to check reference" - system (potgresql) is very quickly
>   realizes that key is referenced from another table, maybe this
>   information may be accessed through some system relations, or so?

No, postgresql runs a select on each of the referencing tables for
matching rows to figure it out itself.  Theoretically it'd be possible to
keep track of it somewhere else with triggers when you insert/update a
value in the various referencing tables and use that data to determine if
a row has references if selecting from the various tables won't work for
you.