Обсуждение: Primary key reference count
Hi, I'm trying to find which of the tables in pg_catalog contains the amount of references to a primary key, but I can't seem to find it. Google queries are also less than successful. Is it even stored in the catalog?
am 19.04.2006, um 14:01:03 +0200 mailte Wiebe Cazemier folgendes: > Hi, > > I'm trying to find which of the tables in pg_catalog contains the amount of > references to a primary key, but I can't seem to find it. Google queries are > also less than successful. Is it even stored in the catalog? Yes, http://www.postgresql.org/docs/8.1/interactive/catalog-pg-constraint.html HTH, Andreas -- Andreas Kretschmer (Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net=== Schollglas Unternehmensgruppe ===
On Wednesday 19 April 2006 15:09, A. Kretschmer wrote: > Yes, > http://www.postgresql.org/docs/8.1/interactive/catalog-pg-constraint.html Either I'm blind, or this is not what I'm looking for. I need to know the amount of references _to_ a primary key. If you have a customer with two orders and one invoice, the reference count for the primary key of the custormer would be 3. I need to know if I can delete rows which are not referenced anywhere, without catching the relational constraint exception. The reason I can't catch the exception, is that I want the exception to arrive at my application and/or tests.
On Wednesday 19 April 2006 15:33, Wiebe Cazemier wrote: > Either I'm blind, or this is not what I'm looking for. I need to know the > amount of references _to_ a primary key. If you have a customer with two > orders and one invoice, the reference count for the primary key of the > custormer would be 3. > > I need to know if I can delete rows which are not referenced anywhere, > without catching the relational constraint exception. The reason I can't > catch the exception, is that I want the exception to arrive at my application > and/or tests. Never mind. I found another solution.
On Wednesday 19 April 2006 17:00, Wiebe Cazemier wrote: > Never mind. I found another solution. Whoops. Posted to quickly there. I do still need to know the amount of references to the primary key.
On Apr 19 02:01, Wiebe Cazemier wrote: > I'm trying to find which of the tables in pg_catalog contains the amount of > references to a primary key, but I can't seem to find it. Here's simple query, to list which table's which columns references to a specific table. (Hope this is what you asked for.) SELECT TBL.table_name, COL.column_name FROM information_schema.referential_constraints AS REF INNER JOIN information_schema.table_constraintsAS TBL USING (constraint_name) INNER JOIN information_schema.constraint_column_usageAS COL USING (constraint_name) INNER JOIN information_schema.table_constraintsAS PRI ON (PRI.constraint_name = REF.unique_constraint_name) WHERE PRI.table_name= 'pri_id'; -- Your target table goes here. This is a quick & dirty hack. Just wanted to show the possibility of the idea using information schema. (You don't want to deal with internal tables, ain't?) Regards.
On Wednesday 19 April 2006 19:21, Volkan YAZICI wrote: > Here's simple query, to list which table's which columns references to a > specific table. (Hope this is what you asked for.) > > SELECT TBL.table_name, COL.column_name > FROM information_schema.referential_constraints AS REF > INNER JOIN information_schema.table_constraints AS TBL > USING (constraint_name) > INNER JOIN information_schema.constraint_column_usage AS COL > USING (constraint_name) > INNER JOIN information_schema.table_constraints AS PRI > ON (PRI.constraint_name = REF.unique_constraint_name) > WHERE PRI.table_name = 'pri_id'; -- Your target table goes here. > > This is a quick & dirty hack. Just wanted to show the possibility of the > idea using information schema. (You don't want to deal with internal > tables, ain't?) I know the reply is somewhat late, but indeed, this would seem to be what I was looking for. And, using the information schema instead of pg_catalog would seem to be a better idea as well :)