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.tablesWHERE 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_nameWHERE constraint_type = 'FOREIGN KEY' AND tc.table_nameIN (SELECT table_name FROM information_schema.tablesWHERE 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_columnFROM 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_nameWHERE constraint_type = 'PRIMARY KEY' AND tc.table_nameIN (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_nameFROM 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_nameWHERE constraint_type = 'FOREIGN KEY' AND tc.table_nameIN (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!