{"record":{"id":"b7bb160f90a4c1c0","repo":"prestodb/presto","slug":"constraint-violation","errorCode":"CONSTRAINT_VIOLATION","errorMessage":"NULL value not allowed for NOT NULL column: ","messagePattern":"NULL value not allowed for NOT NULL column: ","errorType":"error_code","errorClass":"PrestoException","httpStatus":null,"severity":"error","filePath":"presto-main-base/src/main/java/com/facebook/presto/operator/TableWriterOperator.java","lineNumber":353,"sourceCode":"        timer.end(statisticsTiming);\n\n        ListenableFuture<?> blockedOnAggregation = statisticAggregationOperator.isBlocked();\n        CompletableFuture<?> future = pageSink.appendPage(new Page(blocks));\n        updateMemoryUsage();\n        ListenableFuture<?> blockedOnWrite = toListenableFuture(future);\n        blocked = allAsList(blockedOnAggregation, blockedOnWrite);\n        rowCount += page.getPositionCount();\n        updateWrittenBytes();\n    }\n\n    private void verifyBlockHasNoNulls(Block block, String columnName)\n    {\n        if (!block.mayHaveNull()) {\n            return;\n        }\n        for (int position = 0; position < block.getPositionCount(); position++) {\n            if (block.isNull(position)) {\n                throw new PrestoException(CONSTRAINT_VIOLATION, \"NULL value not allowed for NOT NULL column: \" + columnName);\n            }\n        }\n    }\n\n    @Override\n    public Page getOutput()\n    {\n        if (!blocked.isDone()) {\n            return null;\n        }\n\n        if (!statisticAggregationOperator.isFinished()) {\n            OperationTimer timer = new OperationTimer(statisticsCpuTimerEnabled);\n            Page aggregationOutput = statisticAggregationOperator.getOutput();\n            timer.end(statisticsTiming);\n\n            if (aggregationOutput == null) {\n                return null;","sourceCodeStart":335,"sourceCodeEnd":371,"githubUrl":"https://github.com/prestodb/presto/blob/55bb57d202de3b926896fa966c2c4a44c779634e/presto-main-base/src/main/java/com/facebook/presto/operator/TableWriterOperator.java#L335-L371","documentation":"CONSTRAINT_VIOLATION thrown by TableWriterOperator.verifyBlockHasNoNulls when a Block destined for a NOT NULL output column actually contains a NULL value. Presto's table writers enforce NOT NULL constraints declared on target tables (e.g. in a connector such as Hive/Iceberg) before writing. This surfaces at execution time when upstream operators produce NULLs the schema forbids.","triggerScenarios":"During addInput on the writer's input channel for a NOT NULL column, block.mayHaveNull() is true and scanning positions finds block.isNull(position) true for any position.","commonSituations":"Inserting or CTAS-ing data with NULLs into a table whose column is declared NOT NULL; LEFT/OUTER JOIN producing NULLs feeding the writer; COALESCE/CAST logic missing on optional columns; schema evolution making a previously nullable column NOT NULL.","solutions":["Clean the data before writing: wrap the offending expression with COALESCE(col, default) or filter NULL rows (WHERE col IS NOT NULL).","Check which column fails — the message includes columnName — and relax the target table's NOT NULL constraint if NULLs are legitimate.","Fix upstream joins/aggregations that introduce unintended NULLs.","Use TRY() on expressions that can fail to NULL unexpectedly, or add explicit validation in the query."],"exampleFix":"// before\nINSERT INTO tgt (id, name) SELECT id, name FROM src; -- name is NOT NULL but has NULLs\n// after\nINSERT INTO tgt (id, name) SELECT id, COALESCE(name, 'unknown') FROM src WHERE id IS NOT NULL;","handlingStrategy":"validation","validationCode":"// SQL pre-check before inserting into NOT NULL columns:\nSELECT COUNT(*) FROM src\nWHERE id IS NULL OR name IS NULL; -- must be 0 for NOT NULL targets\n// or coerce at write time:\n-- INSERT INTO tgt SELECT id, COALESCE(name, 'unknown') FROM src","typeGuard":null,"tryCatchPattern":"try {\n    statement.execute(insertSql);\n} catch (SQLException e) {\n    if (e.getMessage() != null && e.getMessage().contains(\"NULL value not allowed for NOT NULL column\")) {\n        String col = e.getMessage().substring(e.getMessage().lastIndexOf(':') + 1).trim();\n        throw new DataIntegrityViolationException(\"NULLs present for NOT NULL column: \" + col, e);\n    }\n    throw e;\n}","preventionTips":["Check target table DDL for NOT NULL constraints before writing","Wrap nullable expressions in COALESCE with sensible defaults","Filter NULL rows with WHERE col IS NOT NULL when dropping them is acceptable","Re-check constraints after schema changes (column made NOT NULL)"],"tags":["not-null-constraint","writer","data-quality"],"backgroundTag":"not-null-constraint-violation","analyzedSha":"55bb57d202de3b926896fa966c2c4a44c779634e","analyzedAt":"2026-09-04T12:50:26.162Z","contentChangedAt":"2026-09-04T12:50:26.162Z","schemaVersion":2},"datasetVersion":"2026-09-11T21:17:09.523Z"}