How to Find Tables Which Reference a Particular Row via a Foreign Key

How can I find tables which reference a particular row via a foreign key?

NULL values in referencing columns

This query produces the DML statement to find all rows in all tables, where a column has a foreign-key constraint referencing another table but hold a NULL value in that column:

WITH x AS (
 SELECT c.conrelid::regclass    AS tbl
      , c.confrelid::regclass   AS ftbl
      , quote_ident(k.attname)  AS fk
      , quote_ident(pf.attname) AS pk
 FROM   pg_constraint c
 JOIN   pg_attribute  k ON (k.attrelid, k.attnum) = (c.conrelid, c.conkey[1])
 JOIN   pg_attribute  f ON (f.attrelid, f.attnum) = (c.confrelid, c.confkey[1])
 LEFT   JOIN pg_constraint p  ON p.conrelid = c.conrelid AND p.contype = 'p'
 LEFT   JOIN pg_attribute  pf ON (pf.attrelid, pf.attnum)
                               = (p.conrelid, p.conkey[1])
 WHERE  c.contype   = 'f'
 AND    c.confrelid = 'fk_tbl'::regclass  -- references to this tbl
 AND    f.attname   = 'fk_tbl_id'         -- and only to this column
)
SELECT string_agg(format(
'SELECT %L AS tbl
     , %L AS pk
     , %s::text AS pk_val
     , %L AS fk
     , %L AS ftbl
FROM   %1$s WHERE %4$s IS NULL'
                  , tbl
                  , COALESCE(pk 'NONE')
                  , COALESCE(pk 'NULL')
                  , fk
                  , ftbl), '
UNION ALL
') || ';'
FROM   x;

Produces a query like this:

SELECT 'some_tbl' AS tbl
     , 'some_tbl_id' AS pk
     , some_tbl_id::text AS pk_val
     , 'fk_tbl_id' AS fk
     , 'fk_tbl' AS ftbl
FROM   some_tbl WHERE fk_tbl_id IS NULL
UNION ALL
SELECT 'other_tbl' AS tbl
     , 'other_tbl_id' AS pk
     , other_tbl_id::text AS pk_val
     , 'some_name_id' AS fk
     , 'fk_tbl' AS ftbl
FROM   other_tbl WHERE some_name_id IS NULL;

Produces output like this:

    tbl    |     pk       | pk_val |    fk        |  ftbl
-----------+--------------+--------+--------------+--------
 some_tbl  | some_tbl_id  | 49     | fk_tbl_id    | fk_tbl
 some_tbl  | some_tbl_id  | 58     | fk_tbl_id    | fk_tbl
 other_tbl | other_tbl_id | 66     | some_name_id | fk_tbl
 other_tbl | other_tbl_id | 67     | some_name_id | fk_tbl

• Does not cover multi-column foreign or primary keys reliably. You have to make the query more complex for this.

• I cast all primary key values to text to cover all types.

• Adapt or remove these lines to find foreign key pointing to an other or any column / table:

  AND    c.confrelid = 'fk_tbl'::regclass
  AND    f.attname = 'fk_tbl_id' -- and only this column
  

• Tested with PostgreSQL 9.1.4. I use the pg_catalog tables. Realistically nothing of what I use here is going to change, but that is not guaranteed across major releases. Rewrite it with tables from information_schema if you need it to work reliably across updates. That is slower, but sure.

• I did not sanitize table names in the generated DML script, because quote_ident() would fail with schema-qualified names. It is your responsibility to avoid harmful table names like "users; DELETE * FROM users;". With some more effort, you can retrieve schema-name and table name separately and use quote_ident().

---

NULL values in referenced columns

My first solution does something subtly different from what you ask, because what you describe (as I understand it) is non-existent. The value NULL is "unknown" and cannot be referenced. If you actually want to find rows with a NULL value in a column that has FK constraints pointing to it (not to the particular row with the NULL value, of course), then the query can be much simplified:

WITH x AS (
 SELECT c.confrelid::regclass   AS ftbl
       ,quote_ident(f.attname)  AS fk
       ,quote_ident(pf.attname) AS pk
       ,string_agg(c.conrelid::regclass::text, ', ') AS referencing_tbls
 FROM   pg_constraint c
 JOIN   pg_attribute  f ON (f.attrelid, f.attnum) = (c.confrelid, c.confkey[1])
 LEFT   JOIN pg_constraint p  ON p.conrelid = c.confrelid AND p.contype = 'p'
 LEFT   JOIN pg_attribute  pf ON (pf.attrelid, pf.attnum)
                               = (p.conrelid, p.conkey[1])
 WHERE  c.contype = 'f'
 -- AND    c.confrelid = 'fk_tbl'::regclass  -- only referring this tbl
 GROUP  BY 1, 2, 3
)
SELECT string_agg(format(
'SELECT %L AS ftbl
     , %L AS pk
     , %s::text AS pk_val
     , %L AS fk
     , %L AS referencing_tbls
FROM   %1$s WHERE %4$s IS NULL'
                  , ftbl
                  , COALESCE(pk, 'NONE')
                  , COALESCE(pk, 'NULL')
                  , fk
                  , referencing_tbls), '
UNION ALL
') || ';'
FROM   x;

Finds all such rows in the entire database (commented out the restriction to one table). Tested with Postgres 9.1.4 and works for me.

I group multiple tables referencing the same foreign column into one query and add a list of referencing tables to give an overview.

Get all the rows referencing (via foreign keys) a particular row in a table

You can do one of the following:

1) Add reference_count field to master table. Using triggers on detail tables increase the reference count whenever a row with this master_id is added. Decrease the count, when row gets deleted. When reference_count reaches 0 - delete the record.

2) Use pg_constraint table (details here) to get the list of referencing tables and create a dynamic SQL query.

3) Create triggers on every detail table, that deletes master_id in main table. Silence error messages with BEGIN ... EXCEPTION ... END.

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 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

SQL how do you query for tables that refer to a specific foreign key value?

Not an ideal one, but should return what is needed (list of tables):

declare @tableName sysname, @value sql_variant

set @tableName = 'A'
set @value = 17

declare @sql nvarchar(max)

create table #Value (Value sql_variant)
insert into #Value values (@value)

create table #Tables (Name sysname, [Column] sysname)
create index IX_Tables_Name on #Tables (Name)

set @sql = 'declare @value sql_variant
select @value = Value from #Value

'
set @sql = @sql + replace((
select
    'insert into #Tables (Name, [Column])
select ''' + quotename(S.name) + '.' + quotename(T.name) + ''', ''' + quotename(FC.name) + '''
where exists (select 1 from ' + quotename(S.name) + '.' + quotename(T.name) + ' where ' + quotename(FC.name) + ' = @value)
'
from
    sys.columns C
    join sys.foreign_key_columns FKC on FKC.referenced_column_id = C.column_id and FKC.referenced_object_id = C.object_id
    join sys.columns FC on FC.object_id = FKC.parent_object_id and FC.column_id = FKC.parent_column_id
    join sys.tables T on T.object_id = FKC.parent_object_id
    join sys.schemas S on S.schema_id = T.schema_id
where
    C.object_id = object_id(@tableName)
    and C.name = 'ID'
order by S.name, T.name
for xml path('')), '
', CHAR(13))

--print @sql
exec(@sql)

select distinct Name
from #Tables
order by Name

drop table #Value
drop table #Tables

How to find foreign key dependencies of a specific row?

You can use the INFORMATION_SCHEMA views to generate select statements to display the rows in question. I have only tested this against the tables provided in the question, but it could be expanded to work in cases where the keys are multiple columns.

declare @table_schema nvarchar(50) = 'dbo',
        @table_name nvarchar(50) = 'TableA',
        @id int = 1

select fk_col.TABLE_SCHEMA, fk_col.TABLE_NAME, fk_col.COLUMN_NAME, 
    'select * from ' + fk_col.TABLE_SCHEMA + '.' + fk_col.TABLE_NAME + ' t1 '
        + ' inner join ' + @table_schema + '.' + @table_name + ' t2 '
        + ' on t1.' + fk_col.COLUMN_NAME + ' = t2.' + pk_col.COLUMN_NAME
        + ' where t2.' + pk_col.COLUMN_NAME + ' = ' + cast(@id as nvarchar)

from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk

    join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE pk_col
        on pk.CONSTRAINT_SCHEMA = pk_col.CONSTRAINT_SCHEMA
        and pk.CONSTRAINT_NAME = pk_col.CONSTRAINT_NAME

    join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS fk 
        on pk.CONSTRAINT_SCHEMA = fk.UNIQUE_CONSTRAINT_SCHEMA 
        and pk.CONSTRAINT_NAME = fk.UNIQUE_CONSTRAINT_NAME

    join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE fk_col
        on fk_col.CONSTRAINT_SCHEMA = fk.CONSTRAINT_SCHEMA
        and fk_col.CONSTRAINT_NAME = fk.CONSTRAINT_NAME

where pk.TABLE_SCHEMA = @table_schema 
    and pk.TABLE_NAME = @table_name
    and pk.CONSTRAINT_TYPE = 'PRIMARY KEY'

The select statements generated:

select * from dbo.TableB t1  inner join dbo.TableA t2  on t1.TableAId = t2.Id where t2.Id = 1
select * from dbo.TableC t1  inner join dbo.TableA t2  on t1.TableAId = t2.Id where t2.Id = 1

and the query results:

Id          TableAId    Id
----------- ----------- -----------
1           1           1
2           1           1

Id          TableAId    Id
----------- ----------- -----------
1           1           1

How can I find which tables reference a given table in Oracle SQL Developer?

No. There is no such option available from Oracle SQL Developer.

You have to execute a query by hand or use other tool (For instance PLSQL Developer has such option). The following SQL is that one used by PLSQL Developer:

select table_name, constraint_name, status, owner
from all_constraints
where r_owner = :r_owner
and constraint_type = 'R'
and r_constraint_name in
 (
   select constraint_name from all_constraints
   where constraint_type in ('P', 'U')
   and table_name = :r_table_name
   and owner = :r_owner
 )
order by table_name, constraint_name

Where r_owner is the schema, and r_table_name is the table for which you are looking for references. The names are case sensitive

---

Be careful because on the reports tab of Oracle SQL Developer there is the option "All tables / Dependencies" this is from ALL_DEPENDENCIES which refers to "dependencies between procedures, packages, functions, package bodies, and triggers accessible to the current user, including dependencies on views created without any database links.". Then, this report have no value for your question.

---

Related Topics