Ora2Pg v25 generates invalid foreign keys for Oracle reference partitions when DISABLE_PARTITION is enabled

[Blajimir]

Summary

Ora2Pg v25 can generate invalid PostgreSQL foreign key DDL for Oracle schemas that use reference partitioning when the migration is configured with DISABLE_PARTITION 1.

The generated child-table foreign key may include the parent table partition key even though that column does not exist on the child table. PostgreSQL then rejects the generated schema SQL with:

ERROR: column "<partition_key_column>" referenced in foreign key constraint does not exist

Environment

• Ora2Pg: v25.0
• Export type: TYPE TABLE
• PostgreSQL target: PostgreSQL 16
• Relevant Ora2Pg settings:
  • DISABLE_PARTITION 1
  • PG_VERSION 16

Generic Reproduction Shape

Oracle metadata:

• Parent table is partitioned by a range/list/hash partition key.
• Child table is reference-partitioned through a foreign key to the parent table.
• The child foreign key is logically defined only on the child FK column, for example:

CHILD_TABLE(PARENT_ID) -> PARENT_TABLE(ID)

• The child table does not contain the parent partition key column.

Expected PostgreSQL DDL when partitions are disabled:

ALTER TABLE child_table
  ADD CONSTRAINT fk_child_parent
  FOREIGN KEY (parent_id)
  REFERENCES parent_table(id);

Actual PostgreSQL DDL generated by Ora2Pg v25:

ALTER TABLE child_table
  ADD CONSTRAINT fk_child_parent
  FOREIGN KEY (parent_id, parent_partition_key)
  REFERENCES parent_table(id, parent_partition_key);

This fails because parent_partition_key is not a column on child_table.

Suspected Root Cause

In _create_foreign_keys, Ora2Pg appends partition key columns from partitions_list to the local and remote FK column lists. That logic appears to run even when DISABLE_PARTITION 1 is enabled.

When partitions are flattened into regular PostgreSQL tables, the partition key should not be appended to the generated FK. Similar partition-aware logic in other parts of Ora2Pg is guarded by !$self->{disable_partition}.

Proposed Fix

Guard the partition-key append block in _create_foreign_keys with:

if (!$self->{disable_partition}) {
    ...
}

This keeps FK generation aligned with flattened table DDL when DISABLE_PARTITION 1 is used.

Why Workarounds Are Not Sufficient

Per-constraint FKEY[...] excludes remove the broken DDL, but they require manual analysis for every schema and can silently drop useful constraints. Lowering PG_VERSION or changing unrelated naming options may hide the symptom, but they do not address the underlying FK generation logic.