Search code examples
sqlpostgresqlforeign-keysconstraints

Find the referenced table name using table, field and schema name

I have a requirement where I need to find the referenced table name (Primary key table name) by a particular field in a table (Foreign key table) using this field name, table name (where this field resides) and the schema name (where the table and thereby the field resides)

For example:

Schema1.TableA
  Id (Integer, PK)  
  Name varchar


Schema2.TableB
  Id (integer, PK)  
  A_Id (integer, FK referencing TableA.Id)  
  Name varchar

I need to pass A_Id, TableB and Schema2 to a function and get Schema1.TableA as result.

I am using Postgres 8.3.

Solution

  • If you don't need this to be portable to another RDBMS (or another major Postgres version) it is faster and simpler to use the catalog tables in pg_catalog instead of the standard information schema:

    SELECT c.confrelid::regclass::text AS referenced_table
     , c.conname AS fk_name
     , pg_get_constraintdef(c.oid) AS fk_definition
FROM   pg_attribute a 
JOIN   pg_constraint c ON (c.conrelid, c.conkey[1]) = (a.attrelid, a.attnum)
WHERE  a.attrelid = '"Schema2"."TableB"'::regclass   -- table name
AND    a.attname  = 'A_Id'                           -- column name  
AND    c.contype  = 'f'
ORDER  BY referenced_table, c.contype DESC;

    Returns:

    referenced_table fk_name fk_definition
    Schema1.TableA b1_fkey FOREIGN KEY ("B_id") REFERENCES "Schema1"."TableA"("A_id")

    You only asked for the first column. I added two columns for context.

    This returns all referenced tables by all foreign keys involving the given column name - including FK constraints on multiple columns.

    The name is automatically schema-qualified if necessary according to the current search_path. It is also double-quoted automatically where needed.

    Check out details of pg_constraint and pg_attribute in the manual. More about object identifier types, too.

    Related: