List foreign keys with columns in Vertica database

Bart Gawrych - Dataedo Team Bart Gawrych 2019-10-31

Table of Contents:


    Query below allows to find out columns used in foreign key constraint and corresponding them columns from primary table in a database.

    Query

    select table_name || '.' || table_schema as foreign_table,
           '>-' rel,
           reference_table_schema || '.' || reference_table_name as primary_table,
           ordinal_position as 'no.',
           column_name as fk_column,
           '=' as join,
           reference_column_name as pk_column,
           constraint_name
    from v_catalog.foreign_keys
    order by table_schema,
             table_name,
             constraint_name,
             ordinal_position;
    

    Columns

    • foreign_table - foreign table name preceded by schema name
    • rel - relationship symbol implicating direction
    • primary_table - referenced table schema name preceded by name
    • no - column position in constraint
    • fk_column - foreign key column name
    • join - "=" symbol indicating join operation for pair of columns
    • pk_column - corresponding primary key column
    • constraint_name - foreign key constraint name

    Rows

    • One row represents one column used by foreign key. Each column of foreign key appears separately.
    • Scope of rows: all clumns used as foreign key
    • Ordered by FK's table schema, FK's table name, FK's constraint name and key position within constraint

    Sample results

    0
    There are no comments. Click here to write the first comment.

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy.
    Accept