如何知道外键是否在删除子句上级联

Question

由于对我每天使用的PostgreSQL数据库的误解，在数据库模式包含的155个表上，一些带有外键的表没有">on delete cascade"子句，这会导致严重的麻烦，正如你所料。

我想纠正这一点，使用 PGAdmin，我能够看到表的所有 FK，但如果 FK 作为子句，则看不到。而不是为每个表手动删除每个 FK 并使用"删除级联"创建新 FK，我想知道对于特定表，外键的完整定义。像这样，我不会删除已经具有子句的外键。

Answer 1

您可以在pg_constraint中查看：

postgres=# create table car (id int primary key, name text);
CREATE TABLE
postgres=# create table driver (id int, car int references car(id) on delete cascade);
CREATE TABLE
postgres=# select connamespace, conname, c.relname as child_table, p.relname as parent_table, confdeltype 
from pg_constraint 
join pg_class c on c.oid=conrelid 
join pg_class p on p.oid=confrelid;
connamespace |     conname     | child_table | parent_table | confdeltype 
--------------+-----------------+-------------+--------------+-------------
2200 | driver_car_fkey | driver      | car          | c
(1 row)

这将显示所有没有ON DELETE CASCADE的外键(及其源表和目标表(：

select connamespace, conname, c.relname as child_table, p.relname as parent_table, confdeltype
from pg_constraint 
join pg_class c on c.oid=conrelid 
join pg_class p on p.oid=confrelid
where confdeltype <> 'c';

文档中的更多信息

披露：我在EnterpriseDB(EDB(工作

Answer 2

此查询将显示在不级联删除的情况下定义的所有外部约束以及定义它们的表：

SELECT conname AS constraint_name,
conrelid::regclass AS table
FROM pg_constraint
WHERE contype = 'f'
AND confdeltype <> 'c';

Answer 3

约束可以从pg_constraint查询。外键约束在列contype中具有'f'。可以从列confdeltype中执行删除操作。对于级联操作，它持有'c'。因此，您希望所有约束都contype'f'，confdeltype不'c'。

您可以联接涉及的表的pg_class，并为其架构pg_namespace。这样，您还可以将搜索限制为特定架构的表。

在conkey引用和confkey引用都存储为相应表中列的序号数组。您可以从pg_attribute获取名称。

以下查询将为您提供架构中任何表的所有外键约束，public其删除操作不会与表的架构名称和约束中涉及的列一起级联。

SELECT con.conname,
con.confdeltype,
nsp.nspname,
rel.relname,
(SELECT array_agg(att.attname ORDER BY un.ord)
FROM unnest(con.conkey) WITH ORDINALITY un (attnum, ord)
INNER JOIN pg_attribute att
ON att.attnum = un.attnum
WHERE att.attrelid = rel.oid) conkeyattnames,
fnsp.nspname,
frel.relname,
(SELECT array_agg(att.attname ORDER BY un.ord)
FROM unnest(con.confkey) WITH ORDINALITY un (attnum, ord)
INNER JOIN pg_attribute att
ON att.attnum = un.attnum
WHERE att.attrelid = frel.oid) confkeyattnames
FROM pg_constraint con
INNER JOIN pg_class rel
ON rel.oid = con.conrelid
INNER JOIN pg_namespace nsp
ON nsp.oid = rel.relnamespace
INNER JOIN pg_class frel
ON frel.oid = con.confrelid
INNER JOIN pg_namespace fnsp
ON fnsp.oid = frel.relnamespace
WHERE con.contype = 'f'
AND con.confdeltype <> 'c'
AND nsp.nspname = 'public';