{"record":{"id":"71b88d54045aba43","repo":"hibernate/hibernate-orm","slug":"insert-conflict-do-update-clause-with-constraint-71b88d","errorCode":null,"errorMessage":"Insert conflict 'do update' clause with constraint name is not supported","messagePattern":"Insert conflict 'do update' clause with constraint name is not supported","errorType":"exception","errorClass":"IllegalQueryOperationException","httpStatus":null,"severity":"error","filePath":"hibernate-core/src/main/java/org/hibernate/sql/ast/spi/AbstractSqlAstTranslator.java","lineNumber":2052,"sourceCode":"\t\t\t}\n\t\t}\n\t\tclauseStack.pop();\n\t}\n\n\tprotected void visitOnDuplicateKeyConflictClause(ConflictClause conflictClause) {\n\t\tif ( conflictClause == null ) {\n\t\t\treturn;\n\t\t}\n\t\t// The duplicate key clause does not support specifying the constraint name or constraint column names,\n\t\t// but to allow compatibility, we have to require the user to specify either one in the SQM conflict clause.\n\t\t// To allow meaningful usage, we simply ignore the constraint column names in this emulation.\n\t\t// A possible problem with this is when the constraint column names contain the primary key columns,\n\t\t// but the insert fails due to a unique constraint violation. This emulation will not cause a failure to be\n\t\t// propagated, but instead will run the respective conflict action.\n\t\tfinal String constraintName = conflictClause.getConstraintName();\n\t\tif ( constraintName != null ) {\n\t\t\tif ( conflictClause.isDoUpdate() ) {\n\t\t\t\tthrow new IllegalQueryOperationException( \"Insert conflict 'do update' clause with constraint name is not supported\" );\n\t\t\t}\n\t\t\telse {\n\t\t\t\treturn;\n\t\t\t}\n\t\t}\n//\t\tfinal List<String> constraintColumnNames = conflictClause.getConstraintColumnNames();\n//\t\tif ( !constraintColumnNames.isEmpty() ) {\n//\t\t\tthrow new IllegalQueryOperationException( \"Dialect does not support constraint column names in conflict clause\" );\n//\t\t}\n\n\t\tfinal InsertSelectStatement statement = (InsertSelectStatement) statementStack.getCurrent();\n\t\tclauseStack.push( Clause.CONFLICT );\n\t\tappendSql( \" on duplicate key update\" );\n\t\tfinal List<Assignment> assignments = conflictClause.getAssignments();\n\t\tif ( assignments.isEmpty() ) {\n\t\t\t// Emulate do nothing by setting the first column to itself\n\t\t\tfinal ColumnReference columnReference = statement.getTargetColumns().get( 0 );\n\t\t\ttry {","sourceCodeStart":2034,"sourceCodeEnd":2070,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-core/src/main/java/org/hibernate/sql/ast/spi/AbstractSqlAstTranslator.java#L2034-L2070","documentation":"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.","triggerScenarios":"HQL 'insert ... on conflict on constraint <name> do update set ...' executed on MySQL or MariaDB, where upsert is translated to ON DUPLICATE KEY UPDATE.","commonSituations":"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.","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)."],"exampleFix":"// before — named constraint + do update on MySQL/MariaDB\nsession.createQuery(\n    \"insert into User (id,email) values (:i,:e) on conflict on constraint uk_email do update set email = excluded.email\")\n    .executeUpdate();\n\n// after — drop the constraint name; any unique-key conflict triggers the update\nsession.createQuery(\n    \"insert into User (id,email) values (:i,:e) on conflict do update set email = excluded.email\")\n    .executeUpdate();","handlingStrategy":"validation","validationCode":"org.hibernate.dialect.Dialect d = sessionFactory.getJdbcServices().getDialect();\nboolean duplicateKeyUpsert = d instanceof org.hibernate.dialect.MySQLDialect\n        || d instanceof org.hibernate.dialect.MariaDBDialect;\nif (duplicateKeyUpsert && constraintName != null && doUpdate) {\n    constraintName = null; // ON DUPLICATE KEY UPDATE cannot be scoped to a constraint anyway\n}","typeGuard":null,"tryCatchPattern":"try { session.createQuery(insertHql).executeUpdate(); }\ncatch (org.hibernate.query.IllegalQueryOperationException e) {\n    if (e.getMessage().equals(\"Insert conflict 'do update' clause with constraint name is not supported\")) {\n        session.createQuery(insertHql.replace(\" on constraint \" + name, \"\")).executeUpdate();\n    } else { throw e; }\n}","preventionTips":["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."],"tags":["hibernate","orm","upsert","mysql","mariadb","on-duplicate-key"],"backgroundTag":"upsert-conflict-clause-unsupported","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}