v9.6.16
I have a query which shows the parents and children in FK relations, along
with the parent column name, but can't seem to find the child column names.
Is there a way to find the child column names without having to dig into
pg_constraint?
Thanks
test=# select ccu.table_schema||'.'||ccu.table_name as parent_table,
test-# ccu.column_name as parent_column,
test-# tc.table_schema||'.'||tc.table_name as child_table,
test-# ccu.constraint_schema||'.'||ccu.constraint_name as con_name
test-# from information_schema.table_constraints tc,
test-# information_schema.constraint_column_usage ccu
test-# where tc.constraint_type = 'FOREIGN KEY'
test-# and tc.constraint_schema = ccu.constraint_schema
test-# and tc.constraint_name = ccu.constraint_name
test-# order by parent_table, child_table, ccu.column_name
test-# ;
parent_table | parent_column | child_table | con_name
---------------------+---------------+---------------------+------------------------------------
public.inventory | inv_id | public.sales_detail |
public.sales_detail_sd_inv_id_fkey
public.sales_header | parent_id | public.sales_detail |
public.sales_detail_id_fkey
(2 rows)
test=# \d inventory
Table "public.inventory"
Column | Type | Modifiers
-------------+------------------------+-----------
inv_id | integer | not null
description | character varying(255) |
Indexes:
"inventory_pkey" PRIMARY KEY, btree (inv_id)
Referenced by:
TABLE "sales_detail" CONSTRAINT "sales_detail_sd_inv_id_fkey" FOREIGN
KEY (sd_inv_id) REFERENCES inventory(inv_id)
test=# \d sales_header
Table "public.sales_header"
Column | Type | Modifiers
-----------+---------+-----------
parent_id | integer | not null
Indexes:
"sales_header_pkey" PRIMARY KEY, btree (parent_id)
Referenced by:
TABLE "sales_detail" CONSTRAINT "sales_detail_id_fkey" FOREIGN KEY
(child_id) REFERENCES sales_header(parent_id)
test=# \d sales_detail
Table "public.sales_detail"
Column | Type | Modifiers
-----------+---------+-----------
child_id | integer | not null
seq | integer | not null
sd_inv_id | integer |
Indexes:
"sales_detail_pkey" PRIMARY KEY, btree (child_id, seq)
Foreign-key constraints:
"sales_detail_id_fkey" FOREIGN KEY (child_id) REFERENCES
sales_header(parent_id)
"sales_detail_sd_inv_id_fkey" FOREIGN KEY (sd_inv_id) REFERENCES
inventory(inv_id)
--
Angular momentum makes the world go 'round.
