evergreen.pg_all_foreign_keys (view)

↑ evergreen schema

This is a database view, not a base table. It has no triggers, indexes, or FK constraints of its own. Querying this view may be more efficient than joining the underlying tables directly.

Columns

Column Type Nullable Notes

fk_schema_name

name

Yes

fk_table_name

name

Yes

fk_constraint_name

name

Yes

fk_table_oid

oid

Yes

fk_columns

name[]

Yes

pk_schema_name

name

Yes

pk_table_name

name

Yes

pk_constraint_name

name

Yes

pk_table_oid

oid

Yes

pk_index_name

name

Yes

pk_columns

name[]

Yes

match_type

text

Yes

on_delete

text

Yes

on_update

text

Yes

is_deferrable

boolean

Yes

is_deferred

boolean

Yes

View Definition

 SELECT n1.nspname AS fk_schema_name,
    c1.relname AS fk_table_name,
    k1.conname AS fk_constraint_name,
    c1.oid AS fk_table_oid,
    _pg_sv_column_array(k1.conrelid, k1.conkey) AS fk_columns,
    n2.nspname AS pk_schema_name,
    c2.relname AS pk_table_name,
    k2.conname AS pk_constraint_name,
    c2.oid AS pk_table_oid,
    ci.relname AS pk_index_name,
    _pg_sv_column_array(k1.confrelid, k1.confkey) AS pk_columns,
        CASE k1.confmatchtype
            WHEN 'f'::"char" THEN 'FULL'::text
            WHEN 'p'::"char" THEN 'PARTIAL'::text
            WHEN 'u'::"char" THEN 'NONE'::text
            ELSE NULL::text
        END AS match_type,
        CASE k1.confdeltype
            WHEN 'a'::"char" THEN 'NO ACTION'::text
            WHEN 'c'::"char" THEN 'CASCADE'::text
            WHEN 'd'::"char" THEN 'SET DEFAULT'::text
            WHEN 'n'::"char" THEN 'SET NULL'::text
            WHEN 'r'::"char" THEN 'RESTRICT'::text
            ELSE NULL::text
        END AS on_delete,
        CASE k1.confupdtype
            WHEN 'a'::"char" THEN 'NO ACTION'::text
            WHEN 'c'::"char" THEN 'CASCADE'::text
            WHEN 'd'::"char" THEN 'SET DEFAULT'::text
            WHEN 'n'::"char" THEN 'SET NULL'::text
            WHEN 'r'::"char" THEN 'RESTRICT'::text
            ELSE NULL::text
        END AS on_update,
    k1.condeferrable AS is_deferrable,
    k1.condeferred AS is_deferred
   FROM pg_constraint k1
     JOIN pg_namespace n1 ON n1.oid = k1.connamespace
     JOIN pg_class c1 ON c1.oid = k1.conrelid
     JOIN pg_class c2 ON c2.oid = k1.confrelid
     JOIN pg_namespace n2 ON n2.oid = c2.relnamespace
     JOIN pg_depend d ON d.classid = 'pg_constraint'::regclass::oid AND d.objid = k1.oid AND d.objsubid = 0 AND d.deptype = 'n'::"char" AND d.refclassid = 'pg_class'::regclass::oid AND d.refobjsubid = 0
     JOIN pg_class ci ON ci.oid = d.refobjid AND ci.relkind = 'i'::"char"
     LEFT JOIN pg_depend d2 ON d2.classid = 'pg_class'::regclass::oid AND d2.objid = ci.oid AND d2.objsubid = 0 AND d2.deptype = 'i'::"char" AND d2.refclassid = 'pg_constraint'::regclass::oid AND d2.refobjsubid = 0
     LEFT JOIN pg_constraint k2 ON k2.oid = d2.refobjid AND (k2.contype = ANY (ARRAY['p'::"char", 'u'::"char"]))
  WHERE k1.conrelid <> 0::oid AND k1.confrelid <> 0::oid AND k1.contype = 'f'::"char" AND _pg_sv_table_accessible(n1.oid, c1.oid);
Previous
Next