primary - postgresql español

Postgres: SQL para listar claves externas de tabla

Question

¿Hay alguna forma de usar SQL para listar todas las claves foráneas para una tabla determinada? Sé el nombre / esquema de la tabla y puedo conectarlo.

Answer 1

Aquí hay una solución de Andreas Joseph Krogh de la lista de correo de PostgreSQL: http://www.postgresql.org/message-id/[email protected]

SELECT source_table::regclass, source_attr.attname AS source_column,
    target_table::regclass, target_attr.attname AS target_column
FROM pg_attribute target_attr, pg_attribute source_attr,
  (SELECT source_table, target_table, source_constraints[i] source_constraints, target_constraints[i] AS target_constraints
   FROM
     (SELECT conrelid as source_table, confrelid AS target_table, conkey AS source_constraints, confkey AS target_constraints,
       generate_series(1, array_upper(conkey, 1)) AS i
      FROM pg_constraint
      WHERE contype = ''f''
     ) query1
  ) query2
WHERE target_attr.attnum = target_constraints AND target_attr.attrelid = target_table AND
      source_attr.attnum = source_constraints AND source_attr.attrelid = source_table;

Esta solución maneja claves foráneas que hacen referencia a múltiples columnas, y evita duplicados (que algunas de las otras respuestas fallan al hacer). Lo único que cambié fueron los nombres de las variables.

Aquí hay un ejemplo que devuelve todas employee columnas de employee que hacen referencia a la tabla de permission :

SELECT source_column
FROM foreign_keys
WHERE source_table = ''employee''::regclass AND target_table = ''permission''::regclass;

Answer 2

Creé una pequeña herramienta para consultar y luego comparar el esquema de la base de datos: volcar el esquema db de PostgreSQL en texto

Hay información sobre FK, pero la respuesta ollyc brinda más detalles.

Answer 3

Creo que lo que estabas buscando y muy cerca de lo que escribió @ollyc es esto:

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

Esto mostrará una lista de todas las tablas que usan su tabla especificada como clave externa

Answer 4

De otra manera:

WITH foreign_keys AS (
    SELECT
      conname,
      conrelid,
      confrelid,
      unnest(conkey)  AS conkey,
      unnest(confkey) AS confkey
    FROM pg_constraint
    WHERE contype = ''f'' -- AND confrelid::regclass = ''your_table''::regclass
)
-- if confrelid, conname pair shows up more than once then it is multicolumn foreign key
SELECT fk.conname as constraint_name,
       fk.confrelid::regclass as referenced_table, af.attname as pkcol,
       fk.conrelid::regclass as referencing_table, a.attname as fkcol
FROM foreign_keys fk
JOIN pg_attribute af ON af.attnum = fk.confkey AND af.attrelid = fk.confrelid
JOIN pg_attribute a ON a.attnum = conkey AND a.attrelid = fk.conrelid
ORDER BY fk.confrelid, fk.conname
;

Answer 5

Emita /d+ tablename en la solicitud de PostgreSQL, además de mostrar los tipos de datos de la columna de la tabla, mostrará los índices y las claves externas.

Answer 6

Escribí una solución que me gusta y uso con frecuencia. El código está en http://code.google.com/p/pgutils/ . Ver la vista pgutils.foreign_keys.

Lamentablemente, el resultado es demasiado prolijo para incluirlo aquí. Sin embargo, puedes probarlo en una versión pública de la base de datos aquí, así:

$ psql -h unison-db.org -U PUBLIC -d unison -c ''select * from pgutils.foreign_keys;

Esto funciona con 8.3 como mínimo. Anticipo actualizarlo, de ser necesario, en los próximos meses.

-Reece

Answer 7

Esta consulta funciona correctamente con claves compuestas también:

select c.constraint_name
    , x.table_schema as schema_name
    , x.table_name
    , x.column_name
    , y.table_schema as foreign_schema_name
    , y.table_name as foreign_table_name
    , y.column_name as foreign_column_name
from information_schema.referential_constraints c
join information_schema.key_column_usage x
    on x.constraint_name = c.constraint_name
join information_schema.key_column_usage y
    on y.ordinal_position = x.position_in_unique_constraint
    and y.constraint_name = c.unique_constraint_name
order by c.constraint_name, x.ordinal_position

Answer 8

Esto es lo que estoy usando actualmente, listará una tabla y sus restricciones fkey [eliminar cláusula de tabla y listará todas las tablas en el catálogo actual]:

SELECT

    current_schema() AS "schema",
    current_catalog AS "database",
    "pg_constraint".conrelid::regclass::text AS "primary_table_name",
    "pg_constraint".confrelid::regclass::text AS "foreign_table_name",

    (
        string_to_array(
            (
                string_to_array(
                    pg_get_constraintdef("pg_constraint".oid),
                    ''(''
                )
            )[2],
            '')''
        )
    )[1] AS "foreign_column_name",

    "pg_constraint".conindid::regclass::text AS "constraint_name",

    TRIM((
        string_to_array(
            pg_get_constraintdef("pg_constraint".oid),
            ''(''
        )
    )[1]) AS "constraint_type",

    pg_get_constraintdef("pg_constraint".oid) AS "constraint_definition"

FROM pg_constraint AS "pg_constraint"

JOIN pg_namespace AS "pg_namespace" ON "pg_namespace".oid = "pg_constraint".connamespace

WHERE
    --fkey and pkey constraints
    "pg_constraint".contype IN ( ''f'', ''p'' )
    AND
    "pg_namespace".nspname = current_schema()
    AND
    "pg_constraint".conrelid::regclass::text IN (''whatever_table_name'')

Answer 9

Extensión a la receta ollyc:

CREATE VIEW foreign_keys_view AS
SELECT
    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'';

Entonces:

SELECT * FROM foreign_keys_view WHERE table_name=''YourTableNameHere'' ;

Answer 10

La respuesta de Ollyc es buena ya que no es específica de Postgres, sin embargo, se descompone cuando la clave externa hace referencia a más de una columna. La siguiente consulta funciona para un número arbitrario de columnas pero depende en gran medida de las extensiones de Postgres:

select 
    att2.attname as "child_column", 
    cl.relname as "parent_table", 
    att.attname as "parent_column",
    conname
from
   (select 
        unnest(con1.conkey) as "parent", 
        unnest(con1.confkey) as "child", 
        con1.confrelid, 
        con1.conrelid,
        con1.conname
    from 
        pg_class cl
        join pg_namespace ns on cl.relnamespace = ns.oid
        join pg_constraint con1 on con1.conrelid = cl.oid
    where
        cl.relname = ''child_table''
        and ns.nspname = ''child_schema''
        and con1.contype = ''f''
   ) con
   join pg_attribute att on
       att.attrelid = con.confrelid and att.attnum = con.child
   join pg_class cl on
       cl.oid = con.confrelid
   join pg_attribute att2 on
       att2.attrelid = con.conrelid and att2.attnum = con.parent

Answer 11

Ninguna de las respuestas existentes me dio los resultados en la forma en que realmente los quería. Así que aquí está mi consulta (gargantuan) para encontrar información sobre claves externas.

Algunas notas:

• Las expresiones utilizadas para generar from_cols y to_cols podrían simplificarse enormemente en Postgres 9.4 y más tarde utilizando WITH ORDINALITY lugar de la función de ventana que uso hackers que estoy usando.
• Esas mismas expresiones se basan en que el planificador de consultas no altera el orden de resultados devuelto por UNNEST . No creo que sea así, pero no tengo claves externas de múltiples columnas en mi conjunto de datos para probar. Agregar las sutilezas 9.4 elimina esta posibilidad por completo.
• La consulta en sí requiere Postgres 9.0 o posterior (8.x no permitió ORDER BY en funciones agregadas)
• Reemplace STRING_AGG con ARRAY_AGG si desea una matriz de columnas en lugar de una cadena separada por comas.

-

SELECT
    c.conname AS constraint_name,
    (SELECT n.nspname FROM pg_namespace AS n WHERE n.oid=c.connamespace) AS constraint_schema,

    tf.name AS from_table,
    (
        SELECT STRING_AGG(QUOTE_IDENT(a.attname), '', '' ORDER BY t.seq)
        FROM
            (
                SELECT
                    ROW_NUMBER() OVER (ROWS UNBOUNDED PRECEDING) AS seq,
                    attnum
                FROM
                    UNNEST(c.conkey) AS t(attnum)
            ) AS t
            INNER JOIN pg_attribute AS a ON a.attrelid=c.conrelid AND a.attnum=t.attnum
    ) AS from_cols,

    tt.name AS to_table,
    (
        SELECT STRING_AGG(QUOTE_IDENT(a.attname), '', '' ORDER BY t.seq)
        FROM
            (
                SELECT
                    ROW_NUMBER() OVER (ROWS UNBOUNDED PRECEDING) AS seq,
                    attnum
                FROM
                    UNNEST(c.confkey) AS t(attnum)
            ) AS t
            INNER JOIN pg_attribute AS a ON a.attrelid=c.confrelid AND a.attnum=t.attnum
    ) AS to_cols,

    CASE confupdtype WHEN ''r'' THEN ''restrict'' WHEN ''c'' THEN ''cascade'' WHEN ''n'' THEN ''set null'' WHEN ''d'' THEN ''set default'' WHEN ''a'' THEN ''no action'' ELSE NULL END AS on_update,
    CASE confdeltype WHEN ''r'' THEN ''restrict'' WHEN ''c'' THEN ''cascade'' WHEN ''n'' THEN ''set null'' WHEN ''d'' THEN ''set default'' WHEN ''a'' THEN ''no action'' ELSE NULL END AS on_delete,
    CASE confmatchtype::text WHEN ''f'' THEN ''full'' WHEN ''p'' THEN ''partial'' WHEN ''u'' THEN ''simple'' WHEN ''s'' THEN ''simple'' ELSE NULL END AS match_type,  -- In earlier postgres docs, simple was ''u''nspecified, but current versions use ''s''imple.  text cast is required.

    pg_catalog.pg_get_constraintdef(c.oid, true) as condef
FROM
    pg_catalog.pg_constraint AS c
    INNER JOIN (
        SELECT pg_class.oid, QUOTE_IDENT(pg_namespace.nspname) || ''.'' || QUOTE_IDENT(pg_class.relname) AS name
        FROM pg_class INNER JOIN pg_namespace ON pg_class.relnamespace=pg_namespace.oid
    ) AS tf ON tf.oid=c.conrelid
    INNER JOIN (
        SELECT pg_class.oid, QUOTE_IDENT(pg_namespace.nspname) || ''.'' || QUOTE_IDENT(pg_class.relname) AS name
        FROM pg_class INNER JOIN pg_namespace ON pg_class.relnamespace=pg_namespace.oid
    ) AS tt ON tt.oid=c.confrelid
WHERE c.contype = ''f'' ORDER BY 1;

Answer 12

Nota: ¡No olvide el orden de la columna mientras lee las columnas de restricción!

SELECT conname, attname
  FROM pg_catalog.pg_constraint c 
  JOIN pg_catalog.pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY (c.conkey)
 WHERE attrelid = ''schema.table_name''::regclass
 ORDER BY conname, array_position(c.conkey, a.attnum)

Answer 13

Para ampliar la excelente respuesta de Martin aquí hay una consulta que le permite filtrar en base a la tabla padre y le muestra el nombre de la tabla secundaria con cada tabla padre para que pueda ver todas las tablas / columnas dependientes en función de las restricciones de clave externa en la tabla padre

select 
    con.constraint_name,
    att2.attname as "child_column", 
    cl.relname as "parent_table", 
    att.attname as "parent_column",
    con.child_table,
    con.child_schema
from
   (select 
        unnest(con1.conkey) as "parent", 
        unnest(con1.confkey) as "child", 
        con1.conname as constraint_name,
        con1.confrelid, 
        con1.conrelid,
        cl.relname as child_table,
        ns.nspname as child_schema
    from 
        pg_class cl
        join pg_namespace ns on cl.relnamespace = ns.oid
        join pg_constraint con1 on con1.conrelid = cl.oid
    where  con1.contype = ''f''
   ) con
   join pg_attribute att on
       att.attrelid = con.confrelid and att.attnum = con.child
   join pg_class cl on
       cl.oid = con.confrelid
   join pg_attribute att2 on
       att2.attrelid = con.conrelid and att2.attnum = con.parent
   where cl.relname like ''%parent_table%''       

Answer 14

Puede hacerlo a través de las tablas information_schema. Por ejemplo:

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

Answer 15

Puede usar los catálogos del sistema PostgreSQL . Tal vez pueda consultar pg_constraint para solicitar claves externas. También puedes usar el esquema de información

Answer 16

Solución adecuada al problema, utilizando information_schema , trabajando con claves de varias columnas, uniendo columnas de diferentes nombres en ambas tablas correctamente y también compatibles con ms sqlsever:

select fks.TABLE_NAME as foreign_key_table_name
, fks.CONSTRAINT_NAME as foreign_key_constraint_name
, kcu_foreign.COLUMN_NAME as foreign_key_column_name
, rc.UNIQUE_CONSTRAINT_NAME as primary_key_constraint_name
, pks.TABLE_NAME as primary_key_table_name
, kcu_primary.COLUMN_NAME as primary_key_column_name
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS fks -- foreign keys
inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu_foreign -- the columns of the above keys
    on fks.TABLE_CATALOG = kcu_foreign.TABLE_CATALOG
    and fks.TABLE_SCHEMA = kcu_foreign.TABLE_SCHEMA
    and fks.TABLE_NAME = kcu_foreign.TABLE_NAME
    and fks.CONSTRAINT_NAME = kcu_foreign.CONSTRAINT_NAME
inner join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc -- referenced constraints
    on rc.CONSTRAINT_CATALOG = fks.CONSTRAINT_CATALOG
    and rc.CONSTRAINT_SCHEMA = fks.CONSTRAINT_SCHEMA
    and rc.CONSTRAINT_NAME = fks.CONSTRAINT_NAME
inner join INFORMATION_SCHEMA.TABLE_CONSTRAINTS pks -- primary keys (referenced by fks)
    on rc.UNIQUE_CONSTRAINT_CATALOG = pks.CONSTRAINT_CATALOG
    and rc.UNIQUE_CONSTRAINT_SCHEMA = pks.CONSTRAINT_SCHEMA
    and rc.UNIQUE_CONSTRAINT_NAME = pks.CONSTRAINT_NAME
inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu_primary
    on pks.TABLE_CATALOG = kcu_primary.TABLE_CATALOG
    and pks.TABLE_SCHEMA = kcu_primary.TABLE_SCHEMA
    and pks.TABLE_NAME = kcu_primary.TABLE_NAME
    and pks.CONSTRAINT_NAME = kcu_primary.CONSTRAINT_NAME
    and kcu_foreign.ORDINAL_POSITION = kcu_primary.ORDINAL_POSITION -- this joins the columns
where fks.TABLE_SCHEMA = ''dbo'' -- replace with schema name
and fks.TABLE_NAME = ''your_table_name'' -- replace with table name
and fks.CONSTRAINT_TYPE = ''FOREIGN KEY''
and pks.CONSTRAINT_TYPE = ''PRIMARY KEY''
order by fks.constraint_name, kcu_foreign.ORDINAL_POSITION

Nota: Existen algunas diferencias entre las implementaciones de sqlserver y potgresql de information_schema que hacen que la respuesta superior arroje resultados diferentes en los dos sistemas: una muestra los nombres de columna para la tabla de clave foránea y la otra para la tabla de clave principal. Por esta razón, decidí usar KEY_COLUMN_USAGE vista en su lugar.

Answer 17

Use el nombre de la clave principal a la que hacen referencia las claves y consulte el esquema de información:

select table_name, column_name
from information_schema.key_column_usage
where constraint_name IN (select constraint_name
  from information_schema.referential_constraints 
  where unique_constraint_name = ''TABLE_NAME_pkey'')

Aquí ''TABLE_NAME_pkey'' es el nombre de la clave principal a la que hacen referencia las claves externas.

Answer 18

psql hace esto, y si comienza psql con:

psql -E

le mostrará exactamente qué consulta se ejecuta. En el caso de encontrar claves foráneas, es:

SELECT conname,
  pg_catalog.pg_get_constraintdef(r.oid, true) as condef
FROM pg_catalog.pg_constraint r
WHERE r.conrelid = ''16485'' AND r.contype = ''f'' ORDER BY 1

En este caso, 16485 es la oid de la tabla que estoy viendo. Puedes obtenerla simplemente echando tu nombre de tabla a regclass como:

WHERE r.conrelid = ''mytable''::regclass

Esquema: califica el nombre de la tabla si no es único (o el primero en tu search_path ):

WHERE r.conrelid = ''myschema.mytable''::regclass

Answer 19

revise la publicación de ff para su solución y no se olvide de marcar esto cuando multa esto útil

http://errorbank.blogspot.com/2011/03/list-all-foreign-keys-references-for.html

SELECT
  o.conname AS constraint_name,
  (SELECT nspname FROM pg_namespace WHERE oid=m.relnamespace) AS source_schema,
  m.relname AS source_table,
  (SELECT a.attname FROM pg_attribute a WHERE a.attrelid = m.oid AND a.attnum = o.conkey[1] AND a.attisdropped = false) AS source_column,
  (SELECT nspname FROM pg_namespace WHERE oid=f.relnamespace) AS target_schema,
  f.relname AS target_table,
  (SELECT a.attname FROM pg_attribute a WHERE a.attrelid = f.oid AND a.attnum = o.confkey[1] AND a.attisdropped = false) AS target_column
FROM
  pg_constraint o LEFT JOIN pg_class c ON c.oid = o.conrelid
  LEFT JOIN pg_class f ON f.oid = o.confrelid LEFT JOIN pg_class m ON m.oid = o.conrelid
WHERE
  o.contype = ''f'' AND o.conrelid IN (SELECT oid FROM pg_class c WHERE c.relkind = ''r'');

Answer 20

SELECT r.conname
      ,ct.table_name
      ,pg_catalog.pg_get_constraintdef(r.oid, true) as condef
  FROM pg_catalog.pg_constraint r, information_schema.constraint_table_usage ct
 WHERE r.contype = ''f'' 
   AND r.conname = ct.constraint_name
 ORDER BY 1