// SQL Server error reference

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
Error message
Cannot insert the value NULL into column '%.*ls', table '%.*ls'; column does not allow nulls. %ls fails.

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 INSERT column list leaves out a NOT NULL column that has no DEFAULT.
  • A variable or parameter passed to the column is NULL.
  • An INSERT ... SELECT whose source has NULLs, often from an outer join.
  • A new NOT NULL column was added without a default, and old code doesn't supply it.

How to fix it

  1. Provide a value for the column, or wrap possibly-NULL inputs.

    INSERT INTO dbo.Orders (CustomerId, Status, CreatedAt)
    VALUES (@CustomerId, COALESCE(@Status, 'new'), SYSUTCDATETIME());
  2. 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;
  3. 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

More constraints and keys errors

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.

// fewer errors, faster fixes

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.