Heidisql: Foreign key reference tables are blank

[MarcelloTheArcane]

Steps to reproduce this issue

1. Step 1; Go to a table and select Foreign keys
2. Step 2; Add a key name and select a column
3. Step 3; Click the reference table dropdown
4. Then I get... nothing!

Current behavior

No tables show in the dropdown:

Expected behavior

All the tables should be listed in the Reference table box for me to select.

Environment

• HeidiSQL version: 10.2.0.5599
  
• Database system and version: PostgreSQL 10.5
  
• Operating system: Windows 10
  

[ansgarbecker]

I suppose you are the first one reorting that issue for PostgreSQL. And I think the feature was not well tested by myself on PostgreSQL. So let's get foreign keys to work on PostgreSQL.

[MarcelloTheArcane]

Yep, that was me.

[rwilliams]

Also already created foreign keys do not show up on postgres. Using postgres 11.5.3

[halmai]

The bug is still present with

Version 10.3.0.5837 (64 Bit)
Compiled on: 2020-01-19 19:55:50

[ansgarbecker]

Could you please check with the latest build (5848 currently)

[rwilliams]

Reference table does not populate and created foreign keys do not show. Build 5849. psql (PostgreSQL) 12.1 (Debian 12.1-1.pgdg100+1)

[ansgarbecker]

What would be a good SQL approach to retrieve foreign keys of one table? I know I can join pg_index with pg_class to get many index details, but for HeidiSQL that query should deliver also the reference table and columns from source and reference table:

SELECT * FROM pg_index i, pg_class c
WHERE
    i.indexrelid=c.oid

[rwilliams]

I found this on Stack overflow

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' AND tc.table_name='truck_setups_workers';

"table_schema"  "constraint_name"   "table_name"    "column_name"   "foreign_table_schema"  "foreign_table_name"    "foreign_column_name"
"public"    "fk_rails_d00006069d"   "truck_setups_workers"  "truck_setup_id"    "public"    "truck_setups"  "id"
"public"    "fk_rails_a1cce43842"   "truck_setups_workers"  "employee_id"   "public"    "employees" "id"

[rwilliams]

In 5850 it looks like the reference tables are now populating.

[ansgarbecker]

In 5850 it looks like the reference tables are now populating.

Really, in PostgreSQL? Or on MySQL or MS SQL?

[rwilliams]

PostgreSQL

Just tested 5771 and it definitely doesn't populate there.

[ansgarbecker]

Oh yes, I was mixing this issue with #158 , which complains about non working detection of foreign keys. This one is just for the reference tables, which I fixed in conjunction with 7ab47a31434cda49e14604d7b2d2b1e79c89b5aa for #150 .
Thanks for your feedback!