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 the INSERT 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.