是否有一种方法使用SQL列出给定表的所有外键?我知道表名/模式,我可以把它插入。


当前回答

你可以使用PostgreSQL系统目录。也许您可以查询pg_constraint来请求外键。 您还可以使用信息模式

其他回答

只需替换'您的表名'在下面的查询与您的表名。

简短但贴心的赞,如果对你有用的话。

select * from information_schema.key_column_usage
where constraint_catalog=current_catalog and table_name='your_table_name'
and position_in_unique_constraint notnull;
SELECT conrelid::regclass AS table_name, 
       conname AS foreign_key, 
       pg_get_constraintdef(oid) 
FROM   pg_constraint 
WHERE  contype = 'f' 
AND    connamespace = 'public'::regnamespace   
ORDER  BY conrelid::regclass::text, contype DESC;

https://soft-builder.com/how-to-list-all-foreign-keys-in-postgresql-database

我认为你想要的和@ollyc写的很接近的是:

SELECT
tc.constraint_name, tc.table_name, kcu.column_name, 
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
JOIN information_schema.constraint_column_usage AS ccu
  ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY' AND ccu.table_name='YourTableNameHere';

这将列出所有使用指定表作为外键的表

您可以通过information_schema表来实现这一点。例如:

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

$1 ('my_schema')是模式,$2 ('my_table')是表名:

SELECT ss.conname constraint_name, a.attname column_name, ss.refnamespace fk_table_schema, ss.reflname fk_table_name, af.attname fk_column_name
    FROM  pg_attribute a, pg_attribute af,
        (SELECT r.oid roid, c.conname, rf.relname reflname, information_schema._pg_expandarray(c.conkey) x,
                nrf.nspname refnamespace, rf.oid rfoid, information_schema._pg_expandarray(cf.confkey) xf
            FROM pg_namespace nr, pg_class r, pg_constraint c,
                pg_namespace nrf, pg_class rf, pg_constraint cf
            WHERE nr.oid = r.relnamespace
                AND r.oid = c.conrelid
                AND rf.oid = cf.confrelid
                AND c.conname = cf.conname
                AND nrf.oid = rf.relnamespace
                AND nr.nspname = $1
                AND r.relname = $2) ss
    WHERE ss.roid = a.attrelid AND a.attnum = (ss.x).x AND NOT a.attisdropped
        AND ss.rfoid = af.attrelid AND af.attnum = (ss.xf).x AND NOT af.attisdropped
    ORDER BY ss.conname, a.attname;