{"record":{"id":"b192a0c1f9efeed7","repo":"hibernate/hibernate-orm","slug":"insert-conflict-do-update-clause-with-constraint-b192a0","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/dialect/sql/ast/SQLServerSqlAstTranslator.java","lineNumber":138,"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":120,"sourceCodeEnd":156,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-core/src/main/java/org/hibernate/dialect/sql/ast/SQLServerSqlAstTranslator.java#L120-L156","documentation":"On SQL Server, Hibernate renders HQL INSERT ... ON CONFLICT statements as MERGE-based upserts. SQLServerSqlAstTranslator.visitConflictClause rejects the PostgreSQL-style constraint-name conflict target: when the ConflictClause is a DO UPDATE clause with getConstraintName() != null ('on conflict on constraint <name> do update'), it throws IllegalQueryOperationException at translation time, because T-SQL MERGE matches rows by join condition and cannot anchor the conflict to a named constraint.","triggerScenarios":"HQL 'insert into ... values/select ... on conflict on constraint <name> do update set ...' (or conflictOnConstraint(name) on the SQM conflict clause) executed against SQLServerDialect. Throws when the query is translated/compiled (createQuery/executeUpdate), before any JDBC round trip.","commonSituations":"Porting PostgreSQL upsert HQL to SQL Server; cross-dialect test suites that keep one HQL string for all backends; adopting the Hibernate 6.6+ 'on conflict' HQL clause from PostgreSQL-oriented documentation.","solutions":["Switch to a column-list conflict target: 'on conflict (id) do update set ...' renders fine on SQL Server","Ensure the column list matches the unique index you intended the named constraint to protect, so semantics are unchanged","Use a native MERGE ... USING ... WHEN MATCHED THEN UPDATE WHEN NOT MATCHED THEN INSERT statement if you need exact T-SQL control","Branch the HQL by dialect at runtime when one code path must serve PostgreSQL and SQL Server"],"exampleFix":"// before\n\"insert into AuditEvent a (a.ref,a.ts) values (:r,:t) on conflict on constraint uk_auditevent_ref do update set a.ts = excluded.ts\"\n\n// after\n\"insert into AuditEvent a (a.ref,a.ts) values (:r,:t) on conflict (ref) do update set a.ts = excluded.ts\"","handlingStrategy":"validation","validationCode":"static boolean supportsConstraintNameConflictTarget(Dialect dialect) {\n    // SQL Server's MERGE-based rendering only matches on columns\n    return dialect instanceof PostgreSQLDialect;\n}\n\nString conflictClause = supportsConstraintNameConflictTarget(session.getFactory().getJdbcServices().getDialect())\n    ? \"on conflict on constraint \" + name + \" do update set ...\"\n    : \"on conflict (\" + String.join(\",\", columns) + \") do update set ...\";","typeGuard":null,"tryCatchPattern":"try {\n    em.createQuery(hql).executeUpdate();\n} catch (IllegalQueryOperationException e) {\n    if (e.getMessage().contains(\"constraint name is not supported\")) {\n        em.createQuery(hqlWithColumnConflictTarget).executeUpdate();\n        return;\n    }\n    throw e;\n}","preventionTips":["Use column conflict targets in all shared upsert HQL","Test upsert queries on SQL Server in CI, not only on PostgreSQL","Document which HQL fragments are PostgreSQL-only in the codebase"],"tags":["hibernate","sql-server","hql","upsert","on-conflict","insert","query-translation"],"backgroundTag":"upsert-on-conflict-unsupported","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}