Re: [GENERAL] Queries for Diagramming Schema Keys - Mailing list pgsql-general

Hi,

 

I'm trying to write an application (using libpqxx/C++) which creates graphical images of large and complex relations between tables (basically an SVG image with hot spots for drilling down on details). I need to essentially create icons of tables and their columns, and then draw interconnecting lines where foreign keys are involved, and to distinctly label primary keys, foreign keys, and non-key columns. Eventually this will have knowledge of an XML file loading scheme and be able to reverse engineer the required load order (imagine approximately 1,000 tables with many foreign keys and file loads which may take hours for each failure to load). I need some advice on using ANSI/information_schema queries to accomplish this.

 

Thus I have this query to list all tables:

SELECT table_name
  FROM information_schema.tables
WHERE table_schema='public'
   AND table_type='BASE TABLE';

 

...this seems to work ok. The next query is to find all foreign keys...this seems to sort of work, but may have issues:

SELECT
    tc.table_name AS local_table,
    kcu.column_name AS key_column,
    ccu.table_name AS fk_table,
    ccu.column_name AS fk_column 
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
IN (SELECT table_name
  FROM information_schema.tables
WHERE table_schema='public'
   AND table_type='BASE TABLE');

 

This is my query to find all primary keys which are not foreign keys, and this definitely is not 100% correct:

SELECT DISTINCT
    tc.table_name AS local_table,
    kcu.column_name AS key_column
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 = 'PRIMARY KEY' AND tc.table_name
IN (SELECT table_name
  FROM information_schema.tables
 WHERE table_schema='public'
   AND table_type='BASE TABLE')
AND (tc.table_name, kcu.column_name)
NOT IN (
SELECT
    tc.table_name,
    kcu.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
IN (SELECT table_name
  FROM information_schema.tables
 WHERE table_schema='public'
   AND table_type='BASE TABLE')
)
ORDER BY local_table, key_column;

 

I am completely at a loss how I would query for all columns which are neither primary nor foreign keys. Would anyone have a suggestion for something like this:

SELECT table_name, non_key_column

FROM ...

WHERE ...

ORDER BY table_name, non_key_column

 

Any advice on any of the queries would be appreciated!

 

Thanks!