PostgreSQL creating multiple reference foriegn key to each schema is incorrect

[jeebendu]

Preconditions

• HeidiSQL version:12.7
• Database type and version: PostgreSQL 16.0
• OS: Windows

Describe the bug

**i am using postgress table to create multiple schemas with same table definition.

if i am creating 3 schema in each specific schema 3 foreign key are creating which is incorrect.

Correct Behavior : it should create one foreign key instead of multiple ref from another schema**

Reproduceble script :
https://raw.githubusercontent.com/jeebendu/schema-based-multi-tenancy/main/src/main/resources/db/migration/postgress_fk_issue.sql

select tc.table_schema,
tc.constraint_type,
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
WHERE tc.constraint_type = 'FOREIGN KEY'
AND tc.table_schema = 'tenant1'
and tc.table_name = 'note';

Screenshots or Crash reports

PGAdmin showing correctly after export the table

[ansgarbecker]

Thanks for the comprehensive comparison and for the test script.

Fixed in the next build.

Issue was in the query for retrieving foreign keys, due to a missing join condition for constraint_column_usage.constraint_schema.

Please update via Help > Check for updates, and report back.

[jeebendu]

@ansgarbecker
Tested and working fine in night build
Thanks