Find Referenced Field(S) of Foreign Key Constraint

Find referenced field(s) of foreign key constraint

This query adds the referenced column(s) for the foreign key constraint:

SELECT c.confrelid::regclass::text AS referenced_table
      ,string_agg(f.attname, ', ') AS referenced_columns
      ,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)
JOIN   pg_attribute  f ON f.attrelid = c.confrelid
                      AND f.attnum = ANY (confkey)
WHERE  a.attrelid = '"Schema"."Users"'::regclass   -- table name
AND    a.attname  = 'org_id'                       -- column name  
AND    c.contype  = 'f'
GROUP  BY c.confrelid, c.conname, c.oid;

A fk constraint can reference multiple columns. That's the reason for the aggregate function string_agg() in the query.

How can I find out what FOREIGN KEY constraint references a table in SQL Server?

Here it is:

SELECT 
   OBJECT_NAME(f.parent_object_id) TableName,
   COL_NAME(fc.parent_object_id,fc.parent_column_id) ColName
FROM 
   sys.foreign_keys AS f
INNER JOIN 
   sys.foreign_key_columns AS fc 
      ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN 
   sys.tables t 
      ON t.OBJECT_ID = fc.referenced_object_id
WHERE 
   OBJECT_NAME (f.referenced_object_id) = 'YourTableName'

This way, you'll get the referencing table and column name.

Edited to use sys.tables instead of generic sys.objects as per comment suggestion.
Thanks, marc_s

How can I list all foreign keys referencing a given table in SQL Server?

Not sure why no one suggested but I use sp_fkeys to query foreign keys for a given table:

EXEC sp_fkeys 'TableName'

You can also specify the schema:

EXEC sp_fkeys @pktable_name = 'TableName', @pktable_owner = 'dbo'

Without specifying the schema, the docs state the following:

If pktable_owner is not specified, the default table visibility rules
of the underlying DBMS apply.

In SQL Server, if the current user owns a table with the specified
name, that table's columns are returned. If pktable_owner is not
specified and the current user does not own a table with the specified
pktable_name, the procedure looks for a table with the specified
pktable_name owned by the database owner. If one exists, that table's
columns are returned.

How to get foreign key referenced table in Firebird

Lists all referenced by FK tables and fields.

SELECT
    detail_index_segments.rdb$field_name AS field_name,
    master_relation_constraints.rdb$relation_name AS reference_table,
    master_index_segments.rdb$field_name AS fk_field

FROM
    rdb$relation_constraints detail_relation_constraints
    JOIN rdb$index_segments detail_index_segments ON detail_relation_constraints.rdb$index_name = detail_index_segments.rdb$index_name 
    JOIN rdb$ref_constraints ON detail_relation_constraints.rdb$constraint_name = rdb$ref_constraints.rdb$constraint_name -- Master indeksas
    JOIN rdb$relation_constraints master_relation_constraints ON rdb$ref_constraints.rdb$const_name_uq = master_relation_constraints.rdb$constraint_name
    JOIN rdb$index_segments master_index_segments ON master_relation_constraints.rdb$index_name = master_index_segments.rdb$index_name 

WHERE
    detail_relation_constraints.rdb$constraint_type = 'FOREIGN KEY'
    AND detail_relation_constraints.rdb$relation_name = :table_name

SQL-Query for a table with a foreign-key-field that references other foreign-key-fields

This query would work:

SELECT w.ID, worker, c.name AS `combined-Name`, d.department, l.department as 
location FROM workers w
LEFT JOIN combined c ON c.ID = w.combined
LEFT JOIN helper h ON h.ID = c.helper
LEFT JOIN departments d ON d.ID = h.department
LEFT JOIN location l ON l.ID = h.location
GROUP BY w.ID

I used the AS keyword to set the names to your preferred output.

This was tested locally using the provided structures and data.

It's basically 4 simple left joins, and then instead of selecting the ID's I select the name columns of the foreign tables.

The alias on c.name is quoted because we need to escape the special character -

How to check which field in a table a foreign key references

Open Constraints tab. When you click one of the foreign key constraint name, columns wihch is used in the foreign key references will be seen in the Columns view at the bottom of Constraints Tab

How do I see all foreign keys to a table or column?

For a Table:

SELECT 
  TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_SCHEMA = '<database>' AND
  REFERENCED_TABLE_NAME = '<table>';

For a Column:

SELECT 
  TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_SCHEMA = '<database>' AND
  REFERENCED_TABLE_NAME = '<table>' AND
  REFERENCED_COLUMN_NAME = '<column>';

Basically, we changed REFERENCED_TABLE_NAME with REFERENCED_COLUMN_NAME in the where clause.

How to find foreign key dependencies in SQL Server?

The following query will help to get you started. It lists all Foreign Key Relationships within the current database.

SELECT
    FK_Table = FK.TABLE_NAME,
    FK_Column = CU.COLUMN_NAME,
    PK_Table = PK.TABLE_NAME,
    PK_Column = PT.COLUMN_NAME,
    Constraint_Name = C.CONSTRAINT_NAME
FROM
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
    ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
    ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
    ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
            SELECT
                i1.TABLE_NAME,
                i2.COLUMN_NAME
            FROM
                INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
            INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
                ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
            WHERE
                i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
           ) PT
    ON PT.TABLE_NAME = PK.TABLE_NAME

You can also view relationships graphically within SQL Server Management studio within Database Diagrams.

How do I SELECT all (table, column) referencing the same column as foreign key

OK so I have done it :-)

The following query returns every column in database referencing FOO_TABLE.foo_column as foreign key:

SELECT 
fk.table_schema as schema, --optional in my case, I only use public
fk.table_name as table,
substring(fk.constraint_def, 14, position(')' in constraint_def)-14) as column
FROM
    (SELECT tc.*,
     pg_get_constraintdef(c.oid) as constraint_def
     --,c.oid 
     from pg_constraint c
     left join information_schema.table_constraints tc
     on c.conname = tc.constraint_name
     where tc.constraint_type = 'FOREIGN KEY')
     as fk    
WHERE constraint_def ILIKE format('%%REFERENCES %s(%s)%%',
                                 'FOO_TABLE','foo_column')  
ORDER BY table_schema,table_name,3;

Test it there

I've found information_schema.table_constraints get most of it informations from pg_constraint wich include internal reference OID, and there is a built-in function pg_get_constraintdef() returning the definition of constraint object from it's OID.

Then, some substring of the definition is enough to extract the column_name AND
filter over the referenced column with the (I)LIKE filter I've prepared in my question.

----------------- OTHER ANSWER ------------------

Another acceptable query I've built from improving @Abelisto suggestion :

SELECT table_name, column_name
FROM (SELECT table_name, SUBSTR(column_name, 2, LENGTH(column_name)-2) as column_name,
             referenced_table,SUBSTR(referenced_column, 2, LENGTH(referenced_column)-2) as referenced_column
      FROM(select
             conrelid::regclass::text as table_name,
            (select array_agg(attname) from pg_attribute where conrelid = attrelid and attnum = any(conkey))::text as column_name,
             confrelid::regclass::text as referenced_table,
            (select array_agg(attname) from pg_attribute where confrelid = attrelid and attnum = any(confkey))::text as referenced_column
             from pg_constraint where contype = 'f' 
             ) b ) a
WHERE (referenced_table, referenced_column) = 'FOO_TABLE','foo_column');

Test it there

I don't think performances really matters there, so one should pick regarding what are the side needs. I think my first solution have the advantage to get the constraint definition if you want to alter it (say, for exemple, add a ON UPDATE CASCADE clause), yet the second seems more "compact" for this very purpose of just returning the table.column.

How to list table foreign keys

You can do this via the information_schema tables. For example:

SELECT
    tc.table_schema, 
    tc.constraint_name, 
    tc.table_name, 
    kcu.column_name, 
    ccu.table_schema AS foreign_table_schema,
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name 
FROM 
    information_schema.table_constraints AS tc 
    JOIN information_schema.key_column_usage AS kcu
      ON tc.constraint_name = kcu.constraint_name
      AND tc.table_schema = kcu.table_schema
    JOIN information_schema.constraint_column_usage AS ccu
      ON ccu.constraint_name = tc.constraint_name
      AND ccu.table_schema = tc.table_schema
WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_name='mytable';

---

Related Topics