0

Imagine, you have two tables. And each Table has a lot of columns.

I did not create the database, so I really don't know what columns are related. One problem is there's no same Column name in between two tables, So I can not figure out by column name.

How can I find the column which is related to two different tables?

Dong-gyun Kim
  • 411
  • 5
  • 23

1 Answers1

2

If there are foreign keys between the tables, then you can find the relationship between them. To do this, you can call \d on a table and see the foreign keys associated with its columns. Without explicit foreign keys, then there is no way to determine the relationship between tables except by talking with the developers and/or technical decision-makers.

An example of how the foreign key relationship is displayed in psql:

postgres=# alter table pgbench_accounts add constraint "pgb_accounts_branches_fk" foreign key (bid) references pgbench_branches (bid);
ALTER TABLE
postgres=# \d pgbench_accounts
              Table "public.pgbench_accounts"
  Column  |     Type      | Collation | Nullable | Default 
----------+---------------+-----------+----------+---------
 aid      | integer       |           | not null | 
 bid      | integer       |           |          | 
 abalance | integer       |           |          | 
 filler   | character(84) |           |          | 
Indexes:
    "pgbench_accounts_pkey" PRIMARY KEY, btree (aid)
Foreign-key constraints:
    "pgb_accounts_branches_fk" FOREIGN KEY (bid) REFERENCES pgbench_branches(bid)

postgres=# \d pgbench_branches
              Table "public.pgbench_branches"
  Column  |     Type      | Collation | Nullable | Default 
----------+---------------+-----------+----------+---------
 bid      | integer       |           | not null | 
 bbalance | integer       |           |          | 
 filler   | character(88) |           |          | 
Indexes:
    "pgbench_branches_pkey" PRIMARY KEY, btree (bid)
Referenced by:
    TABLE "pgbench_accounts" CONSTRAINT "pgb_accounts_branches_fk" FOREIGN KEY (bid) REFERENCES pgbench_branches(bid)

postgres=#
richyen
  • 8,114
  • 4
  • 13
  • 28
  • Thanks. But How can I check foreign keys are in a table? for example, I want to know if there are any relations between table A and table B. Just type \d table A and see if there's a foreign keys? Is there are some mark in a specific col? – Dong-gyun Kim Mar 07 '20 at 11:12
  • 1
    Correct, just type `\d` and you will see any relevant foreign keys in the `Foreign-key constraints:` or `Referenced by:` sections. If they do not show up, then there is no foreign key defined. See my updated post for an example – richyen Mar 09 '20 at 05:54
  • Just one more simple question, what if I change the data in Parent table, does it affect the Child table? – Dong-gyun Kim Mar 10 '20 at 08:34
  • 1
    @DonggyunKim, you will not be able to change the referenced column in the Parent table unless 1) a row with the Parent table's new value exists in the Child table (i.e. one-to-many relationship or many-to-many relationship) or 2) you delete the referenced row in the Child table first (one-to-one relationship) – richyen Mar 10 '20 at 16:08