SQL Server error 515: 'Cannot insert the value NULL into column ... column does not allow nulls'

Cannot insert the value NULL into column 'FullName', table 'fix8_lab.dbo.Patients'; column does not allow nulls. INSERT fails. (error 515) means the row you sent has no value for a NOT NULL column. Either the column was left out of the INSERT list and has no default, or the value you passed was NULL. Supply a value, or give the column a default.

The message names the column and the table, so the fix starts there: find where that column's value should come from in your INSERT, and if a sensible default exists, put it on the table with a DEFAULT constraint.

The error

INSERT INTO dbo.Patients (Email) VALUES (N'maria.garcia@example.test');

Msg 515, Level 16, State 2, Server MACHINE\LOCALDB#EAD42A01, Line 2
Cannot insert the value NULL into column 'FullName', table 'fix8_lab.dbo.Patients'; column does not allow nulls. INSERT fails.
The statement has been terminated.

Why it happens

When a column is missing from the column list of an INSERT, SQL Server fills it with the column's default. A column without a DEFAULT constraint has NULL as its default, and a NOT NULL column refuses it, so the whole statement is rolled back. The table here declares FullName nvarchar(100) NOT NULL with no default, and the insert named only Email.

The second common cause is less visible: the column is in the list, but the value is NULL at run time. A variable that was declared and never assigned gave exactly the same message:

DECLARE @Name nvarchar(100);
INSERT INTO dbo.Patients (FullName, Email) VALUES (@Name, N'david.chen@example.test');

Msg 515, Level 16, State 2, Server MACHINE\LOCALDB#EAD42A01, Line 3
Cannot insert the value NULL into column 'FullName', table 'fix8_lab.dbo.Patients'; column does not allow nulls. INSERT fails.

In application code the same thing happens when a C# property is null and the parameter is sent as DBNull.

The fix

-- fix A: supply the value
INSERT INTO dbo.Patients (FullName, Email) VALUES (N'Maria Garcia', N'maria.garcia@example.test');
GO
-- fix B: give the column a default, then leave it out
ALTER TABLE dbo.Patients ADD CONSTRAINT DF_Patients_FullName DEFAULT (N'(unknown)') FOR FullName;
INSERT INTO dbo.Patients (Email) VALUES (N'david.chen@example.test');

Both inserts succeeded. A SELECT afterwards returned 3 Maria Garcia and 4 (unknown): the two failed inserts had already used identity values 1 and 2, so gaps in an IDENTITY column after errors like this are normal. Prefer fix A when the value is genuinely required, because a default such as (unknown) hides a missing input instead of reporting it. Use fix B when a real default exists, such as a creation date or a status. Making the column nullable also silences the error, but it changes the rule rather than honouring it.

How it was reproduced

A single script run with sqlcmd -S "(localdb)\MSSQLLocalDB" -d fix8_lab -I -i e515.sql on SQL Server 2022 LocalDB 16.0.1210.3 with sqlcmd 16.0.1000.6, against a throwaway database fix8_lab created for the test and dropped afterwards. The table had an identity key, a NOT NULL name and a nullable email. The names and addresses are fictional.

Frequently asked

What does 'Cannot insert the value NULL into column' mean in SQL Server?

Error 515 means a NOT NULL column received NULL. Either the column was left out of the INSERT list and has no DEFAULT constraint, or the value passed for it was NULL at run time.

How do I fix error 515 without changing the table?

Send a value for the column named in the message. Check the INSERT column list first, then any variable or parameter that feeds that column, because an unassigned variable is NULL.

Why does my identity column skip numbers after error 515?

A failed INSERT still consumes the identity value it was given. In the test, two failed inserts used Ids 1 and 2 and the first successful row got Id 3. Gaps are normal and harmless.

More decoded errors in the Fixes category.