Обсуждение: Search for restricting foreign keys
Is there a way in PG 7.3, given a field, to find out what other tables & records are linked to it via a foreign key? EG: create table cities (id serial primary key, title varchar not null); insert into cities(title) values ('San Fransisco'); insert into cities(title) values ('Los Angeles'); create table stores (id serial primary key, city integer not null references cities(id), title varchar); insert into stores(city, title) values (1, 'North City'); insert into stores(city, title) values (2, 'Central District'); insert into stores (city, title) values (1, 'Beachfront"); Given the above, and I wanted to know all the tables/records that relate to id 1, San Fransisco, and get a result something like: table | primary key stores | 1 stores | 3 Does such functionality exist in PG? Isn't it already doing essentially this when attempting to delete a record with other records linked to it? Currently, I do this by attempting to delete a record in a transaction, and trap the error - it's a terrible way to do this, and sometimes I'm already in a transaction. -Ben -- "The best way to predict the future is to invent it." - XEROX PARC slogan, circa 1978
On Mon, Jan 24, 2005 at 08:35:45PM -0800, Benjamin Smith wrote: > Is there a way in PG 7.3, given a field, to find out what other tables & > records are linked to it via a foreign key? See the "System Catalogs" chapter in the documentation: http://www.postgresql.org/docs/7.3/static/catalogs.html The pg_constraint table contains, among other things, foreign key constraints. By querying it and joining it with pg_attribute, pg_class, and pg_namespace, you could get a list of tables and columns that have foreign key constraints on the given table and column; from that you could build queries to find out which rows in those tables match the given value. You could wrap all this code in a set-returning function. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Michael Fuhr wrote: > On Mon, Jan 24, 2005 at 08:35:45PM -0800, Benjamin Smith wrote: >>Is there a way in PG 7.3, given a field, to find out what other tables & >>records are linked to it via a foreign key? > > The pg_constraint table contains, among other things, foreign key > constraints. By querying it and joining it with pg_attribute, > pg_class, and pg_namespace, you could get a list of tables and > columns that have foreign key constraints on the given table and > column; from that you could build queries to find out which rows > in those tables match the given value. You could wrap all this > code in a set-returning function. I just needed such a function yesterday, and wrote one. Here it is, use it for whatever you want ;-) create type foreignkey (, table_referenced as regclass, fields_referenced as varchar[], table_referencing as regclass, fields_referencing as varchar[] ) ; create or replace function f_get_pks(regclass) returns foreignkey as ' select pg_constraint.confrelid::regclass as table_referenced, array(select pg_attribute.attname from pg_catalog.pg_attribute where pg_attribute.attrelid = pg_constraint.confrelid and pg_attribute.attnum = ANY(pg_constraint.confkey) order by alienkey.f_array_pos( pg_constraint.confkey, pg_attribute.attnum ) )::varchar[] as fields_referenced, pg_constraint.conrelid::regclass as table_referencing, array(select pg_attribute.attname from pg_catalog.pg_attribute where pg_attribute.attrelid = pg_constraint.conrelid and pg_attribute.attnum = ANY(pg_constraint.conkey) order by alienkey.f_array_pos( pg_constraint.confkey, pg_attribute.attnum ) )::varchar[] as fields_referencing from pg_catalog.pg_constraint where pg_constraint.confrelid = $1 and pg_constraint.contype = 'f' ' language 'sql' stable ; Of course this could be a view too - just remove the where-clause containing "= $1", and wrap it in a create view statement. greetings, Florian Pflug