Redshift Observatory

System Table Tracker

System view information_schema.referential_constraints version 1.0.52931 / 2023-07-07

View Text

SELECT CAST(current_database() AS information_schema.sql_identifier) AS constraint_catalog,
       CAST(ncon.nspname AS information_schema.sql_identifier) AS constraint_schema,
       CAST(con.conname AS information_schema.sql_identifier) AS constraint_name,
       CAST(CASE
              WHEN npkc.nspname IS NULL
                THEN CAST(NULL AS name)
              ELSE current_database()
            END AS information_schema.sql_identifier) AS unique_constraint_catalog,
       CAST(npkc.nspname AS information_schema.sql_identifier) AS unique_constraint_schema,
       CAST(pkc.conname AS information_schema.sql_identifier) AS unique_constraint_name,
       CAST(CASE
              WHEN con.confmatchtype = CAST('f' AS "char")
                THEN CAST('FULL' AS text)
              WHEN con.confmatchtype = CAST('p' AS "char")
                THEN CAST('PARTIAL' AS text)
              WHEN con.confmatchtype = CAST('u' AS "char")
                THEN CAST('NONE' AS text)
              ELSE CAST(NULL AS text)
            END AS information_schema.character_data) AS match_option,
       CAST(CASE
              WHEN con.confupdtype = CAST('c' AS "char")
                THEN CAST('CASCADE' AS text)
              WHEN con.confupdtype = CAST('n' AS "char")
                THEN CAST('SET NULL' AS text)
              WHEN con.confupdtype = CAST('d' AS "char")
                THEN CAST('SET DEFAULT' AS text)
              WHEN con.confupdtype = CAST('r' AS "char")
                THEN CAST('RESTRICT' AS text)
              WHEN con.confupdtype = CAST('a' AS "char")
                THEN CAST('NO ACTION' AS text)
              ELSE CAST(NULL AS text)
            END AS information_schema.character_data) AS update_rule,
       CAST(CASE
              WHEN con.confdeltype = CAST('c' AS "char")
                THEN CAST('CASCADE' AS text)
              WHEN con.confdeltype = CAST('n' AS "char")
                THEN CAST('SET NULL' AS text)
              WHEN con.confdeltype = CAST('d' AS "char")
                THEN CAST('SET DEFAULT' AS text)
              WHEN con.confdeltype = CAST('r' AS "char")
                THEN CAST('RESTRICT' AS text)
              WHEN con.confdeltype = CAST('a' AS "char")
                THEN CAST('NO ACTION' AS text)
              ELSE CAST(NULL AS text)
            END AS information_schema.character_data) AS delete_rule
FROM pg_namespace AS ncon
     INNER JOIN pg_constraint AS con ON ncon.oid = con.connamespace
     INNER JOIN pg_class AS c ON con.conrelid = c.oid
     INNER JOIN pg_user AS u ON c.relowner = u.usesysid
     LEFT JOIN (pg_constraint AS pkc
                INNER JOIN pg_namespace AS npkc ON pkc.connamespace = npkc.oid)
        ON con.confrelid = pkc.conrelid
       AND information_schema._pg_keysequal(con.confkey, pkc.conkey)
WHERE c.relkind = CAST('r' AS "char")
  AND con.contype = CAST('f' AS "char")
  AND (pkc.contype = CAST('p' AS "char")
    OR pkc.contype = CAST('u' AS "char")
    OR pkc.contype IS NULL)
  AND u.usename = CAST("current_user"() AS name)

Home 3D Друк Blog Bring-Up Times Consultancy Cross-Region Benchmarks Email Forums IRC Mailing Lists Reddit Redshift Price Tracker Redshift Version Tracker Redshift Workbench System Table Tracker The Known Universe Twitter White Papers