{"record":{"id":"f154ba0cc4ad2fe5","repo":"hibernate/hibernate-orm","slug":"insert-conflict-do-update-clause-with-constraint-f154ba","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-community-dialects/src/main/java/org/hibernate/community/dialect/SQLServerLegacySqlAstTranslator.java","lineNumber":137,"sourceCode":"\t\t}\n\t}\n\n\t@Override\n\tprotected void renderFromClauseAfterUpdateSet(UpdateStatement statement) {\n\t\tif ( statement.getFromClause().getRoots().isEmpty() ) {\n\t\t\tappendSql( \" from \" );\n\t\t\trenderDmlTargetTableExpression( statement.getTargetTable() );\n\t\t}\n\t\telse {\n\t\t\tvisitFromClause( statement.getFromClause() );\n\t\t}\n\t}\n\n\t@Override\n\tprotected void visitConflictClause(ConflictClause conflictClause) {\n\t\tif ( conflictClause != null ) {\n\t\t\tif ( conflictClause.isDoUpdate() && conflictClause.getConstraintName() != null ) {\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}\n\t}\n\n\t@Override\n\tprotected boolean needsRecursiveKeywordInWithClause() {\n\t\treturn false;\n\t}\n\n\t@Override\n\tprotected void renderTableGroupJoin(TableGroupJoin tableGroupJoin, List<TableGroupJoin> tableGroupJoinCollector) {\n\t\tappendSql( WHITESPACE );\n\t\tif ( tableGroupJoin.getJoinedGroup().isLateral() ) {\n\t\t\tif ( tableGroupJoin.getJoinType() == SqlAstJoinType.LEFT ) {\n\t\t\t\tappendSql( \"outer apply \" );\n\t\t\t}\n\t\t\telse {\n\t\t\t\tappendSql( \"cross apply \" );","sourceCodeStart":119,"sourceCodeEnd":155,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/SQLServerLegacySqlAstTranslator.java#L119-L155","documentation":"Hibernate 6.6+ supports HQL upserts: 'insert into ... select ... on conflict ... do update'. SQL Server cannot bind the DO UPDATE action to a named constraint the way PostgreSQL does; SQLServerLegacySqlAstTranslator.visitConflictClause() detects 'on conflict on constraint X do update' during SQL translation and aborts with IllegalQueryOperationException.","triggerScenarios":"Executing HQL like 'insert into Customer (id, email) select ... on conflict on constraint customer_pkey do update set email = excluded.email' on SQLServerLegacyDialect, or a Criteria insert-with-conflict built with a constraint-name conflict action.","commonSituations":"Porting PostgreSQL-native upsert HQL to SQL Server; shared repository code used against multiple databases where constraint-name targets were chosen for convenience.","solutions":["Use a column list as the conflict target: 'on conflict (id) do update set ...'","Use 'on conflict do nothing', which needs no target","Perform the upsert with a native MERGE statement on SQL Server","Scan dynamic HQL for 'on conflict on constraint' before executing it against this dialect"],"exampleFix":"-- before (HQL)\ninsert into Customer (id, email)\nselect i.id, i.email from ImportRow i\non conflict on constraint customer_pkey do update set email = excluded.email\n\n-- after (HQL)\ninsert into Customer (id, email)\nselect i.id, i.email from ImportRow i\non conflict (id) do update set email = excluded.email","handlingStrategy":"validation","validationCode":"boolean usesConstraintTarget(String hql, Dialect dialect) {\n    return hql != null\n        && hql.toLowerCase( Locale.ROOT ).contains( \"on conflict on constraint\" )\n        && dialect instanceof SQLServerLegacyDialect;\n}\n\nif ( usesConstraintTarget( hql, dialect ) ) {\n    hql = hql.replaceAll( \"(?i)on constraint \\\\S+\", \"(id)\" ); // column-list target\n}","typeGuard":null,"tryCatchPattern":"try {\n    return session.createMutationQuery( hql ).executeUpdate();\n}\ncatch ( IllegalQueryOperationException e ) {\n    // rewrite to 'on conflict (cols) do update' or run a native MERGE on SQL Server\n    throw e;\n}","preventionTips":["Always use column-list conflict targets in portable HQL upserts","Use native MERGE for SQL Server-specific upserts","Run upsert queries against every target dialect in CI"],"tags":["sqlserver","hql","upsert","insert-on-conflict","hibernate6"],"backgroundTag":"insert-on-conflict-unsupported","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}