SQL Server error 515: Cannot insert the value NULL into column
An INSERT or UPDATE left a NOT NULL column without a value. The message names the column, the table, and which statement failed.
- Error number
515- Severity
- 16
- Category
- Constraints and keys
What it means
A column declared NOT NULL must always have a value. If an INSERT doesn't list the column and it has no default, or an UPDATE sets it to NULL, SQL Server stops the statement with 515.
Common causes
- The
INSERTcolumn list leaves out aNOT NULLcolumn that has noDEFAULT. - A variable or parameter passed to the column is NULL.
- An
INSERT ... SELECTwhose source has NULLs, often from an outer join. - A new
NOT NULLcolumn was added without a default, and old code doesn't supply it.
How to fix it
-
Provide a value for the column, or wrap possibly-NULL inputs.
INSERT INTO dbo.Orders (CustomerId, Status, CreatedAt) VALUES (@CustomerId, COALESCE(@Status, 'new'), SYSUTCDATETIME());
-
If the column should get a value automatically, add a default constraint.
ALTER TABLE dbo.Orders ADD CONSTRAINT DF_Orders_CreatedAt DEFAULT (SYSUTCDATETIME()) FOR CreatedAt;
-
Find the source rows with NULLs before an
INSERT ... SELECT.SELECT * FROM staging.Orders WHERE Status IS NULL;
Reproduce it safely
CREATE TABLE #n (a int NOT NULL, b int NULL); INSERT INTO #n (b) VALUES (1); -- Msg 515: Cannot insert the value NULL into column 'a' ... DROP TABLE #n;
Related errors
547Statement conflicted with a constraint2627Violation of PRIMARY KEY or UNIQUE KEY23502PostgreSQL: null value violates not-null constraint1048MySQL: Column cannot be null1364MySQL: Field doesn't have a default valueORA-01400Oracle: cannot insert NULL into columnSQLITE_CONSTRAINT_NOTNULLSQLite: NOT NULL constraint failed
More constraints and keys errors
2601Cannot insert duplicate key row3726Can't drop: referenced by a FOREIGN KEY1913Index or statistics name already exists1750Could not create constraint or index4712Can't truncate a referenced table1505Duplicate key found, unique index
The code and message text are from the Microsoft Database Engine events and errors reference. Browse the full SQL Server error list or search every database error.
Work on SQL Server in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for SQL Server and the other databases you use.