-1

Im trying to collect the foreign key mapping from system tables. And I used this below query.

query 1:

select
    kcu.table_schema,
    kcu.table_name as foreign_table,
    string_agg(kcu.column_name, ', ') as fk_columns,
    rel_tco.table_name as primary_table,
    kcu.constraint_name
from
    information_schema.table_constraints tco
join information_schema.key_column_usage kcu on
    tco.constraint_schema = kcu.constraint_schema
    and tco.constraint_name = kcu.constraint_name
join information_schema.referential_constraints rco on
    tco.constraint_schema = rco.constraint_schema
    and tco.constraint_name = rco.constraint_name
join information_schema.table_constraints rel_tco on
    rco.unique_constraint_schema = rel_tco.constraint_schema
    and rco.unique_constraint_name = rel_tco.constraint_name
where
    tco.constraint_type = 'FOREIGN KEY'
group by
    kcu.table_schema,
    kcu.table_name,
    rel_tco.table_name,
    rel_tco.table_schema,
    kcu.constraint_name
order by
    kcu.table_schema,
    kcu.table_name;

But this won't provide the Primary tables columns that are pointing as Fk. So I found this query on Stackoverflow.

query 2

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

Now this query gives individual columns. Lets say there is a FK like (logid, item) then I'll get two rows. I thought to use string_agg with my first query, I got the results but it endup duplicates. Like(logid, item,logid, item)

query with duplicates:

select
    kcu.table_schema,
    kcu.table_name as foreign_table,
    string_agg(kcu.column_name, ', ') as fk_columns,
    rel_tco.table_name as primary_table,
    kcu.constraint_name,
    string_agg(ccu.column_name, ', ') as pk_columns
    from 
    information_schema.table_constraints tco
join information_schema.key_column_usage kcu on
    tco.constraint_schema = kcu.constraint_schema
    and tco.constraint_name = kcu.constraint_name
join information_schema.referential_constraints rco on
    tco.constraint_schema = rco.constraint_schema
    and tco.constraint_name = rco.constraint_name
join information_schema.table_constraints rel_tco on
    rco.unique_constraint_schema = rel_tco.constraint_schema
    and rco.unique_constraint_name = rel_tco.constraint_name
    JOIN information_schema.constraint_column_usage AS ccu
      ON ccu.constraint_name = tco.constraint_name 
where
    tco.constraint_type = 'FOREIGN KEY'
group by
    kcu.table_schema,
    kcu.table_name,
    rel_tco.table_name,
    rel_tco.table_schema,
    kcu.constraint_name
order by
    kcu.table_schema,
    kcu.table_name;

Can someone help me to fix this query?

Example FK:

ALTER TABLE myschema.user ADD CONSTRAINT view_option_fk01 FOREIGN KEY (account_id, user_id) REFERENCES myschema.account(account_id, user_id);

Expected Query output:

enter image description here

TheDataGuy
  • 2,712
  • 6
  • 37
  • 89
  • Query to do what exactly? [mre] – philipxy Oct 09 '20 at 04:48
  • 1
    Try `SELECT DISTINCT...` ? – SILENT Oct 09 '20 at 05:13
  • `string_agg(distinct kcu.column_name, ', ')`? –  Oct 09 '20 at 05:25
  • Please [use text, not images/links, for text--including tables & ERDs](https://meta.stackoverflow.com/q/285551/3404097). Use images only for what cannot be expressed as text or to augment text. Include a legend/key & explanation with an image. PS An example helps. But it illustrates what? Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. When giving a business relation(ship)/association or table (base or query result), say what a row in it states about the business situation in terms of its column values. – philipxy Oct 09 '20 at 05:54
  • Possible duplicate of [How to select unique records by SQL](https://stackoverflow.com/q/1641718/3404097) – philipxy Oct 09 '20 at 07:31
  • The accepted answer answers a faq. is a faq. Please before considering posting read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. Reflect your research. See [ask] & the voting arrow mouseover texts. If you post a question, use one phrasing as title. – philipxy Oct 09 '20 at 07:34

1 Answers1

0

Use SELECT DISTINCT... to remove duplicates

SILENT
  • 3,916
  • 3
  • 38
  • 57