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!
