Gráfico de llaves foráneas
Ayer, uno de los sistemas de monitorización de red que utilizamos en la universidad (NAV - Network Administration Visualized) tuvo problemas con una de las consultas DELETE que mandaba a la base de datos PostgreSQL que utiliza.
La base de datos que utiliza NAV es un poco complicada, con muchas claves foráneas, triggers y rules para garantizar la integridad de los datos. En el proceso de busqueda del fallo, una de las cosas que queria tener era una relación de todas las tablas y sus claves foráneas para hacerme una idea de como las diferentes tablas estaban relacionadas entre si. En una base de datos pequeña y sin muchas relaciones esto no seria un problema pero a medida que la cosa crece, se va complicando rápidamente.
Necesitaba algo rápido y fácil que me diera esta información sin tener que instalar ningún programa adicional, además solamente me interesaba la relación de claves foráneas y no una representación completa de la base de datos.
Y como no hay nada más rápido que una consola con psql y una consulta SQL apropiada, me puse manos a la obra para generar una consulta SQL que me generase una salida que pudiese utilizar con Graphviz, para crear un gráfico con las relaciones. En debian/ubuntu, Graphviz se puede instalar con: sudo apt-get install graphviz
La consulta que se me ocurrio es la siguiente:
SELECT DISTINCT ON ('"' || fkey_table || '" -> "' || pkey_table || '(' || pkey_column || ')";') '"' || fkey_table || '" -> "' || pkey_table || '(' || pkey_column || ')";' FROM ( SELECT a.constraint_name, b.column_name AS fkey_column, b.table_name AS fkey_table FROM information_schema.referential_constraints a INNER JOIN information_schema.key_column_usage b ON a.constraint_name = b.constraint_name ) AS fkey_constraints INNER JOIN ( SELECT a.unique_constraint_name, b.column_name AS pkey_column, b.table_name AS pkey_table, a.constraint_name FROM information_schema.referential_constraints a INNER JOIN information_schema.constraint_column_usage b ON a.unique_constraint_name = b.constraint_name ) AS pkey_constraints ON fkey_constraints.constraint_name = pkey_constraints.constraint_name;
Esta consulta te devuelve una lista con lineas del tipo:
"accountalertqueue" -> "account(id)"; "accountalertqueue" -> "account(id)"; "accountalertqueue" -> "alertq(alertqid)"; "accountalertqueue" -> "alertsubscription(id)"; "accountgroup_accounts" -> "account(id)"; "accountgroup_accounts" -> "accountgroup(id)"; ........... "type" -> "vendor(vendorid)"; "vlan" -> "nettype(nettypeid)"; "vlan" -> "org(orgid)"; "vlan" -> "usage(usageid)";
En donde el primer termino antes del simbolo "->" es el nombre de una tabla en vuestra base de datos y el segundo una clave foránea (tabla(columna)) definida en esta tabla.
Ahora lo único que nos queda es copiar y pegar el resultado en un editor de texto, añadirle un par de lineas extras, grabarlo en un fichero y ejecutar Graphviz sobre el mismo. Podemos grabar un fichero llamado claves_foraneas.dot con estos datos:
digraph fkey_pkey_relacion{ K=1; Aqui vendria la salida de ejecutar el comando SQL en vustro sistema }
y ejecutar este comando para generar un fichero PNG:
fdp -Tpng -o claves_foraneas.png claves_foraneas.dot
El resultado que conseguí lo podeis ver a continuación:

Comentarios
Excelente aporte
Excelente aporte es justo lo que estaba buscando, gracias y saludos desde Mexico
como mostrar llaves foráneas
como mostrar llaves foráneas