hibernate/hibernate-orm · error · IllegalQueryOperationException

Insert conflict 'do update' clause with constraint name is n

Error message

Insert conflict 'do update' clause with constraint name is not supported

What it means

MySQL's 'INSERT ... ON DUPLICATE KEY UPDATE' cannot target a specific constraint — it fires on any unique-key violation. visitOnDuplicateKeyConflictClause (used by MySQLSqlAstTranslator:211 and MariaDBSqlAstTranslator:192) therefore rejects an HQL conflict clause that both names a constraint and asks for DO UPDATE, throwing IllegalQueryOperationException before rendering, because there is no faithful way to emulate the named-constraint do-update semantics.

Source

Thrown at hibernate-core/src/main/java/org/hibernate/sql/ast/spi/AbstractSqlAstTranslator.java:2052

			}
		}
		clauseStack.pop();
	}

	protected void visitOnDuplicateKeyConflictClause(ConflictClause conflictClause) {
		if ( conflictClause == null ) {
			return;
		}
		// The duplicate key clause does not support specifying the constraint name or constraint column names,
		// but to allow compatibility, we have to require the user to specify either one in the SQM conflict clause.
		// To allow meaningful usage, we simply ignore the constraint column names in this emulation.
		// A possible problem with this is when the constraint column names contain the primary key columns,
		// but the insert fails due to a unique constraint violation. This emulation will not cause a failure to be
		// propagated, but instead will run the respective conflict action.
		final String constraintName = conflictClause.getConstraintName();
		if ( constraintName != null ) {
			if ( conflictClause.isDoUpdate() ) {
				throw new IllegalQueryOperationException( "Insert conflict 'do update' clause with constraint name is not supported" );
			}
			else {
				return;
			}
		}
//		final List<String> constraintColumnNames = conflictClause.getConstraintColumnNames();
//		if ( !constraintColumnNames.isEmpty() ) {
//			throw new IllegalQueryOperationException( "Dialect does not support constraint column names in conflict clause" );
//		}

		final InsertSelectStatement statement = (InsertSelectStatement) statementStack.getCurrent();
		clauseStack.push( Clause.CONFLICT );
		appendSql( " on duplicate key update" );
		final List<Assignment> assignments = conflictClause.getAssignments();
		if ( assignments.isEmpty() ) {
			// Emulate do nothing by setting the first column to itself
			final ColumnReference columnReference = statement.getTargetColumns().get( 0 );
			try {

View on GitHub (pinned to fad1729dce)

Solutions

Remove the constraint name and use plain 'on conflict do update set ...' (or conflict column names, which this emulation ignores) — ON DUPLICATE KEY UPDATE then applies.
If the constraint must be distinguished (multiple unique keys on the table), use a native MySQL statement plus manual checks, since ON DUPLICATE KEY UPDATE cannot be constrained anyway.
Model the constraint-specific behavior in application logic (pre-select by that unique key, then update-or-insert).

Example fix

// before — named constraint + do update on MySQL/MariaDB
session.createQuery(
    "insert into User (id,email) values (:i,:e) on conflict on constraint uk_email do update set email = excluded.email")
    .executeUpdate();

// after — drop the constraint name; any unique-key conflict triggers the update
session.createQuery(
    "insert into User (id,email) values (:i,:e) on conflict do update set email = excluded.email")
    .executeUpdate();

Defensive patterns

Strategy: validation

Validate before calling

org.hibernate.dialect.Dialect d = sessionFactory.getJdbcServices().getDialect();
boolean duplicateKeyUpsert = d instanceof org.hibernate.dialect.MySQLDialect
        || d instanceof org.hibernate.dialect.MariaDBDialect;
if (duplicateKeyUpsert && constraintName != null && doUpdate) {
    constraintName = null; // ON DUPLICATE KEY UPDATE cannot be scoped to a constraint anyway
}

Try / catch

try { session.createQuery(insertHql).executeUpdate(); }
catch (org.hibernate.query.IllegalQueryOperationException e) {
    if (e.getMessage().equals("Insert conflict 'do update' clause with constraint name is not supported")) {
        session.createQuery(insertHql.replace(" on constraint " + name, "")).executeUpdate();
    } else { throw e; }
}

Prevention

On MySQL/MariaDB never combine named constraints with do-update; any unique key triggers ON DUPLICATE KEY UPDATE.
If multiple unique keys exist, implement constraint-specific upserts in native SQL with explicit predicates.

When it happens

Trigger: HQL 'insert ... on conflict on constraint <name> do update set ...' executed on MySQL or MariaDB, where upsert is translated to ON DUPLICATE KEY UPDATE.

Common situations: Porting PostgreSQL 'ON CONFLICT ON CONSTRAINT ... DO UPDATE' HQL to MySQL/MariaDB; multi-dialect codebases sharing upsert HQL; enabling Hibernate 6.5+ insert-conflict features against MariaDB.

Related errors

AI-assisted analysis of hibernate/hibernate-orm@fad1729dce (2026-08-22). Data as JSON: /api/errors/71b88d54045aba43. Report an issue: GitHub.