Get All The Rows Referencing (Via Foreign Keys) a Particular Row in a Table

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

SQL get ALL rows which share foreign keys

With EXISTS:

SELECT m.* FROM mytable m
WHERE EXISTS (
  SELECT 1 FROM mytable
  WHERE id <> m.id AND fk_id = m.fk_id
)

See the demo.

Or with COUNT() window function:

SELECT m.id, m.fk_id 
FROM (
  SELECT *, COUNT(id) OVER (PARTITION BY fk_id) counter  
  FROM mytable
) m
WHERE m.counter > 1

See the demo.

Results:

| id  | fk_id |
| --- | ----- |
| 2   | 200   |
| 3   | 200   |
| 4   | 300   |
| 5   | 300   |
| 6   | 300   |

Fetch all rows for a foreign key if even one row matches condition

You could also use an INNER JOIN to accomplish this instead of a WHERE id IN... statement:

SELECT  A.ID,
        A.Name,
        A.Value
  FROM  props A
    INNER JOIN (SELECT  ID
                  FROM  props
                  WHERE (Name = 'mat' AND value < 50)) B ON B.ID = A.ID 

So in B get all the IDs that match the criteria then simply join them to the props table (alias A).

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 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 to get specific row data from 3 table using foreign key?

Add where [conditions] to the end of your query to get what you want:

For example, to get only the data where id=2

SELECT
airport.id, airport.airport_name, runway.id, runway.runway_no, runway.runway_length, runway.runway_width
FROM
airport
INNER JOIN
runway
ON
airport.id=runway.id 
where airport.id=2

How do I find all references from other tables to a specific row?

You're going to have to query each of the other tables.

I would do it as a UNION query:

  SELECT id, "schools" as whichTable from schools where address_id=1
  UNION
  SELECT id, "parks" as whichTable from parks where address_id=1
  UNION
  ...

so that you only have to run one query and get back the results as a single dataset that you work with.

If you have a list of tables (or a table of tables), you could generate the query programmaticly -- that would save you having to update your query when the tables are changed.

---

Related Topics