如何找到通过外键引用特定行的表? [英] How can I find tables which reference a particular row via a foreign key?
问题描述
给定这样的结构:
CREATE TABLE reference_table (
reference_table_key numeric NOT NULL,
reference_value numeric,
CONSTRAINT reference_table_pk PRIMARY KEY (reference_table_key)
);
CREATE TABLE other_table (
other_table_key numeric NOT NULL,
reference_table_key numeric,
CONSTRAINT other_table_pk PRIMARY KEY (other_table_key),
ONSTRAINT other_table_reference_fk FOREIGN KEY (reference_table_key)
REFERENCES reference_table (reference_table_key) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE SET NULL
);
CREATE TABLE another_table (
another_table_key numeric NOT NULL,
do_stuff_key numeric,
CONSTRAINT another_table_pk PRIMARY KEY (another_table_key),
ONSTRAINT another_table_reference_fk FOREIGN KEY (do_stuff_key)
REFERENCES reference_table (reference_table_key) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE SET NULL
);
--there are 50-60 tables which have similar foreign key references to reference_table
我想编写一个查询，告诉我 other_table 和 another_table 中的主键以及可能更多 reference_value 为 NULL 的表.
I want to write a query that tells me the primary keys in other_table and another_table and potentially more tables where reference_value is NULL.
在伪代码中:
SELECT table_name, table_primary_key, table_fk_column_name
FROM ?????? some PG table ???????, reference_table
WHERE reference_table.reference_value IS NULL;
结果看起来像:
table_name | table_primary_key | table_fk_column_name | reference_table_pk
---------------------------------------------------------------------------
other_table | 2 | reference_table_key | 7
other_table | 4 | reference_table_key | 56
other_table | 45 | reference_table_key | 454
other_table | 65765 | reference_table_key | 987987
other_table | 11 | reference_table_key | 3213
another_table | 3 | do_stuff_key | 4645
another_table | 5 | do_stuff_key | 43546
another_table | 7 | do_stuff_key | 464356
unknown_table | 1 | unkown_column_key | 435435
unknown_table | 1 | some_other_column_key | 34543
unknown_table | 3 | unkown_column_key | 124
unknown_table | 3 | some_other_column_key | 123
这类似于 Postgres: SQL to list表外键 .该问题显示了表结构.我想找到具体的实例.
This is similar to, but not a duplicate of Postgres: SQL to list table foreign keys . That question shows the table structure. I want to find specific instances.
基本上，如果我要 DELETE FROM reference_table WHERE reference_value IS NULL;，postgres 必须在内部做一些事情来确定它需要在第 2 行中设置 reference_table_keyother_table 为 NULL.我想看看那些行会是什么.
Essentially if I were to DELETE FROM reference_table WHERE reference_value IS NULL;, postgres has to do something internally to figure out that it needs to set reference_table_key in row 2 in other_table to NULL. I want to see what those rows would be.
有没有可以做到这一点的查询?是否有一个修饰符可以传递给 DELETE 调用，告诉我哪些表/行/列会受到该 DELETE 的影响?
Is there a query that can do this? Is there a modifier that I can pass to a DELETE call that would tell me what tables/rows/columns would be affected by that DELETE?
推荐答案
引用列中的NULL值
此查询生成 DML 语句以在所有表中查找 所有行，其中列具有外键约束引用另一个表但持有 NULL 该列中的值:
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;
产生这样的查询:
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;
产生这样的输出:
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.
我将所有主键values转换为
text以覆盖所有类型.I cast all primary key values to
textto 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使用 PostgreSQL 9.1.4 测试.我使用
pg_catalog表.实际上，我在这里使用的任何东西都不会改变，但这并不能保证在主要版本中.如果您需要它在更新中可靠地工作，请使用information_schema中的表重写它.这会更慢，但可以肯定.Tested with PostgreSQL 9.1.4. I use the
pg_catalogtables. Realistically nothing of what I use here is going to change, but that is not guaranteed across major releases. Rewrite it with tables frominformation_schemaif you need it to work reliably across updates. That is slower, but sure.我没有在生成的 DML 脚本中清理表名，因为
quote_ident()会因模式限定名称而失败.您有责任避免使用像"users; DELETE * FROM users;"这样的有害表名.通过更多的努力，您可以分别检索架构名称和表名称并使用quote_ident().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 usequote_ident().我的第一个解决方案与您所要求的略有不同，因为您所描述的(据我所知)是不存在的.
NULL的值是未知的"并且不能被引用.如果您确实想在具有 FK 约束指向 to 的列中查找具有NULL值的行(而不是具有NULL值，当然)，那么查询可以大大简化:My first solution does something subtly different from what you ask, because what you describe (as I understand it) is non-existent. The value
NULLis "unknown" and cannot be referenced. If you actually want to find rows with aNULLvalue in a column that has FK constraints pointing to it (not to the particular row with theNULLvalue, 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;在整个数据库中查找所有此类行(注释掉对一个表的限制).使用 Postgres 9.1.4 测试并为我工作.
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.
这篇关于如何找到通过外键引用特定行的表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
