If you’re getting an error that reads something like “Cannot insert the value NULL into column ‘CatId’, table ‘demo.dbo.Cats’; column does not allow nulls. INSERT fails” in SQL Server, it’s because you’re trying to insert a NULL value into non-nullable column (i.e. a column with a NOT NULL
constraint).
We can’t insert NULL values into non-nullable columns.
To fix this issue, insert a non-NULL value.
Example of Error
Here’s an example of code that produces the error:
INSERT INTO Cats (CatName) VALUES ('Meow');
Output:
Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'CatId', table 'demo.dbo.Cats'; column does not allow nulls. INSERT fails.
This error occurred because I tried to insert a NULL value into the CatId
column, which happens to be the primary key column for this table.
Actually, in this case I omitted the primary key column altogether. Normally this would’ve resulted in the column having a NULL value, but because the primary key column can’t be NULL
, it returned an error.
We get the same error if we explicitly specify NULL
:
INSERT INTO Cats (CatId, CatName) VALUES (NULL, 'Meow');
Output:
Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'CatId', table 'demo.dbo.Cats'; column does not allow nulls. INSERT fails.
This time I used NULL
in my code to try to make it NULL
. It wouldn’t accept it.
This issue isn’t limited to just primary key columns. It can happen on any column with a NOT NULL
constraint.
For example, we get the same error if we try to insert NULL
into the other column:
INSERT INTO Cats (CatId, CatName) VALUES (1, NULL);
Output:
Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'CatName', table 'demo.dbo.Cats'; column does not allow nulls. INSERT fails.
In this case I switched it around; I provided a value for the primary key column but I tried to make the CatName
column NULL
. But that column also has a NOT NULL
constraint.
Either way, the cause of the problem is the same; we tried to insert a NULL value into a non-nullable column.
Solution
To fix this issue, change the value that you’re trying to insert to a non-NULL value.
Example:
INSERT INTO Cats (CatId, CatName) VALUES (1, 'Meow');
Output:
(1 row affected)
This time it worked.
Let’s check the table:
SELECT * FROM Cats;
Output:
CatId CatName
----- -------
1 Meow
Yes, we can see that the row was inserted as expected.
A Caveat: Default Values
Now, if the column has a DEFAULT
constraint, then that can change things a little. If we omit the column name from the column list, and that column has a DEFAULT
constraint, then the default value defined in the constraint will be inserted into the column.
Example:
INSERT INTO Cats (CatId) VALUES (2);
Output:
(1 row affected)
Let’s check the table:
SELECT * FROM Cats;
Output:
CatId CatName
----- -------
1 Meow
2 Scratch
In this case, the column’s DEFAULT
constraint specified Scratch
as the default value. Therefore, if we don’t provide a value, then that’s the value that will be used.
However, this doesn’t apply when we explicitly specify NULL
in our INSERT
statement:
INSERT INTO Cats (CatId, CatName) VALUES (2, NULL);
Output:
Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'CatName', table 'demo.dbo.Cats'; column does not allow nulls. INSERT fails.
We get the same error again.
So to recap, to resolve the issue, either:
- Specify a non-NULL value for the column
- If the column has a
DEFAULT
constraint (i.e. it has a default value defined), you can omit the column altogether from theINSERT
statement (including from the column list) if you prefer to use the default value.
You’ll also need to ensure the value conforms to any other constraints applied against the column.