pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to check: is some key referenced from sometable
5+ messages / 5 participants
[nested] [flat]
* How to check: is some key referenced from sometable
@ 2003-08-04 08:17 evl@my-mail.com.ua
0 siblings, 1 reply; 5+ messages in thread
From: evl@my-mail.com.ua @ 2003-08-04 08:17 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to check: is some key referenced from sometable
@ 2003-08-09 15:08 Bruno Wolff III <bruno@wolff.to>
parent: evl@my-mail.com.ua
0 siblings, 1 reply; 5+ messages in thread
From: Bruno Wolff III @ 2003-08-09 15:08 UTC (permalink / raw)
To: evl@my-mail.com.ua; +Cc: pgsql-sql
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');
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to check: is some key referenced from sometable
@ 2003-08-09 21:44 eVl One <evl@my-mail.com.ua>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 2 replies; 5+ messages in thread
From: eVl One @ 2003-08-09 21:44 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: pgsql-sql
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?
Thanx for attention.
--
Best regards,
eVl
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to check: is some key referenced from sometable
@ 2003-08-10 05:30 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: eVl One <evl@my-mail.com.ua>
1 sibling, 0 replies; 5+ messages in thread
From: Stephan Szabo @ 2003-08-10 05:30 UTC (permalink / raw)
To: eVl One <evl@my-mail.com.ua>; +Cc: Bruno Wolff III <bruno@wolff.to>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to check: is some key referenced from sometable
@ 2003-08-12 16:46 Jonathan Gardner <jgardner@jonathangardner.net>
parent: eVl One <evl@my-mail.com.ua>
1 sibling, 0 replies; 5+ messages in thread
From: Jonathan Gardner @ 2003-08-12 16:46 UTC (permalink / raw)
To: eVl One <evl@my-mail.com.ua>; Bruno Wolff III <bruno@wolff.to>; +Cc: pgsql-sql
-----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-----
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2003-08-12 16:46 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-08-04 08:17 How to check: is some key referenced from sometable evl@my-mail.com.ua
2003-08-09 15:08 ` Bruno Wolff III <bruno@wolff.to>
2003-08-09 21:44 ` eVl One <evl@my-mail.com.ua>
2003-08-10 05:30 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-12 16:46 ` Jonathan Gardner <jgardner@jonathangardner.net>
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