Foreign Key Constraint Violation When Using Batch Processing in JDBC Sink Connector 2.7.0.Final [DBZ-8922]

Migrated from [DBZ-8922](https://issues.redhat.com/browse/DBZ-8922)

In order to make your issue reports as actionable as possible, please provide the following information, depending on the issue type.
h1. Bug report

For bug reports, provide this information, please:

We are experiencing a foreign key constraint violation when using the Debezium JDBC Sink Connector 2.7.0.Final with batch settings larger than 1. Although the messages in our topic are in the correct order, increasing the batch size seems to introduce an issue during the insert/update operations on the target PostgreSQL 15.4 database.

Observed Behavior:
* With the connector configured for {{batch.size=1}} and {{{}consumer.override.max.poll.records=1{}}}, all operations proceed correctly without any foreign key issues.
* When the batch size is increased to {{500}} (along with {{{}consumer.override.max.poll.records=500{}}}), we receive errors such as:

_Caused by: org.postgresql.util.PSQLException: ERROR: insert or update on table "CUsage" violates foreign key constraint "FK_CUsage_AUsage_AUsageId"_
_Detail: Key (AUsageId)=(e9aa119d-15a4-4bcd-9c5d-02183dc3ff4b) is not present in table "AUsage"_

Troubleshooting Attempts:
* Deferred Foreign Key Constraints:
Configured PostgreSQL to have deferrable constraints (using {{{}DEFERRABLE INITIALLY DEFERRED{}}}), but the error persisted.

_ALTER TABLE "CUsage" DROP CONSTRAINT "FK_CUsage_AUsage_AUsageId";_
_ALTER TABLE "CUsage"_
  _ADD CONSTRAINT "FK_CUsage_AUsage_AUsageId"_
  _FOREIGN KEY ("AUsageId") REFERENCES "AUsage"("Id")_
  _ON DELETE CASCADE_
  _DEFERRABLE INITIALLY DEFERRED;_

* _Connection Pool Settings:_

Forced a single-connection pool with settings, but the error persisted.:

** {{connection.pool.min_size = 1}}

** {{connection.pool.max_size = 1}}

** {{connection.pool.acquire_increment = 0}}

Questions:
* Why does the batching lead to a violation of foreign key constraints even when the topic messages are in the proper order?
* Could there be an issue with how the SQL statements are grouped into transactions when batch processing is enabled?, if yes, how could I set to not have transactions in parallel?
* Is there a recommended configuration or a workaround that would allow the connector to operate in batch mode without encountering these foreign key violations?, I can not disable foreign key o production environment.
* Can I somehow use the JDBC connector hibernate.* passthrough properties?

Any insight or guidance on resolving this ordering issue during batched operations in the connector would be greatly appreciated.
h2. What Debezium connector do you use and what version?

Debezium Sync Connector 2.7.0.Final (image: debezium/connect:2.7.3.Final)
h2. What is the connector configuration?

{
    "connector.class": "io.debezium.connector.jdbc.JdbcSinkConnector",
    "connection.password": "******",
    "tasks.max": "1",
    "transforms": "filter",
    "quote.identifiers": "true",
    "consumer.override.max.poll.records": "500",
    "insert.mode": "upsert",
    "table.name.format": "${topic}",
    "primary.key.mode": "record_key",
    "database.time_zone": "UTC",
    "topics": "Topic-Name",
    "batch.size": "500",
    "connection.username": "username",
    "transforms.filter.type": "custom.debezium.connectors.smt.CustomImportFilter",
    "delete.enabled": "true",
    "name": "IMPORT.LOCAL.35adb52b-803b-4ad6-9527-a28080142fe1.83c1243c-3daf-4eb9-a6b5-e5e6b1526c04",
    "primary.key.fields": "Id",
    "auto.create": "true",
    "connection.url": "jdbc:postgresql://192.168.34.123:5432/DatabaseName",
    "table.naming.strategy": "custom.debezium.connectors.smt.CustomTableNamingStrategy"
}
h2. What is the captured database version and mode of deployment?

(E.g. on-premises, with a specific cloud provider, etc.)

on-premises
h2. What behavior do you expect?

I expect the connector to import the messages into the database in the same order as they are in the topic, without batch size 1 where performance is so poor.
h2. What behavior do you see?

The connector seems to transform the messages from the topic into native SQLs which it mixes in a different order or groups into multiple transactions that are run in parallel, thus leading to foreign key errors.
h2. Do you see the same behaviour using the latest released Debezium version?

(Ideally, also verify with latest Alpha/Beta/CR version)

I didn't test
h2. Do you have the connector logs, ideally from start till finish?

(You might be asked later to provide [DEBUG/TRACE|https://debezium.io/documentation/reference/stable/operations/logging.html] level log)

_Caused by: org.postgresql.util.PSQLException: ERROR: insert or update on table "CUsage" violates foreign key constraint "FK_CUsage_AUsage_AUsageId"_
_Detail: Key (AUsageId)=(e9aa119d-15a4-4bcd-9c5d-02183dc3ff4b) is not present in table "AUsage"_
h2. How to reproduce the issue using our [tutorial|https://github.com/debezium/debezium-examples/tree/main/tutorial] deployment?

h1. Feature request or enhancement

For feature requests or enhancements, provide this information, please:
h2. Which use case/requirement will be addressed by the proposed feature?

h2. Implementation ideas (optional)

Start with the Debezium JDBC Sink Connector configuration, especially batch.size, consumer.override.max.poll.records, tasks.max, and insert.mode. Compare connector logs and PostgreSQL behavior with batch values of 1 and 500, focusing on the reported CUsage-to-AUsage foreign-key failure. Done means establishing whether batching changes transaction or statement ordering and documenting a reproducible resolution or supported configuration.

Written by the indexing model from the issue text.