PutDatabaseRecord: “Table not found or does not have a Primary Key and no Update Keys were specified”

Summary

This error appears when PutDatabaseRecord is configured to perform an upsert, but Clockspring cannot identify which rows to update. Either the table does not exist, does not have a primary key, or you did not configure Update Keys.

Symptom

In the bulletin you see something like:

Failed to put Records to database... SQLIntegrityConstraintViolationException: Table '<table_name>' not found or does not have a Primary Key and no Update Keys were specified

The FlowFile is routed to the failure relationship.

Root Cause

One of the following is true:

1. The table name is wrong

   • The table does not exist in the target database

   • Schema prefix is missing or incorrect (for example public.se_data vs se_data)

2. The table exists but there is no primary key

   • The table has no primary key or unique constraint

   • The database cannot identify which row should be updated

3. You are trying to upsert without telling Clockspring which columns to use

   • PutDatabaseRecord is set to use an upsert pattern

   • Update Keys property is empty

   • The processor has no way to match incoming records to existing rows

How to Fix It

1. Confirm the table exists

   • Connect to the database

   • Verify the table name and schema

   • Update the table property in PutDatabaseRecord if needed

2. Ensure the table has a primary key or unique key

   • Add a primary key or unique index to the table if it is meant to support upserts

   • Typical choice is an id column or a natural business key

3. Configure Update Keys in PutDatabaseRecord

   • Open the processor

   • Set the Update Keys property to a comma separated list of key columns used to identify existing rows

     • Example: id

     • Example: customer_id, order_id

   • Make sure these columns exist both in the table and in the incoming records

4. If you only want inserts

   • Change the processor configuration so it performs inserts only, not upserts

   • Remove or clear Update Keys and adjust any related settings accordingly

Prevention

• Always design database tables that will be used for upserts with a clear primary key or unique constraint

• Document which column or set of columns is the “business key” and configure those in Update Keys

• Test new upsert flows with a small dataset and verify that updates behave as expected before scaling up

Related Articles

• Troubleshooting SQL and Database Writes

• Common PutDatabaseRecord Configuration Patterns

• Designing Idempotent Database Writes