Thread: How to check: is some key referenced from sometable

Please help.

Need a boolean function which returns true if given key in table is
referensed from another table(s).

-----------------------
see You again when ...                  eVl

On Mon, Aug 04, 2003 at 11:17:56 +0300, 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).

Use "exists" with a subselect. Something like:
select exists(select 1 from table where table.key = 'value');

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
allwith 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
functionto executes farther after delete);  "something to check reference" - system (potgresql) is very quickly
realizesthat key is referenced from another table, maybe this information may be accessed through some system
relations,or so?
 
Thanx for attention. 
-- 
Best regards,             eVl

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.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Saturday 09 August 2003 14:44, eVl One wrote:
> 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?
> Thanx for attention.

You may want to check out the "ON DELETE" and "ON UPDATE" clauses of the
FOREIGN KEY syntax. This will help you keep you data consistent.

Otherwise, if you want to know what is referencing something, you'll
have to query all of those tables. Your database structure should be
well-defined and thought out, so you should be able to figure out what
all is referencing it. If you need it for day-to-day queries, you may
want to rethink your design.

If you like, take a look at the pg_trigger table. The way PostgreSQL
manages the foreign key references is through triggers. You may be able
to identify which triggers are which, and from there, determine which
tables are referencing which tables.

- --
Jonathan Gardner <jgardner@jonathangardner.net>
Live Free, Use Linux!
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQE/ORn/WgwF3QvpWNwRAsJ5AJ9iGmopncQsyizb6yIh2CvCQsSmwQCg0UZ/
xImGLW35bdAJSuzCWc7yxBU=
=eQGH
-----END PGP SIGNATURE-----