获取复合外键关联表依赖及字段顺序的正确SQL查询方法

Got it, I get why your current query isn't working right for composite foreign keys—PostgreSQL keeps track of the exact order those columns were defined in, and your original query isn't respecting that order. Tools like PgAdmin use the ordinal position metadata to show constraints correctly, so we just need to tweak your query to do the same.

The core issue here is that composite foreign keys have a defined column sequence, and your existing query doesn't account for the ordinal_position field that PostgreSQL uses to store this order. Here's the corrected query that matches what you see in PgAdmin:

SELECT
    tc.constraint_name AS nom_fk,
    tc.table_name AS table_out,
    kcu.column_name AS colonne_out,
    ccu.table_name AS table_in,
    ccu.column_name AS colonne_in,
    kcu.ordinal_position AS fk_column_order -- Optional, but useful to verify order
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 kcu.constraint_name = ccu.constraint_name
    AND kcu.table_schema = ccu.table_schema
    AND kcu.ordinal_position = ccu.ordinal_position -- Critical for matching column order
WHERE
    tc.constraint_type = 'FOREIGN KEY'
ORDER BY
    tc.table_name,
    tc.constraint_name,
    kcu.ordinal_position; -- Ensures columns appear in their defined order

What Changed & Why: #

• Added kcu.ordinal_position = ccu.ordinal_position to the join: This ensures each foreign key column maps to the correct referenced column in the exact sequence they were created. Without this, composite FK columns might be mismatched or out of order.
• Ordered results by ordinal_position: Groups all columns for a single composite FK together and displays them in their defined order, just like PgAdmin does.
• Included fk_column_order (optional): This column makes it easy to confirm the sequence is correct at a glance.

Example of Correct Output: #

If you have a composite FK fk_order_details (order_id, product_id) referencing orders(id, product_id), the query will return rows in this order:

This is exactly the structure you'd see when viewing the constraint in PgAdmin.

内容的提问来源于stack exchange，提问作者Monsieur Jack